back to first page..
back to WRBD

-- verifying years and dates
select * from(
select op_id,extract(year from op_date) as dateyear,op_year from wrbd.operation_op where op_date is not null and op_year is not null) as sub
where dateyear!=op_year; -- op_id=1657
update wrbd.operation_op set op_date='1977-01-01' where op_id=1657;
update wrbd.operation_op set op_year=extract(year from op_date) where op_year is null and op_date is not null; -- 318 lines modified

--verifying stations
select * from wrbd.fish_fi where fi_st_id is null; --1974 and 1975 and 1976
select * from  wrbd.operation_op where op_year<1976; -- it is st2 as it is not marine institute data
update wrbd.fish_fi set fi_st

alter table wrbd.fish_fi  add constraint c_fk_fi_st_id FOREIGN KEY (fi_st_id) REFERENCES wrbd.station_st(st_id);
--La clé (fi_st_id)=(MI037) n'est pas présente dans la table « station_st ».
select * from wrbd.fish_fi where fi_st_id='MI037'; -- fi_id=24865
update wrbd.fish_fi set fi_st_id='MI0037' where fi_st_id='MI037'; --1100 lines modified
alter table wrbd.fish_fi  add constraint c_fk_fi_st_id FOREIGN KEY (fi_st_id) REFERENCES wrbd.station_st(st_id); -- OK

--verifying operations
select * from wrbd.fish_fi where fi_op_id is null; -- 23461 lines ouh!

select fi_id, op.* from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null; -- 22144



select  'update wrbd.fish_fi set fi_op_id='||cast(op_id as text)||' where fi_id='||cast (fi_id as text)||';' from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null and fi_id not in (
select fi_id from (
select count(fi_id) as count, fi_id from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null
group by fi_id) as toto where count>1);
; -- 22086 -- export update_fish_op_1.sql --lancer ici le script sql




-- now I still have when joining on date and station some fish pointing to more than one operation
select * from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null; -- 58

-- only two choices there... One with 8 fishes (fi_id 10761 to 10768) one with 21 8 is 1674 21 is 1675
select * from wrbd.fish_fi where fi_id>=10761 and fi_id<=10768; -- to verify
update wrbd.fish_fi set fi_op_id=1674 where fi_id>=10761 and fi_id<=10768;

select * from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null; -- 42

select * from wrbd.fish_fi where fi_id>=10769 and fi_id<=10789; -- to verify
update wrbd.fish_fi set fi_op_id=1675 where fi_id>=10769 and fi_id<=10789;

-- OK finished for pivot on date

select * from wrbd.fish_fi where fi_op_id is null; --1346 still.... some have dates uh ?

select * from wrbd.fish_fi join wrbd.operation_op op on (fi_year,fi_st_id)=(op_year,op_st_id)
where fi_op_id is null and fi_date is not null; -- 645


select * from wrbd.operation_op op where op_year=1974;

select * from wrbd.fish_fi where fi_year=1974 and fi_st_id='st2';
--three operations missing
insert into wrbd.operation_op(op_st_id,op_date,op_year) values ('st2','1974-07-18',1974);
insert into wrbd.operation_op(op_st_id,op_date,op_year) values ('st2','1974-07-19',1974);
insert into wrbd.operation_op(op_st_id,op_date,op_year) values ('st2','1974-07-20',1974);

--exporting fishes to update_fish_1.sql
select  'update wrbd.fish_fi set fi_op_id='||cast(op_id as text)||' where fi_id='||cast (fi_id as text)||';' from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null and fi_id not in (
select fi_id from (
select count(fi_id) as count, fi_id from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null
group by fi_id) as toto where count>1);
;

select * from wrbd.fish_fi join wrbd.operation_op op on (fi_year,fi_st_id)=(op_year,op_st_id)
where fi_op_id is null and fi_date is not null; -- 531

select * from wrbd.fish_fi where fi_year=1975 and fi_st_id='st2';
select * from wrbd.operation_op op where op_year=1975;

insert into wrbd.operation_op(op_st_id,op_date,op_year) values ('st2','1975-07-31',1974);
insert into wrbd.operation_op(op_st_id,op_date,op_year) values ('st2','1975-08-01',1974);
insert into wrbd.operation_op(op_st_id,op_date,op_year) values ('st2','1975-08-02',1974);

--exporting fishes to update_fish_2.sql
select  'update wrbd.fish_fi set fi_op_id='||cast(op_id as text)||' where fi_id='||cast (fi_id as text)||';' from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null and fi_id not in (
select fi_id from (
select count(fi_id) as count, fi_id from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null
group by fi_id) as toto where count>1);
;
select * from wrbd.fish_fi join wrbd.operation_op op on (fi_year,fi_st_id)=(op_year,op_st_id)
where fi_op_id is null and fi_date is not null; -- 474 no more st2, MI0011,MI0017, MI0018

