How to split time intervals into disjoint time intervals and count number of rows in them?-CodePudding

I have data with recordings of people visiting some place. There are time intervals of visits. I want to count how many people are there at what time.

For example with toy data :

create table data ( id int, start_time datetime, end_time datetime);
insert into data values (1, '2019-11-06 09:32:00', '2019-11-06 09:41:00');
insert into data values (2, '2019-11-06 09:35:00', '2019-11-06 09:43:00');

I expect to get

    start_time              end_time           count 
2019-11-06 09:32:00   2019-11-06 09:35:00        1
2019-11-06 09:35:00   2019-11-06 09:41:00        2
2019-11-06 09:41:00   2019-11-06 09:43:00        1

How can I implement this efficiently in SQL Server?

CodePudding user response：

Seems like what you need to do is unpivot your times, use LEAD, to get the next time, and then re-JOIN to your table with the new "buckets". For the sample data we have, this works:

WITH Times AS(
    SELECT V.[Time] AS Start_Time,
           LEAD(V.Time) OVER (ORDER BY V.Time) AS End_Time
    FROM dbo.data d
         CROSS APPLY (VALUES(d.start_time),(d.end_time))V([Time]))
SELECT T.Start_Time,
       T.End_Time,
       COUNT(d.id) AS [Count]
FROM Times T
     LEFT JOIN dbo.data d ON d.start_time < T.End_Time
                         AND d.end_time > T.Start_Time
WHERE T.End_Time IS NOT NULL
GROUP BY T.Start_Time,
         T.End_Time;