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.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2003-08-12 : 14:21:16
|
Ok, I know enough to know the general theory of how to do this, but my brain is about to explode after staring at it for too long. Help!Given:CREATE TABLE USERS (user_id int PRIMARY KEY, signed up datetime) I want to know how many users signed up during 1 hour periods, as measured on a couple of days. So my desired results are something like: 10-Aug 11-Aug 12-Aug6am 17 12 177am 21 28 218am 25 22 239am 21 24 3010am 28 26 52 ..Clearly this is a situation for counting cases, but I just cannot get it.Help, please?Thanks-b |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-08-12 : 14:44:33
|
| create table t (id int, dt datetime)select datepart(hh, dt) hour,sum(case when datepart(dd, dt)=10 then 1 else 0 end) Aug10,sum(case when datepart(dd, dt)=11 then 1 else 0 end) Aug11,sum(case when datepart(dd, dt)=12 then 1 else 0 end) Aug12from t where datepart(mm, dt)=8group by datepart(hh, dt) |
 |
|
|
|
|
|
|
|
Pivot table help..