1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
|
SELECT vue.heures, case
when vue.heures = 1 then vue.c0
when vue.heures = 2 then vue.c1
...
when vue.heures = 22 then vue.c22
when vue.heures = 23 then vue.c23
end
FROM (
SELECT dateadd(dd, datediff(dd,0, DATE), 0), DATEPART(HH,HEURE) as heures,
SUM(CASE WHEN DATEPART(HH,HEURE) = 0 THEN QP ELSE 0 END) as c0,
SUM(CASE WHEN DATEPART(HH,HEURE) <= 1 THEN QP ELSE 0 END) as c1,
SUM(CASE WHEN DATEPART(HH,HEURE) <= 2 THEN QP ELSE 0 END) as c2,
..
SUM(CASE WHEN DATEPART(HH,HEURE) <= 22 THEN QP ELSE 0 END) as c22,
SUM(CASE WHEN DATEPART(HH,HEURE) <= 23 THEN QP ELSE 0 END) as c23
FROM VPASSAGE
WHERE DATE = CONVERT(VARCHAR,GETDATE()-1,112)
AND JL = 'EM'
GROUP BY dateadd(dd, datediff(dd,0, DATE), 0), DATEPART(HH,HEURE)
) AS vue; |
Partager