asked on

Get a count of records over the past 24 hours from a SQL table

I need to get the count of error messages from a SQL database table over the past 24 hours.

Example:

Time: Count:
1:00 AM 5 errors
2:00 AM 1 error
3:00 AM 10 errors
4:00 AM 5 errors
.......

12:00 PM 0 errors
.........

10:00 PM 6 errors

and so on. Below is my attempt but it is not correct.
select COUNT (messageType) as [Count], Datestamp
from Table1
where Datestamp between dateadd(hour, -24, CURRENT_TIMESTAMP)
AND CURRENT_TIMESTAMP
GROUP BY CAST(Datestamp as DATE) ,CASE
WHEN DATEPART(HOUR, Datestamp) > 12 THEN DATEPART(hour, Datestamp) - 12
ELSE DATEPART(Hour, Datestamp)
END , dateadd(hour, datediff(hour, 0, Datestamp), 0), Datestamp
ORDER BY dateadd(hour, datediff(hour, 0, Datestamp), 0) ASC;

HainKurt

try

with h as (select 0 n union select 1 union select 23),
     d as (
select count(1) cnt, DATEPART(hour, Datestamp) h
  from Table1
 where Datestamp > getdate()-1
 group by DATEPART(hour, Datestamp)
)
select n, cnt
  from h left join d on h.n=d.h

Open in new window

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

newjeep19

ASKER

Thanks the query works great

HainKurt

line 1 should be

with h as (select 0 n union select 1 union ... union select 23)

Open in new window

ie, from 0 to 23... somehow it is ripped when I pasted :)
I am assuming you figured it out...