Changeset 15478

Timestamp:

Nov 6, 2007, 2:13:11 PM (19 years ago)

Author:

jhoblitt

Message:

properly account for paritially processed skycells

File:

• trunk/ippTools/share/magictool_find_unmagiced.sql (modified) (1 diff)

trunk/ippTools/share/magictool_find_unmagiced.sql

@@ -1 @@
-SELECT
-    *
+SELECT DISTINCT
+    warpSkyCellMap.*,
+    diffInputSkyfile.*
 FROM warpRun
 -- find all of the skycells that came out of a single exposures
 JOIN warpSkyCellMap
     USING(warp_id)
+LEFT JOIN warpSkyfile
+    USING(warp_id, skycell_id, tess_id)
 -- find out which skycells have been diff'd
 LEFT JOIN diffInputSkyfile
-    USING(skycell_id, tess_id)
-JOIN diffRun
+    USING(warp_id, skycell_id, tess_id)
+LEFT JOIN diffRun
+    USING(diff_id)
+LEFT JOIN diffSkyfile
     USING(diff_id)
 -- find out which diff'd skycells have been added to a magic run
 LEFT JOIN magicInputSkyfile
     USING(diff_id)
-JOIN magicRun
+LEFT JOIN magicRun
     USING (magic_id)
 WHERE
-    diffRun.state = 'stop'
-    AND warpRun.state = 'stop'
-    AND diffInputSkyfile.template = 0
-    AND diffInputSkyfile.kind = 'warped'
-    AND diffInputSkyfile.skycell_id IS NOT NULL
-    AND diffInputSkyfile.tess_id IS NOT NULL
+    warpRun.state = 'stop'
+    AND (diffRun.state = 'stop' || diffRun.state IS NULL)
+    AND (warpSkyfile.fault = 0 || warpSkyfile.fault IS NULL)
+    AND (diffInputSkyfile.template = 0 || diffInputSkyfile.template IS NULL)
+    AND (diffInputSkyfile.kind = 'warped' || diffInputSkyfile.kind IS NULL)
+    AND (diffSkyfile.fault = 0 || diffSkyfile.fault IS NULL)
     AND magicInputSkyfile.diff_id IS NULL
+GROUP BY
+    warpRun.warp_id
+HAVING COUNT(warpSkyCellMap.skycell_id) = COUNT(diffInputSkyfile.skycell_id)