Sergiy - Tuesday, January 10, 2017 6:29 PM

KGJ-Dev (1/9/2017)

---

Hi Luis, thanks for the reply and i would like to put my requirement in detail. please note i have updated my sample on the first post.select * from @Sample order by TestDateplease consider the first four records inserted with in same hour. if the testdate falls with in same hours then that's not duplicate. else duplicate.in my case below are the duplicates2017-01-07 14:06:21.8402017-01-07 14:06:22.4402017-01-07 14:06:24.2272017-01-07 14:06:28.180how do i get this duplicate records based on same hour data. any sugestion please Also there is a possiblility 2016-12-31 23.55 , 2016-12-31 23.56, 2017-01-01 00.55. these are not duplicate. We need to tacke this as well

SELECT 'unique records to leave', *, ROW_NUMBER() OVER (ORDER BY s.TestDate)FROM @Sample sWHERE NOT EXISTS (SELECT * FROM @Sample s2 WHERE s2.TestDate > DATEADD(ss, -30, s.TestDate)AND s2.TestDate < s.TestDate )SELECT 'duplicates to remove', * FROM @Sample sWHERE EXISTS (SELECT * FROM @Sample s2 WHERE s2.TestDate > DATEADD(ss, -30, s.TestDate)AND s2.TestDate < s.TestDate )