CODEWITHSUNDEEP
sql-server
Kris
Kris

Reputation: 342

SQL Server query for count per hour

Using SQL Server 2014 - I am struggling with a query that would search/filter with 2 criteria (one of them is per hour). I have found various solutions that work separately but I have dificulties in joining them together as one.

I have 

UserID, event1, timestamp

I would like to select: if the UserID has records 10 or more times in event1 column per hour for each hour (24hrs)

Upvotes: 0

Views: 8154

Answers (1)

InD
InD

Reputation: 259

Try this one: 

SELECT UserID, COUNT(event1) as EventsPerHour, DATEPART(HH, timestamp) as Hour
FROM [TABLE]
GROUP BY UserID, DATEPART(HH, timestamp)
HAVING COUNT(event1) >= 10

EDIT
If your table contains data for more than 24 hours you probably want to specify the date you want to filter:
Only data from today:

SELECT UserID, COUNT(event1), DATEPART(HH, datetime)
FROM [TABLE]
WHERE DATEPART(HH, datetime) = DATEPART(DAY, getdate())
GROUP BY UserID, DATEPART(HH, datetime)
HAVING COUNT(event1) >= 10

Only data from the last 24 hours:

SELECT UserID, COUNT(event1), DATEPART(HH, datetime)
FROM [TABLE]
WHERE datetime between GETDATE() and DATEADD(HH, -24, GETDATE())
GROUP BY UserID, DATEPART(HH, datetime)
HAVING COUNT(event1) >= 10

Upvotes: 5

Related Questions