| Author |
Topic |
|
smccreadie
Aged Yak Warrior
505 Posts |
 Posted - 2002-04-08 : 13:48:19
|
| I've got a table that records user logins. It stores username, some other stuff, and the getdate() value. A user will likely login multiple times per day.I'm trying to count the unique dates (without times) per username. So if user 12345 logs in 3 times today, no times tomorrow, and 5 times the following day - I want the query to report back as two.How do I do this in a select/group by statement? |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-08 : 13:57:15
|
| if a table #temp contained userID and LogonDate, you could write:select userid, count( distinct( datepart( dd, logondate ) ) )from #tempgroup by useridsetBasedIsTheTruepath<O> |
 |
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-04-08 : 14:00:20
|
| What if a user loged in on 03/08/02 and on 04/08/02. Wouldn't that be counted as one logon date if count(distinct(datepart(dd, logondate))) were used? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-08 : 14:06:53
|
| A little tweak to setbased's code solves that:select userid, count( distinct( Convert(char(8), logondate, 112 ) ) ) from #temp group by userid |
|
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-04-08 : 14:09:20
|
| Thanks all - that's what I needed. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-04-08 : 14:14:19
|
Or without a temp table:SELECT distinct username, count(distinct datepart(y, logintime)) FROM Logins GROUP BY username This will work for the year, in other words, Jan 1, 2002 will be the same as Jan 1, 2001If you need it to be different, add a 'year' component to the count.-Chad |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-08 : 14:29:52
|
quote:
Or without a temp table:
I was using #temp just as an example ... oops about the datepart() though  .setBasedIsTheTruepath<O> |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-04-08 : 15:02:27
|
| Sorry SetBased..I just saw the first line said #temp, and assumed you were using a temp table in the solution (As a lot of folks do on this board) I should have read your post more closly to see that your answer was basically the same as mine (Actually, since you were first, Mine was the same as yours).-Chad |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-08 : 15:18:04
|
| oh no worries ... yours wasn't the same as mine, though, since yours was right and mine had a problem setBasedIsTheTruepath<O> |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-04-08 : 15:21:51
|
| Wouldn't count(distinct datepart(y, logintime)) be wrong as well. Woudnt it count the distinct years that a user has logged in. If a user logged in today (03/08/02) and tomorrow (03/09/02) then we would get a count of 1. But if a user logged in on 12/31/02 and then 01/01/03 then we would get the correct number of 2.I think robvolk had the correct answer by casting the datetime to a char(8) of just the date. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-04-08 : 16:11:44
|
| using DATEDIFF may make it a bit faster:COUNT(DISTINCT DATEDIFF(day, '1900-01-01', logondate))1 Jan 1900 is arbitrary, but it might have to do less work with it since it's the internal day 0. Just tried this versus the CONVERT approach on a 1.6 million row table aggregating into 38 rows: the DATEDIFF runs in 6.5 secs, the CONVERT in 31.5. So definitely worth a look. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-04-09 : 01:41:14
|
| Yakoo,The y option is 'Day of year' not year. So it would work for a one year period.-Chad |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-04-09 : 02:22:07
|
| my apologies. It was Monday... hope you understand. |
|
|
|
|
counting unique dates