Cacher is the code snippet organizer for pro developers

SELECT enrollid INTO #Duplicatestodelete FROM (
              SELECT ROW_NUMBER() OVER(PARTITION BY userid, planid, coverageeffectivedate, coverageterminationdate, effectivedate, terminationdate 
              ORDER BY userid, planid, coverageeffectivedate, coverageterminationdate, effectivedate, terminationdate, savedDateTime DESC) rn, e.*
              FROM enroll e WITH(NOLOCK)
              where planid in (230300,230453,230454,230455,230456,259622,259623,259625,259626,259629)
              and removedDate is null
) a
WHERE rn > 1


BEGIN TRAN
DELETE e
FROM enroll e WITH(NOLOCK)
INNER JOIN #Duplicatestodelete toDelete WITH(NOLOCK)
       ON e.enrollid = toDelete.enrollID
IF @@ROWCOUNT = 7
       COMMIT TRAN
ELSE
       ROLLBACK TRAN

DROP TABLE #Duplicatestodelete