1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
|
WITH PLAGEH AS
(SELECT cast('2020-12-11 23:45' AS datetime2) AS PLAGEDEBUT,
cast('2020-12-12 00:00' AS datetime2) AS PLAGEFIN
UNION ALL SELECT PLAGEH.PLAGEFIN,
DATEADD(MINUTE, 15, PLAGEH.PLAGEFIN)
FROM PLAGEH
WHERE PLAGEH.PLAGEFIN <= '2020-12-12 01:00' ),
TMP AS
(SELECT 1 AS id,
'a' AS ev,
'2020-12-11 23:45' AS debut,
'2020-12-12 01:30' AS fin
UNION ALL SELECT 2,
'b',
'2020-12-11 23:37',
'2020-12-12 00:24'
UNION ALL SELECT 3,
'c',
'2020-12-12 00:07',
'2020-12-12 00:55')
SELECT PLAGEH.PLAGEDEBUT,
count(*) AS NB_EV
FROM PLAGEH
JOIN TMP ON PLAGEH.PLAGEFIN >= TMP.debut
AND PLAGEH.PLAGEDEBUT < TMP.fin
GROUP BY PLAGEH.PLAGEDEBUT
ORDER BY PLAGEH.PLAGEDEBUT OPTION (maxrecursion 0) |
Partager