>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); >>
>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 #RestPunches >>