SQL Server query for count per hour

Question

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)

Here is a great place to start so we don't have to guess. spaghettidba.com/2015/04/24/… — Sean Lange
– Sean Lange, Commented Mar 17, 2017 at 15:51

InD · Accepted Answer · 2017-03-17 16:01:23Z

5

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

edited Mar 17, 2017 at 16:01

answered Mar 17, 2017 at 15:04

InD

2792 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kris Over a year ago

This one returned some results, however 2nd and 3rd column has (No column name) as names, and I am not sure how to read through them. the first row for example returns: 360642 14 13 that I assume would be - this id_Number has> 14events at>from13:00to13:59.59?

InD Over a year ago

You are assuming correctly. the user with the ID 360642 has 14 events in the 13th hour.

Kris Over a year ago

Could I also ask if I am to do the same but for 5 events in 30 minutes stretch, should I do -> (DATEPART(HH, datetime)/2) or (DATEPART(MI, Transaction_Time) /30), both queries return different results, I am unsure how to set the minutes value. Also the last column seems to be showing random numbers with HH and zeroes with MI? Any suggestions?

InD Over a year ago

if you want the User with the most events on top of the resultlist just add ORDER BY COUNT(event1) DESC at the end of the query

InD Over a year ago

For 5 events (per Hour) just adjust the HAVING part accordingly. But (DATEPART(HH, datetime)/2) or (DATEPART(MI, Transaction_Time) /30) doesn't work the way you want it.

|

Collectives™ on Stack Overflow

SQL Server query for count per hour

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related