Duplicates - Per Hour - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Duplicates - Per Hour

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-14 : 16:28:01

I've got a table that tracks user logins. I want to count logins per hour, but any single user can only be counted once per hour.

Here's a sample table

CREATE TABLE Logins (UserID INT, LoginTime DATETIME)

CREATE TABLE Users (UserID INT, ...)

-- All the Login attempts are returned by...

SELECT U.UserID, L.LoginTime
FROM Logins L
INNER JOIN Users U ON U.UserID = L.UserID

How would you do a GROUP BY so no UserID would appear more than once in a clock hour?

Time granularity on the clock hour is what I'm using. e.g. each hour starts on the clock hour (as opposed to requiring Logins being more than 60 minutes apart.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-07-14 : 16:30:35

How about:

count(distinct userid)
grouping on the hours