Changes between Version 4 and Version 5 of last change fish & operation

Timestamp:

Dec 11, 2010 9:52:12 PM (14 years ago)

Author:

cedric

Comment:

--

last change fish & operation

@@ -40 +40 @@
 
 
+
+
 -- 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)
@@ -57 +59 @@
 
 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...
 }}}