1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| ;WITH
CTE_JOUR (hour_start, hour_end, nb_jour) AS
(
SELECT 0, 24, 1
UNION ALL SELECT 24, 48, 2
UNION ALL SELECT 48, 72, 3
UNION ALL SELECT 72, 96, 4
UNION ALL SELECT 96, 120, 5
UNION ALL SELECT 120, 144, 6
UNION ALL SELECT 144, 168, 7
)
SELECT C.nb_jour
, T.ImageId
, SUM(T.numberSeen) AS totalSeen
FROM CTE_JOUR AS C
INNER JOIN dbo.maTable AS T
ON DATEDIFF(hour, dateSeen, GETDATE()) >= C.hour_start
AND DATEDIFF(hour, dateSeen, GETDATE()) < C.hour_end
GROUP BY C.nb_jour
, T.ImageId |
Partager