Is it possible to group rows by difference to each other in T-SQL

user_119738 0
Viewed 114

Is it possible to write a SQL query that groups rows based on a datetime column value's difference from an adjacent row's value?

Let me give an example...I have a SQL query something like this:

SELECT
    Id,
    StartTime,
    EndTime,
    datediff(second, max(StartTime), EndTime)) as Duration
FROM Timings
ORDER BY StartTime

Which returns results something like this:

| ID | StartTime           | EndTime             | Duration
| 1  | 2017-10-06 10:59:48 | 2017-10-06 10:59:58 | 10
| 2  | 2017-10-06 11:00:02 | 2017-10-06 11:00:06 | 4
| 3  | 2017-10-06 11:00:15 | 2017-10-06 11:00:22 | 7
| 4  | 2017-10-06 11:00:30 | 2017-10-06 11:00:39 | 9
| 5  | 2017-10-06 15:34:31 | 2017-10-06 15:34:45 | 14
| 6  | 2017-10-06 15:34:48 | 2017-10-06 15:34:56 | 8
| 7  | 2017-10-06 15:34:52 | 2017-10-06 15:34:59 | 7

The important thing here is that the timings are in two batches, the first four were all done around 11am, and the second two around half past 3pm.

I want to get details about the start/end time of each batches of timings, the average duration, and the number of timings in a group. To do that what I need is a way to group the timings by batch, where a batch is defined as the group of timings with less than 30 seconds between the end of one and the start of the next, when ordered by start time. Is it possible?

Some related notes on the real situation...

  • The only really distinguishing feature of a batch is that the gap between the end of one timing and the start of the next will be significantly larger. Hard coding a fixed time limit would be OK.
  • As in this example there may be multiple batches on a day, so grouping by date is out.
  • In reality batches can be anything from a few minutes to several hours long, containing just a few timings, or several thousand. There are no set times that batches occur.
  • Take note of the timings on lines 6 & 7. Multiple timings do occur in parallel overlapping each other, the distinction of a large gap between the end of one and the start of the next still stands though as the way of identifying batches.
gaps-and-islands tsql sql-server sql
2 Answers

In SQL Server 2012+:

Using the window function lag() in a common table expression to get the datediff() of the current row starttime compared to the previous row value for endtime, and then sum() over() with conditional aggregation (comparision to the hardcoded value) to generate the batch:

;with cte as (
select *
  , datediff(second,lag(endtime) over (order by starttime),starttime) as prev_dat
from timings
)
select id, starttime, endtime, duration
  , sum(case when coalesce(prev_dat,31)>30 then 1 else 0 end) over (
    order by starttime
    ) as batch
from cte

rextester demo: http://rextester.com/OVNF90739

returns: 

+----+---------------------+---------------------+----------+-------+
| id |      starttime      |       endtime       | duration | batch |
+----+---------------------+---------------------+----------+-------+
|  1 | 2017-10-06 10:59:48 | 2017-10-06 10:59:58 |       10 |     1 |
|  2 | 2017-10-06 11:00:02 | 2017-10-06 11:00:06 |        4 |     1 |
|  3 | 2017-10-06 11:00:15 | 2017-10-06 11:00:22 |        7 |     1 |
|  4 | 2017-10-06 11:00:30 | 2017-10-06 11:00:39 |        9 |     1 |
|  5 | 2017-10-06 15:34:31 | 2017-10-06 15:34:45 |       14 |     2 |
|  6 | 2017-10-06 15:34:48 | 2017-10-06 15:34:56 |        8 |     2 |
|  7 | 2017-10-06 15:34:52 | 2017-10-06 15:34:59 |        7 |     2 |
+----+---------------------+---------------------+----------+-------+
user_23334990

You could group by the datetime parts

group by DATEPART(StartTime, yyyy) + DATEPART(StartTime, MM) + DATEPART(StartTime, DD)

You'd also have to change you select to match

user_32971630
Related