SQL Server query to select all records except records repeating in specific time interval

Question

I want to select all records in a table except some records which are repeated for a CustId in given time interval.

Following is the simple example to understand requirement, Lets say I want to skip records repeating within 60 Mins (60 Mins is time interval) for same CustId. As you can see in below data, row Id 2 and 3 repeated for 'Cust1' and row Id 6 repeated for 'Cust2'.

+-----------------------------------+
| Id | CustId  | CapturedTime       |
+-----------------------------------+
| 1  | 'Cust1' | '2017-10-01 10:30' |
| 2  | 'Cust1' | '2017-10-01 10:45' | -- Duplicate Row
| 3  | 'Cust1' | '2017-10-01 11:35' | -- Duplicate Row
| 4  | 'Cust1' | '2017-10-01 12:40' |
| 5  | 'Cust2' | '2017-10-01 16:15' |
| 6  | 'Cust2' | '2017-10-01 17:00' | -- Duplicate Row
| 7  | 'Cust4' | '2017-10-02 08:00' |
| 8  | 'Cust1' | '2017-10-02 10:45' |
| 9  | 'Cust3' | '2017-10-02 13:00' |
| 10 | 'Cust3' | '2017-10-02 14:05' |
+----+---------+--------------------+

So the result should be

+-----------------------------------+
| Id | CustId  | CapturedTime       |
+-----------------------------------+
| 1  | 'Cust1' | '2017-10-01 10:30' |
| 4  | 'Cust1' | '2017-10-01 12:40' |
| 5  | 'Cust2' | '2017-10-01 16:15' |
| 7  | 'Cust4' | '2017-10-02 08:00' |
| 8  | 'Cust1' | '2017-10-02 10:45' |
| 9  | 'Cust3' | '2017-10-02 13:00' |
| 10 | 'Cust3' | '2017-10-02 14:05' |
+----+---------+--------------------+

Any help is appreciated.

https://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date — Twelfth, Nov 23 '17 at 15:17
@ZoharPeled Sorry for irrelevant tags .. I have removed those ... — Shri, Nov 26 '17 at 16:18

score 2 · Answer 1 · answered Nov 23 '17 at 16:02

Join the table to itself and use built-in function Lag

SELECT t1.Id, t1.CastId, t1.capturedtime from Customer t1 INNER JOIN 
(SELECT *,LAG(capturedtime,1,0) OVER(PARTITION BY CastId,CAST(capturedtime as date) ORDER BY CastId,capturedtime) as tempTime FROM Customer) t2
ON t1.Id = t2.Id AND DATEDIFF(MINUTE,tempTime, t1.capturedtime) >= 60

SqlKindaGuy · Accepted Answer · 2017-11-23T15:10:04.840

1

You can do it like this where you use LAG to compare times

declare @myt table (id int,custid nvarchar(50),capturedtime datetime
)

insert into @myt
values
( 1 , 'Cust1', '2017-10-01 10:30'),
( 2 , 'Cust1', '2017-10-01 10:45'),
( 3 , 'Cust1', '2017-10-01 11:35'),
( 4 , 'Cust1', '2017-10-01 12:40'),
( 5 , 'Cust2', '2017-10-01 16:15'),
( 6 , 'Cust2', '2017-10-01 17:00'),
( 7 , 'Cust4', '2017-10-02 08:00'),
( 8 , 'Cust1', '2017-10-02 10:45'),
( 9 , 'Cust3', '2017-10-02 13:00'),
( 10, 'Cust3', '2017-10-02 14:05')

select ID,Custid,CapturedTime from (
select * ,datediff(MINUTE,odtime,capturedtime) mm from (
select *,LAG(capturedtime,1,0) over(partition by custid,cast(capturedtime as date )order by custid,capturedtime) as odtime from @myt
)x
)z where mm >= 60
order by id

Result

edited Nov 23 '17 at 15:10

answered Nov 23 '17 at 15:03

SqlKindaGuy

3,501
2
12
29

That's correct assuming the question is about 2012 version. However there are also 2008 and 2005 version tags in the question, and for these versions lag doesn't exist... – Zohar Peled Nov 23 '17 at 15:18
True :) Lets see – SqlKindaGuy Nov 23 '17 at 15:29
@Shri Thats okay but i answered 15.03 he answered 16.02 :) So basically i was first. But glad you got it working – SqlKindaGuy Nov 26 '17 at 21:45

SQL Server query to select all records except records repeating in specific time interval

2 Answers2