mssql calculate total by hour

Question

I have a table that contains basically an error log. These errors arrive based on various factors and have various severities. I am trying to get a query put together the total of each severity per hour in a 24 hour period.

I have it working, except for one small item. The results are sorted by the Hour, which always starts at 0. What I need is for it to be in true time sequence. Meaning that if the query is run at noon on 6/23, the query needs to have the first record at noon 6/22.

Sample table data: ID created severity

FL41988194-51133    4/13/2014 20:21 critical
JO03982444-74849    4/14/2014 12:46 major
JO03982444-74852    4/14/2014 12:46 major
JO03982444-74855    4/14/2014 12:46 major
BY79194841-06182    4/19/2014 19:54 major
BY79194841-06183    4/19/2014 19:54 major
BY79194841-06184    4/19/2014 19:54 major
TV90425333-88384    5/20/2014 7:02  major
FZ23706935-25024    6/7/2014 14:56  major
SY05532197-47119    6/12/2014 3:57  major

Here is my current code:

    select 

    [Hour], ISNULL([Critical],0) as Critical, ISNULL([Major],0) as Major, ISNULL([Minor],0) as Minor, ISNULL([Warning],0) as Warning,  
    ISNULL([Information],0) as Information, 
    ISNULL([Critical],0) + ISNULL([Warning],0) + ISNULL([Major],0) + ISNULL([Minor],0) + ISNULL([Information],0) as [Total]

    from
    (SELECT 
        DATEPART(hh, created) as 'Hour',
        [severity],
          count([id]) as incidents
      FROM [ALARM_TRANSACTION_SUMMARY]
      where created >= GETDATE()-1 
      group by DATEPART(hh, created), severity
    **Union ALL
   select NumberValue, NULL, NULL
   From NumberTable**
      ) PS
    PIVOT
    (
    SUM (incidents)
    FOR severity IN
    ( [Critical], [Information], Major, Minor, Warning)
    ) AS pvt

The Current output would look like this:

Hour      Critical     Major     Minor     Warning    Information     Total 

----      --------     -----     -----     -------    -----------     -----
 0            0          0         0         0            0             0
 1            0          0         0         0            0             0
 2            0          0         0         0            0             0
 3            0          1         0         0            0             1
 4            0          0         0         0            0             0
 5            0          0         0         0            0             0
 6            0          0         0         0            0             0
 7            0          1         0         0            0             1
 8            0          0         0         0            0             0
 9            0          0         0         0            0             0
 10           0          0         0         0            0             0
 11           0          0         0         0            0             0
 12           0          3         0         0            0             3
 13           0          0         0         0            0             0
 14           0          1         0         0            0             1
 15           0          0         0         0            0             0
 16           0          0         0         0            0             0
 17           0          0         0         0            0             0
 18           0          0         0         0            0             0
 19           0          2         0         0            0             2
 20           1          0         0         0            0             1
 21           0          0         0         0            0             0
 22           0          0         0         0            0             0
 23           0          0         0         0            0             0
 

As I said above, this does give me the hourly totals, but the totals are in the wrong sort order...so if I ran this at 2pm, I need the output to look like this:

Hour      Critical     Major     Minor     Warning    Information     Total 

----      --------     -----     -----     -------    -----------     -----
 14           0          1         0         0            0             1
 15           0          0         0         0            0             0
 16           0          0         0         0            0             0
 17           0          0         0         0            0             0
 18           0          0         0         0            0             0
 19           0          2         0         0            0             2
 20           1          0         0         0            0             1
 21           0          0         0         0            0             0
 22           0          0         0         0            0             0
 23           0          0         0         0            0             0
 0            0          0         0         0            0             0
 1            0          0         0         0            0             0
 2            0          0         0         0            0             0
 3            0          1         0         0            0             1
 4            0          0         0         0            0             0
 5            0          0         0         0            0             0
 6            0          0         0         0            0             0
 7            0          1         0         0            0             1
 8            0          0         0         0            0             0
 9            0          0         0         0            0             0
 10           0          0         0         0            0             0
 11           0          0         0         0            0             0
 12           0          3         0         0            0             3
 13           0          0         0         0            0             0
 

One other thing. This is feeding a report that requires the data to be presented as above (the charting software isn't smart enough to use the raw data and subtotal by the timestamps).

Answer 1

Try this query.

select 

[Hour], ISNULL([Critical],0) as Critical, ISNULL([Major],0) as Major, ISNULL([Minor],0) as Minor, ISNULL([Warning],0) as Warning,  
ISNULL([Information],0) as Information, 
ISNULL([Critical],0) + ISNULL([Warning],0) + ISNULL([Major],0) + ISNULL([Minor],0) + ISNULL([Information],0) as [Total]

from
(SELECT 
    DATEPART(hh, created) as 'Hour',
    DATEPART(hh, DATEADD(hh,-DATEPART(hh, created),GetDate())) AS SortHour,
    [severity],
      count([id]) as incidents
  FROM [ALARM_TRANSACTION_SUMMARY]
  where created >= GETDATE()-1 
  group by DATEPART(hh, created), severity
  Union ALL
   select NumberValue,DATEPART(hh, DATEADD(hh,-NumberValue,GetDate())), NULL, NULL
   From NumberTable
  ) PS
PIVOT
(
SUM (incidents)
FOR severity IN
( [Critical], [Information], Major, Minor, Warning)
) AS pvt
order by SortHour

Answer 2

(original query)
ORDER BY
    CASE WHEN [Hour] >= DATEPART(HOUR, GETDATE())
        THEN [Hour]
        ELSE [Hour] + 24
    END

Answer 3

UNTESTED but from appearances this should work; don't think we need to include it in the select.

SELECT   
 [Hour], ISNULL([Critical],0) as Critical, ISNULL([Major],0) as Major, ISNULL([Minor],0) as Minor, ISNULL([Warning],0) as Warning,  
 ISNULL([Information],0) as Information, 
 ISNULL([Critical],0) + ISNULL([Warning],0) + ISNULL([Major],0) + ISNULL([Minor],0) + ISNULL([Information],0) as [Total]
FROM
    (SELECT 
        DATEPART(hh, created) as 'Hour',
        [severity],
          count([id]) as incidents,
        max(created) as SortDate
      FROM [ALARM_TRANSACTION_SUMMARY]
      where created >= GETDATE()-1 
      group by DATEPART(hh, created), severity, max(created)
      ) PS
    PIVOT
    (
    SUM (incidents)
    FOR severity IN
    ( [Critical], [Information], Major, Minor, Warning)
    ) AS pvt
    ORDER BY sortDate desc;