Changeset 31407 for branches/czw_branch/20110406/ippTools/share/laptool_inactiveexp.sql

Timestamp:

Apr 28, 2011, 6:41:17 PM (15 years ago)

Author:

watersc1

Message:

Majority of ippTools/SQL work should be complete now. Need to start queueing exposures, and testing the script

File:

• branches/czw_branch/20110406/ippTools/share/laptool_inactiveexp.sql (modified) (3 diffs)

branches/czw_branch/20110406/ippTools/share/laptool_inactiveexp.sql

@@ -4 +4 @@
       W.*,IFNULL(diff1.diff_id,diff2.diff_id) AS diff_id FROM (
     SELECT DISTINCT
-        lap_id,tess_id,projection_cell,filter,lapRun.state as lapRun_state, lapRun.registered, lapRun.fault, labRun.label,
+        lapRun.lap_id, lapRun.seq_id, lapRun.tess_id, lapRun.projection_cell, lapRun.filter, lapRun.state, lapRun.label,
+        lapRun.dist_group, lapRun.registered, lapRun.fault, lapRun.quick_sass_id, lapRun.final_sass_id,
         lapExp.exp_id,lapExp.chip_id,lapExp.pair_id,private,pairwise,active,lapExp.data_state,
         chipRun.state as chipRun_state, sum(chipProcessedImfile.fault) as chip_faults, sum(chipProcessedImfile.quality) as chip_quality,
-        camRun.state as camRun_state,   sum(camProcessedExp.fault) AS cam_faults, sum(camProcessedExp.quality) AS cam_quality,
-        fakeRun.state as fakeRun_state, sum(fakeProcessedImfile.fault) as fake_faults,
-        warpRun.state as warpRun_state, sum(warpSkyfile.fault) as warp_faults, sum(warpSkyfile.quality) as warp_quality,
+        cam_id, camRun.state as camRun_state,   sum(camProcessedExp.fault) AS cam_faults, sum(camProcessedExp.quality) AS cam_quality,
+        fake_id, fakeRun.state as fakeRun_state, sum(fakeProcessedImfile.fault) as fake_faults,
+        warp_id, warpRun.state as warpRun_state, sum(warpSkyfile.fault) as warp_faults, sum(warpSkyfile.quality) as warp_quality,
         warpRun.magicked
     FROM lapRun JOIN lapExp USING(lap_id)
-    JOIN chipRun USING(chip_id,exp_id)
-    FROM chipRun JOIN chipProcessedImfile USING(chip_id)
+    LEFT JOIN chipRun USING(chip_id)
+    LEFT JOIN chipProcessedImfile USING(chip_id)
     LEFT JOIN camRun USING(chip_id) LEFT JOIN camProcessedExp USING(cam_id)
     LEFT JOIN fakeRun USING(cam_id) LEFT JOIN fakeProcessedImfile USING(fake_id)
@@ -19 +20 @@
     WHERE lapExp.active = FALSE
     AND @WHERE@
+    GROUP BY lap_id,exp_id
     ) AS W
 -- This was unreasonably slow in testing, so that's why I'm using a subquery here.
@@ -25 +27 @@
 ) AS D
 LEFT JOIN diffRun USING(diff_id)
-JOIN lapExp AS others ON (D.chip_id = lapExp.chip_id AND D.lap_id != lapExp.lap_id)
-GROUP BY chip_id
+JOIN lapExp AS others ON (D.chip_id = others.chip_id AND D.lap_id != others.lap_id)
+GROUP BY lap_id,exp_id