T-Sql: How to find records added in duplicate within a few seconds from each other

Goal: You need to find out if in a particular table, records that seem to have been added in duplicate due to a possible double-click. In this example I am querying a Shipment table and the natural key is the GBL number.

Solution:
In this example we will be looking for entries within a minute from each of the duplicate records (30 seconds before to 30 seconds after). See minutes example commented out as an example.

select s.GBL, count(*)
from Shipment s
where s.GBL is not null
and 1 < (
select count(*)
from Shipment i_s
where i_s.GBL = s.GBL
--1 minute window
and i_s.CreatedDateTime between DATEADD(ss, -30, s.CreatedDateTime) and DATEADD(ss, 30, s.CreatedDateTime)
--Example for minutes, int this case 2 minute window
--and i_s.CreatedDateTime between DATEADD(mi, -1, s.CreatedDateTime) and DATEADD(mi, 1, s.CreatedDateTime)
)
group by s.GBL
having count(*) > 1

Print | posted @ Tuesday, October 1, 2013 9:33 AM

Comments on this entry:

Gravatar # re: T-Sql: How to find records added in duplicate within a few seconds from each other
by mansoor Hussain at 5/31/2016 3:02 AM

i likes it … itxbestweb
Post A Comment
Title:
Name:
Email:
Comment:
Verification: