I'm building hourly report(last 8 hour production count) from SQL Table CONFIRMATION via SQL Query. Query absolutely runs fine and gives proper results as follow:
SELECT
(DATENAME(hour, C.DT_CONFIRMED) + ' - ' + DATENAME(hour, DATEADD(hour, 1, C.DT_CONFIRMED))) as PERIOD,
SUM(C.QT_CONFIRMED) as QT_CONFIRMED
FROM
CONFIRMATION C
WHERE C.DT_CONFIRMED >= DATEADD(hh, -8 , '2015-12-03T11:00:00')
GROUP BY (DATENAME(hour, C.DT_CONFIRMED) + ' - ' +
DATENAME(hour, DATEADD(hour, 1, C.DT_CONFIRMED)))
ORDER BY PERIOD
I get following result:
Period QT_CONFIRMED
8 - 9 4
10 - 11 8
But instead of that, i want result in following forma:
Period QT_CONFIRMED
2 - 3 0
3 - 4 0
4 - 5 0
5 - 6 0
7 - 8 0
8 - 9 4
9- 10 0
10 - 11 8
Basically for all those hours where QT_CONFIRMED is zero, I want to show that in the report.
How can I achieve that?
CONFIRMATION Table looks like following:
DT_CONFIRMED QT_CONFIRMED ID_CONFIRMATION
2015-12-03T10:40:43 5 1
2015-12-03T10:48:33 3 2
2015-12-03T11:03:03 12 3
Thanks
Aucun commentaire:
Enregistrer un commentaire