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 29 30 31 32 33 34 35
| DECLARE @plage_horaire TABLE
(
debut_plage datetime NOT NULL
, fin_plage datetime NOT NULL
, PRIMARY KEY (debut_plage, fin_plage)
)
;WITH
CTE AS
(
SELECT DATEADD(hour, -24, GETDATE()) AS debut_plage
, DATEADD(second, 10, DATEADD(hour, -24, GETDATE())) AS fin_plage
UNION ALL
SELECT DATEADD(second, 10, debut_plage)
, DATEADD(second, 10, fin_plage)
FROM CTE
WHERE fin_plage < GETDATE()
)
INSERT INTO @plage_horaire
(
debut_plage
, fin_plage
)
SELECT debut_plage
, fin_plage
FROM CTE
OPTION (MAXRECURSION 8640)
SELECT PL.debut_plage
, PL.fin_plage
, COUNT(*) AS occurences
FROM @plage_horaire AS PL
INNER JOIN dbo.maTableLog AS L
ON L.DateLog BETWEEN PL.debut_plage AND PL.fin_plage
GROUP BY PL.debut_plage , PL.fin_plage HAVING COUNT(*) > 4 |
Partager