mardi 8 décembre 2015

Hourly Report in SQL

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