select * from wrbd.fish_fi where fi_year=1988 and fi_st_id='MI0011'; --1988-09-09

select * from wrbd.operation_op op where op_year=1988 and op_st_id='MI0011';

select op_folio_no,op_st_id,op_year,op_yellow,op_p1,op_main_survey_target,op_equipment,op_nbnets,op_datasource from wrbd.operation_op op where op_date='1988-08-09' and op_st_id='MI0011';
insert into wrbd.operation_op (op_folio_no,op_st_id,op_year,op_yellow,op_p1,op_main_survey_target,op_equipment,op_nbnets,op_datasource)
select op_folio_no,op_st_id,op_year,op_yellow,op_p1,op_main_survey_target,op_equipment,op_nbnets,op_datasource from wrbd.operation_op op where op_date='1988-08-09' and op_st_id='MI0011';
update wrbd.operation_op set op_date='1988-09-09' where op_id=(select max(op_id) from wrbd.operation_op);
update wrbd.operation_op set op_yellow=(select count(*)  from wrbd.fish_fi where fi_date='1988-09-09' and fi_st_id='MI0011') where op_id=(select max(op_id) from wrbd.operation_op);
update wrbd.operation_op set op_p1=(select count(*)  from wrbd.fish_fi where fi_date='1988-09-09' and fi_st_id='MI0011') where op_id=(select max(op_id) from wrbd.operation_op);

--exporting fishes to update_fish_3.sql
select  'update wrbd.fish_fi set fi_op_id='||cast(op_id as text)||' where fi_id='||cast (fi_id as text)||';' from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null and fi_id not in (
select fi_id from (
select count(fi_id) as count, fi_id from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null
group by fi_id) as toto where count>1);

select * from wrbd.fish_fi join wrbd.operation_op op on (fi_year,fi_st_id)=(op_year,op_st_id)
where fi_op_id is null and fi_date is not null; -- 430  MI0017

select * from wrbd.fish_fi where fi_year=1988 and fi_st_id='MI0017'; --1988-09-17 there is 09-16 fiop_id=1506
select * from wrbd.fish_fi where fi_date='1988-09-17' and fi_st_id='MI0017';
update wrbd.fish_fi set fi_op_id=1506 where fi_date='1988-09-17' and fi_st_id='MI0017'; --26 lines

select * from wrbd.fish_fi join wrbd.operation_op op on (fi_year,fi_st_id)=(op_year,op_st_id)
where fi_op_id is null and fi_date is not null; -- 40  MI0018 2007-10-11 and MI0022 2005-06-15

select * from wrbd.fish_fi where fi_year=2005 and fi_st_id='MI0022';
select * from wrbd.fish_fi where fi_date='2005-06-15' and fi_st_id='MI0022';
select distinct on (fi_op_id) fi_op_id from wrbd.fish_fi where fi_date='2005-06-14' and fi_st_id='MI0022';
update wrbd.fish_fi set fi_op_id=(select distinct on (fi_op_id) fi_op_id from wrbd.fish_fi where fi_date='2005-06-14' and fi_st_id='MI0022')
 where fi_date='2005-06-15' and fi_st_id='MI0022'; -- 5 lines modified


select * from wrbd.fish_fi join wrbd.operation_op op on (fi_year,fi_st_id)=(op_year,op_st_id)
where fi_op_id is null and fi_date is not null; -- 40  MI0018 2007-10-11 
select * from wrbd.fish_fi where fi_year=2007 and fi_st_id='MI0018'; -- cette fois pas d'operation
select * from wrbd.operation_op op where op_date='2007-08-14' and op_st_id='MI0018';
select op_folio_no,op_st_id,op_year,op_yellow,op_p1,op_main_survey_target,op_equipment,op_nbnets,op_datasource from wrbd.operation_op op where op_date='2007-08-14' and op_st_id='MI0018';
insert into wrbd.operation_op (op_folio_no,op_st_id,op_year,op_yellow,op_p1,op_main_survey_target,op_equipment,op_nbnets,op_datasource)
select op_folio_no,op_st_id,op_year,op_yellow,op_p1,op_main_survey_target,op_equipment,op_nbnets,op_datasource from wrbd.operation_op op where op_date='2007-08-14' and op_st_id='MI0018';
update wrbd.operation_op set op_date='2007-10-11' where op_id=(select max(op_id) from wrbd.operation_op);
update wrbd.operation_op set op_yellow=(select count(*)  from wrbd.fish_fi where fi_date='2007-10-11' and fi_st_id='MI0018') where op_id=(select max(op_id) from wrbd.operation_op);
update wrbd.operation_op set op_p1=(select count(*)  from wrbd.fish_fi where fi_date='2007-10-11' and fi_st_id='MI0018') where op_id=(select max(op_id) from wrbd.operation_op);


