CODEWITHSUNDEEP
t-sql
Gary Wheeler
Gary Wheeler

Reputation: 39

Records based on time difference

I have a very strange request. I'm trying to create an SQL statement to do this. I know I can create a cursor but trying to see if it can be done is SQL

Here is my source data.

1 - 1:00 PM
2 - 1:02 PM
3 - 1:03 PM
4 - 1:05 PM
5 - 1:06 PM
6 - 1:09 PM
7 - 1:10 PM
8 - 1:12 PM
9 - 1:13 PM
10 - 1:15 PM

I'm trying to create a function that if I pass an interval it will return the resulting data set.

For example I pass in 5 minutes, then the records I would want back are records 1, 4, 7, & 10.

Is there a way to do this in SQL. Note: if record 4 (1:05 PM wasn't in the data set I would expect to see 1, 5, & 8. I would see 5 because it is the next record with a time greater than 5 minutes from record 1 and record 8 because it is the next record with a time greater than 5 minutes from record 5.

Upvotes: 2

Views: 49

Answers (1)

cha
cha

Reputation: 10411

Here is a create script that you should have provided:

declare @Table1 TABLE 
    ([id] int, [time] time)
;

INSERT INTO @Table1
    ([id], [time])
VALUES
    (1, '1:00 PM'),
    (2, '1:02 PM'),
    (3, '1:03 PM'),
    (4, '1:05 PM'),
    (5, '1:06 PM'),
    (6, '1:09 PM'),
    (7, '1:10 PM'),
    (8, '1:12 PM'),
    (9, '1:13 PM'),
    (10, '1:15 PM')
;

I would do this with this query:

declare @interval int
set @interval = 5

;with next_times as(
select id, [time], (select min([time]) from @Table1 t2 where t2.[time] >= dateadd(minute, @interval, t1.[time])) as next_time
from @Table1 t1
),
t as(
select id, [time], next_time
from next_times t1 where id=1
union all 
select t3.id, t3.[time], t3.next_time
from t inner join next_times t3
on t.next_time = t3.[time]
)
select id, [time] from t order by 1

-- results:
id          time
----------- ----------------
1           13:00:00.0000000
4           13:05:00.0000000
7           13:10:00.0000000
10          13:15:00.0000000

(4 row(s) affected)

It works even for the situations with a missing interval:

-- delete the 1:05 PM record
delete from @table1 where id = 4;

;with next_times as(
select id, [time], (select min([time]) from @Table1 t2 where t2.[time] >= dateadd(minute, @interval, t1.[time])) as next_time
from @Table1 t1
),
t as(
select id, [time], next_time
from next_times t1 where id=1
union all 
select t3.id, t3.[time], t3.next_time
from t inner join next_times t3
on t.next_time = t3.[time]
)
select id, [time] from t order by 1;

-- results:
id          time
----------- ----------------
1           13:00:00.0000000
5           13:06:00.0000000
8           13:12:00.0000000

(3 row(s) affected)

Upvotes: 3

Related Questions