By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
CREATE TABLE Example
(
Name VARCHAR(512),
ts VARCHAR(512)
);
INSERT INTO Example (Name, ts) VALUES
('Bob', '05-05-2024 15:00'),
('Ted', '05-05-2024 15:06'),
('Alice', '05-05-2024 15:07'),
('John', '05-05-2024 15:08'),
('Denver', '05-05-2024 15:11');
SELECT * FROM Example
| Name | ts |
|---|---|
| Bob | 05-05-2024 15:00 |
| Ted | 05-05-2024 15:06 |
| Alice | 05-05-2024 15:07 |
| John | 05-05-2024 15:08 |
| Denver | 05-05-2024 15:11 |
WITH CTE (Chunk, sTime ) as
(
SELECT 1, Cast('05-05-2024 15:00' as DateTime)
UNION ALL
SELECT Chunk+1, dateadd(minute,5,sTime)
FROM CTE
WHERE sTime < '05-05-2024 15:15'
)
SELECT * FROM CTE
| Chunk | sTime |
|---|---|
| 1 | 2024-05-05 15:00:00.000 |
| 2 | 2024-05-05 15:05:00.000 |
| 3 | 2024-05-05 15:10:00.000 |
| 4 | 2024-05-05 15:15:00.000 |
WITH CTE (Chunk, sTime ) as
(
SELECT 1, Cast('05-05-2024 15:00' as DateTime)
UNION ALL
SELECT Chunk+1, dateadd(minute,5,sTime)
FROM CTE
WHERE sTime < '05-05-2024 15:15'
),
FoundMaxTS as
(
SELECT Chunk, sTime, Max(e.ts) as maxTS
FROM CTE
INNER JOIN Example e ON e.ts between sTime and dateadd(minute,5,sTime)
GROUP BY CHUNK, sTime
)
SELECT 'Bob' as Name, '05-05-2024 15:00' as ts, null as startTS, Cast('05-05-2024 15:00' as DateTime) as endTS, 0 as chunk
UNION ALL
SELECT e.Name, e.ts, c.sTime as startTS, dateadd(minute,5,c.sTime) as endTS, c.Chunk
FROM Example e
INNER JOIN FoundMaxTS f on f.maxTS = e.ts
INNER JOIN CTE c on c.sTime= f.stime
| Name | ts | startTS | endTS | chunk |
|---|---|---|---|---|
| Bob | 05-05-2024 15:00 | null | 2024-05-05 15:00:00.000 | 0 |
| Bob | 05-05-2024 15:00 | 2024-05-05 15:00:00.000 | 2024-05-05 15:05:00.000 | 1 |
| John | 05-05-2024 15:08 | 2024-05-05 15:05:00.000 | 2024-05-05 15:10:00.000 | 2 |
| Denver | 05-05-2024 15:11 | 2024-05-05 15:10:00.000 | 2024-05-05 15:15:00.000 | 3 |