--exporting fishes to update_fish_4.sql
select  'update wrbd.fish_fi set fi_op_id='||cast(op_id as text)||' where fi_id='||cast (fi_id as text)||';' from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null and fi_id not in (
select fi_id from (
select count(fi_id) as count, fi_id from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null
group by fi_id) as toto where count>1);

update wrbd.fish_fi set fi_op_id=2598 where fi_id=6779;
update wrbd.fish_fi set fi_op_id=2598 where fi_id=6780;
update wrbd.fish_fi set fi_op_id=2598 where fi_id=6781;
update wrbd.fish_fi set fi_op_id=2598 where fi_id=6782;
update wrbd.fish_fi set fi_op_id=2598 where fi_id=6783;
update wrbd.fish_fi set fi_op_id=2598 where fi_id=6784;
update wrbd.fish_fi set fi_op_id=2598 where fi_id=6785;
update wrbd.fish_fi set fi_op_id=2598 where fi_id=6786;
update wrbd.fish_fi set fi_op_id=2598 where fi_id=6787;
update wrbd.fish_fi set fi_op_id=2598 where fi_id=6788;


select * from wrbd.fish_fi join wrbd.operation_op op on (fi_year,fi_st_id)=(op_year,op_st_id)
where fi_op_id is null and fi_date is not null; -- plus rien

select * from wrbd.fish_fi where fi_op_id is null -- 1123 st2 et MI0037


select * from wrbd.operation_op op where op_year=1973; --nothing
select * from wrbd.operation_op op where op_st_id='st2';
select * from wrbd.fish_fi where fi_year=1973 and fi_st_id='st2';
insert into wrbd.operation_op (op_st_id,op_date,op_year) values('st2','1973-06-29',1973);

--exporting fishes to update_fish_5.sql
select  'update wrbd.fish_fi set fi_op_id='||cast(op_id as text)||' where fi_id='||cast (fi_id as text)||';' from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null and fi_id not in (
select fi_id from (
select count(fi_id) as count, fi_id from wrbd.fish_fi join wrbd.operation_op op on (fi_date,fi_st_id)=(op_date,op_st_id)
where fi_op_id is null and fi_date is not null
group by fi_id) as toto where count>1);

update wrbd.fish_fi set fi_op_id=2599 where fi_id=25647;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25648;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25649;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25650;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25651;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25652;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25653;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25654;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25655;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25656;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25657;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25658;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25659;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25660;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25661;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25662;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25663;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25664;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25665;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25666;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25667;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25668;
update wrbd.fish_fi set fi_op_id=2599 where fi_id=25669;

select * from wrbd.fish_fi where fi_op_id is null -- 1123 MI0037 2008 and 2009 no dates

select * from wrbd.operation_op op where op_st_id='MI0037'; --OK two operations 1961 (2008) and 1962 (2009)
select * from wrbd.fish_fi join wrbd.operation_op op on (fi_year,fi_st_id)=(op_year,op_st_id) where fi_op_id is null and fi_date is null

select * from wrbd.fish_fi where fi_op_id is null and fi_year=2008 and fi_st_id='MI0037'
update wrbd.fish_fi set fi_op_id='1961' where fi_op_id is null and fi_year=2008 and fi_st_id='MI0037';
select * from wrbd.fish_fi where fi_op_id is null and fi_year=2009 and fi_st_id='MI0037'
update wrbd.fish_fi set fi_op_id=1962 where fi_op_id is null and fi_year=2009 and fi_st_id='MI0037';

select * from wrbd.fish_fi where fi_op_id is null; --0 lines AHHHHH finished...

alter table wrbd.fish_fi add column fi_tag_code character varying(30);
alter table wrbd.fish_fi add column fi_recaptured character varying(30);
update wrbd.fish_fi set fi_pectoral_fin_length = NULL where fi_pectoral_fin_length=-99;
select * from wrbd.fish_fi where fi_pectoral_fin_length is not null; -- nothing
update wrbd.fish_fi set fi_eye_diameter = NULL where fi_eye_diameter=-99;
alter table wrbd.fish_fi alter column fi_fishid type character varying(20);
copy wrbd.fish_fi(fi_st_id,fi_op_id,fi_date,fi_folio_no,fi_fishid,fi_tag_code,fi_recaptured,fi_length,fi_weight,fi_eye_diameter,
fi_pectoral_fin_length,fi_metallic_colour,fi_black_spots_on_lateral_line,fi_colour)
 from 'C:/eda/ireland/POSE_Data_for_Modellers/lake_fish.csv' with CSV header delimiter as ';';-- 2641 lines modified.