pb Integration of the same line two times

[ClarisseBoulenger]

We can integrate for the same year, hty, emu, lfs two lines: one line with a missing value and a NC and one with a value. Many of these in the database

select eel_typ_id,eel_year,eel_emu_nameshort, eel_lfs_code,eel_hty_code, count(*) 
from datawg.t_eelstock_eel where eel_qal_id in (0,1)
 group by eel_typ_id,eel_year,eel_emu_nameshort, eel_lfs_code,eel_hty_code
 having count(*) >1

[cedricbriandgithub]

-- 194 some of those correspond to coastal waters with area division 
-- two separate columns for area division
WITH cc AS (
select 
eel_typ_id,
eel_qal_id,
eel_year,
eel_emu_nameshort, 
eel_lfs_code,
eel_hty_code, 
eel_area_division,
eel_value, 
eel_missvaluequal, 
count(*) OVER (PARTITION BY eel_typ_id,eel_year,eel_emu_nameshort, eel_lfs_code,eel_hty_code,eel_area_division)  AS n
from datawg.t_eelstock_eel where eel_qal_id in (0,1))
SELECT * FROM cc WHERE n>1

qal data is never 0 when taken from calculations ... In extract data we use qal_id 1, 2 and 4 ...

-- Same query but only using stages that are used
WITH cc AS (
select 
eel_typ_id,
eel_qal_id,
eel_year,
eel_emu_nameshort, 
eel_lfs_code,
eel_hty_code, 
eel_area_division,
eel_value, 
eel_missvaluequal, 
count(*) OVER (PARTITION BY eel_typ_id,eel_year,eel_emu_nameshort, eel_lfs_code,eel_hty_code,eel_area_division)  AS n
from datawg.t_eelstock_eel where eel_qal_id in (1,2,4))
SELECT * FROM cc WHERE n>1

Only 4 lines	eel_typ_id	eel_qal_id	eel_year	eel_emu_nameshort	eel_lfs_code	eel_hty_code	eel_area_division	eel_value	eel_missvaluequal	n
11	1	2014	FI_total	YS			500		2
11	2	2014	FI_total	YS			500		2
11	1	2015	FI_total	YS			500		2
11	2	2015	FI_total	YS			500		2

SOLVED WITH

UPDATE datawg.t_eelstock_eel SET (eel_qal_id, eel_qal_comment) = (21, 'transfered from emu to total but entered again later on, duplicate removed by cedric during dc 2021') WHERE eel_id IN (423409,423410);--2