CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Soham Shah
Soham Shah

Reputation: 571

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

Upvotes: 0

Views: 1139

Answers (2)

dcieslak
dcieslak

Reputation: 2715

The below should work.

WITH Periods AS (
    SELECT 8 AS num, (DATENAME(hour,  DATEADD(hour, -9, getDate())) + ' - ' + DATENAME(hour, DATEADD(hour, -8, getDate()))) as PERIOD
    UNION ALL
    SELECT num - 1,  (DATENAME(hour,  DATEADD(hour, -num, getDate())) + ' - ' + DATENAME(hour, DATEADD(hour, -num + 1, getDate()))) as PERIOD
     FROM Periods WHERE num> 0
) ,
 Confrim as (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))) )
select P.PERIOD, isnull(C.QT_CONFIRMED, 0) from  Periods P
left join  Confrim C
  on P.PERIOD = C.PERIOD
ORDER BY PERIOD

It simply generates periods and later it left joins periods to your query. You can find example here: SQL Fiddle sample

Additionally you can change order by to ORDER BY num desc. After that your periods will be sorted correctly.

Upvotes: 3

TaylorN
TaylorN

Reputation: 399

You could use a recursive CTE:

https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

https://dba.stackexchange.com/questions/14661/do-you-know-an-easy-way-to-generate-one-record-for-each-hour-of-the-past-12-hour

Upvotes: 1

Related Questions