SET NOCOUNT ON WITH DoublePunches (puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut, FXCount) AS ( select puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut, count(*) AS FXCount FROM handpunch_punches WHERE DelFlag = 0 GROUP BY puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut HAVING count(*) > 1 ) , RestPunches (puKey, puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut) AS ( SELECT MAX(Handpunch_Punches.puKey) AS puKey, Handpunch_Punches.puEmKey, Handpunch_Punches.puDateIn, Handpunch_Punches.puTimeIn, Handpunch_Punches.puDateOut, Handpunch_Punches.puTimeOut FROM Handpunch_Punches JOIN DoublePunches ON Handpunch_Punches.puEmKey = DoublePunches.puEmKey AND Handpunch_Punches.puDateIn = DoublePunches.puDateIn AND Handpunch_Punches.puTimeIn = DoublePunches.puTimeIn AND Handpunch_Punches.puDateOut = DoublePunches.puDateOut AND Handpunch_Punches.puTimeOut = DoublePunches.puTimeOut GROUP BY Handpunch_Punches.puEmKey, Handpunch_Punches.puDateIn, Handpunch_Punches.puTimeIn, Handpunch_Punches.puDateOut, Handpunch_Punches.puTimeOut ) DELETE Handpunch_Punches FROM Handpunch_Punches JOIN DoublePunches ON Handpunch_Punches.puEmKey = DoublePunches.puEmKey AND Handpunch_Punches.puDateIn = DoublePunches.puDateIn AND Handpunch_Punches.puTimeIn = DoublePunches.puTimeIn AND Handpunch_Punches.puDateOut = DoublePunches.puDateOut AND Handpunch_Punches.puTimeOut = DoublePunches.puTimeOut WHERE NOT EXISTS(SELECT NULL FROM RestPunches WHERE RestPunches.puKey = Handpunch_Punches.puKey);While the query using temporary tables was extremely fast:
SET NOCOUNT ON SELECT puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut, count(*) AS FXCount INTO #DoublePunches FROM handpunch_punches WHERE DelFlag = 0 GROUP BY puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut HAVING count(*) > 1 SELECT MAX(Handpunch_Punches.puKey) AS puKey, Handpunch_Punches.puEmKey, Handpunch_Punches.puDateIn, Handpunch_Punches.puTimeIn, Handpunch_Punches.puDateOut, Handpunch_Punches.puTimeOut INTO #RestPunches FROM Handpunch_Punches JOIN #DoublePunches DoublePunches ON Handpunch_Punches.puEmKey = DoublePunches.puEmKey AND Handpunch_Punches.puDateIn = DoublePunches.puDateIn AND Handpunch_Punches.puTimeIn = DoublePunches.puTimeIn AND Handpunch_Punches.puDateOut = DoublePunches.puDateOut AND Handpunch_Punches.puTimeOut = DoublePunches.puTimeOut GROUP BY Handpunch_Punches.puEmKey, Handpunch_Punches.puDateIn, Handpunch_Punches.puTimeIn, Handpunch_Punches.puDateOut, Handpunch_Punches.puTimeOut DELETE Handpunch_Punches FROM Handpunch_Punches JOIN #DoublePunches DoublePunches ON Handpunch_Punches.puEmKey = DoublePunches.puEmKey AND Handpunch_Punches.puDateIn = DoublePunches.puDateIn AND Handpunch_Punches.puTimeIn = DoublePunches.puTimeIn AND Handpunch_Punches.puDateOut = DoublePunches.puDateOut AND Handpunch_Punches.puTimeOut = DoublePunches.puTimeOut WHERE NOT EXISTS(SELECT NULL FROM #RestPunches RestPunches WHERE RestPunches.puKey = Handpunch_Punches.puKey); DROP TABLE #DoublePunches DROP TABLE #RestPunchesSo either I misunderstand the purpose of WITH or there is something else I am not aware of?