SQL Server: Use OVER / Partition to get records by the minute ( or a time interval)

Question

I am trying to get the records grouped by the minute they were running in. In example below, I have 2 events a01 and a02. I would like to get the following

min 10:34 - a01
min 10:35 - a01
min 10:36 - a01
min 10:36 - a02
...
min 10:38 - a01
min 10:38 - a02
min 10:39 - a02

So, I am currently using a minute as the time interval. Can you please point me to some examples for this.

Create SQL below:

CREATE TABLE test_t1 (
    t1 VARCHAR(150)
    ,StartTime DATETIME NULL
    ,EndTime DATETIME NULL
    );

INSERT INTO test_t1 (
    t1
    ,StartTime
    ,EndTime
    )
VALUES (
    'a01'
    ,convert(DATETIME, '20180101 10:34:09.630')
    ,convert(DATETIME, '20180101 10:38:09.630')
    );

INSERT INTO test_t1 (
    t1
    ,StartTime
    ,EndTime
    )
VALUES (
    'a02'
    ,convert(DATETIME, '20180101 10:36:09.630')
    ,convert(DATETIME, '20180101 10:39:09.630')
    );

Felix Pamittan · Answer 1 · 2018-01-26T17:09:50.830

You need a Tally Table for this.

DECLARE
    @minDateTime AS DATETIME,
    @maxDateTime AS DATETIME;

SELECT
    @minDateTime = MIN(StartTime),
    @maxDateTime = MAX(EndTime)
FROM test_t1;

DECLARE @Range AS INT = DATEDIFF(MINUTE, @minDateTime, @maxDateTime);

;WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
CteTally(N) AS(
    SELECT TOP(@Range) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E8
)
SELECT
    tt.t1,
    MinInterval = DATEADD(MINUTE, ct.N - 1, tt.StartTime)
FROM test_t1 tt
INNER JOIN CteTally ct
    ON DATEADD(MINUTE, ct.N - 1, tt.StartTime) <= tt.EndTime
ORDER BY
    tt.t1, MinInterval;

Brief explanation of the Tally Table query taken from the article:

Selecting N rows in SQL Server

ONLINE DEMO

score 1 · Accepted Answer · answered Jan 26 '18 at 17:07

Recursive CTE can be used to solve this kind of problems

with cte as (
    select 
        t1, StartTime = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, StartTime), 0)
        , EndTime = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, EndTime), 0)
    from 
        test_t1
)
, rcte as (
    select
        t1, StartTime, EndTime, convert(char(5), StartTime, 108) res
    from
        cte
    union all
    select
        t1, dateadd(mi, 1, StartTime), EndTime, convert(char(5), dateadd(mi, 1, StartTime), 108)
    from
        rcte
    where
        StartTime < EndTime
)

select
    t1, res
from
    rcte
order by t1

option (maxrecursion 0)

SQL Server: Use OVER / Partition to get records by the minute ( or a time interval)

2 Answers2