Index: trunk/ippTools/share/Makefile.am
===================================================================
--- trunk/ippTools/share/Makefile.am	(revision 25795)
+++ trunk/ippTools/share/Makefile.am	(revision 25800)
@@ -89,4 +89,6 @@
      dettool_toresidimfile.sql \
      dettool_tostacked.sql \
+     difftool_change_skyfile_data_state.sql \
+     difftool_change_run_state.sql \
      difftool_completed_runs.sql \
      difftool_definewarpstack_part1.sql \
Index: trunk/ippTools/share/difftool_change_run_state.sql
===================================================================
--- trunk/ippTools/share/difftool_change_run_state.sql	(revision 25800)
+++ trunk/ippTools/share/difftool_change_run_state.sql	(revision 25800)
@@ -0,0 +1,15 @@
+-- change state of ddiffRun from goto_cleaned to cleaned or goto_purged to purged
+-- when all of the consituant skyfiles are in the end state
+-- arguments are new state (cleaned or purged) diff_id and new state again for 
+-- the chipProcessedImfile sub query
+UPDATE diffRun
+    SET state = '%s'
+    WHERE
+    diffRun.diff_id = %lld
+    AND (SELECT
+        COUNT(diff_id)
+        FROM diffSkyfile
+        WHERE
+            diffSkyfile.diff_id = diffRun.diff_id
+            AND data_state != '%s'
+        ) = 0
Index: trunk/ippTools/share/difftool_change_skyfile_data_state.sql
===================================================================
--- trunk/ippTools/share/difftool_change_skyfile_data_state.sql	(revision 25800)
+++ trunk/ippTools/share/difftool_change_skyfile_data_state.sql	(revision 25800)
@@ -0,0 +1,9 @@
+-- handle changes in diffSkyfile.data_state.
+-- Used for the modes tocleanedskyfile and topurgedskyfile
+-- args are new data_state, diff_id, skycell_id and current expected state for diffRun
+UPDATE diffSkyfile
+    SET 
+    data_state = '%s'
+WHERE
+    diff_id = %lld
+    AND skycell_id = '%s'
Index: trunk/ippTools/share/difftool_pendingcleanuprun.sql
===================================================================
--- trunk/ippTools/share/difftool_pendingcleanuprun.sql	(revision 25795)
+++ trunk/ippTools/share/difftool_pendingcleanuprun.sql	(revision 25800)
@@ -1,25 +1,112 @@
 -- does this result in too many entries (one for each diffInputSkyfile?)
 -- all of this is just to get the camera used for the diff run
-SELECT
-    diffRun.diff_id,
-    rawExp.camera,
-    diffRun.state
-FROM diffRun
-JOIN diffInputSkyfile
-    USING(diff_id)
-JOIN warpSkyfile
-    ON  diffInputSkyfile.warp1    = warpSkyfile.warp_id
-    AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
-    AND diffInputSkyfile.tess_id    = warpSkyfile.tess_id
-JOIN warpRun
-    ON warpRun.warp_id = warpSkyfile.warp_id
-JOIN fakeRun
-    USING(fake_id)
-JOIN camRun
-    USING(cam_id)
-JOIN chipRun
-    USING(chip_id)
-JOIN rawExp
-    USING(exp_id)
-WHERE
-    (diffRun.state = 'goto_cleaned' OR diffRun.state = 'goto_scrubbed' OR diffRun.state = 'goto_purged')
+SELECT DISTINCT * FROM 
+   (SELECT 
+	    diffRun.diff_id,
+	    rawExp.camera,
+	    diffRun.state,
+	    diffRun.label
+	FROM diffRun
+	JOIN diffInputSkyfile
+	    USING(diff_id)
+	JOIN warpSkyfile
+	    ON  diffInputSkyfile.warp1    = warpSkyfile.warp_id
+	    AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
+	    AND diffInputSkyfile.tess_id    = warpSkyfile.tess_id
+	JOIN warpRun
+	    ON warpRun.warp_id = warpSkyfile.warp_id
+	JOIN fakeRun
+	    USING(fake_id)
+	JOIN camRun
+	    USING(cam_id)
+	JOIN chipRun
+	    USING(chip_id)
+	JOIN rawExp
+	    USING(exp_id)
+	WHERE
+	    (diffRun.state = 'goto_cleaned' OR 
+             diffRun.state = 'goto_scrubbed' OR 
+	     diffRun.state = 'goto_purged')
+     UNION
+     SELECT
+	    diffRun.diff_id,
+	    rawExp.camera,
+	    diffRun.state,
+	    diffRun.label
+	FROM diffRun
+	JOIN diffInputSkyfile
+	    USING(diff_id)
+	JOIN warpSkyfile
+	    ON  diffInputSkyfile.warp2    = warpSkyfile.warp_id
+	    AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
+	    AND diffInputSkyfile.tess_id    = warpSkyfile.tess_id
+	JOIN warpRun
+	    ON warpRun.warp_id = warpSkyfile.warp_id
+	JOIN fakeRun
+	    USING(fake_id)
+	JOIN camRun
+	    USING(cam_id)
+	JOIN chipRun
+	    USING(chip_id)
+	JOIN rawExp
+	    USING(exp_id)
+	WHERE
+	    (diffRun.state = 'goto_cleaned' OR 
+             diffRun.state = 'goto_scrubbed' OR 
+	     diffRun.state = 'goto_purged')
+     UNION
+     SELECT
+	    diffRun.diff_id,
+	    rawExp.camera,
+	    diffRun.state,
+	    diffRun.label
+	FROM diffRun
+	JOIN diffInputSkyfile
+	    USING(diff_id)
+	JOIN stackSumSkyfile
+	    ON  diffInputSkyfile.stack1 = stackSumSkyfile.stack_id
+	JOIN stackInputSkyfile
+	    ON diffInputSkyfile.stack1 = stackInputSkyfile.stack_id
+	JOIN warpRun
+	    ON warpRun.warp_id = stackInputSkyfile.warp_id
+	JOIN fakeRun
+	    USING(fake_id)
+	JOIN camRun
+	    USING(cam_id)
+	JOIN chipRun
+	    USING(chip_id)
+	JOIN rawExp
+	    USING(exp_id)
+	WHERE
+	    (diffRun.state = 'goto_cleaned' OR 
+             diffRun.state = 'goto_scrubbed' OR 
+	     diffRun.state = 'goto_purged')
+     UNION
+     SELECT
+	    diffRun.diff_id,
+	    rawExp.camera,
+	    diffRun.state,
+	    diffRun.label
+	FROM diffRun
+	JOIN diffInputSkyfile
+	    USING(diff_id)
+	JOIN stackSumSkyfile
+	    ON  diffInputSkyfile.stack2 = stackSumSkyfile.stack_id
+	JOIN stackInputSkyfile
+	    ON diffInputSkyfile.stack2 = stackInputSkyfile.stack_id
+	JOIN warpRun
+	    ON warpRun.warp_id = stackInputSkyfile.warp_id
+	JOIN fakeRun
+	    USING(fake_id)
+	JOIN camRun
+	    USING(cam_id)
+	JOIN chipRun
+	    USING(chip_id)
+	JOIN rawExp
+	    USING(exp_id)
+	WHERE
+	    (diffRun.state = 'goto_cleaned' OR 
+             diffRun.state = 'goto_scrubbed' OR 
+	     diffRun.state = 'goto_purged')
+	) as Foo
+	WHERE 1