SQL Query How do I do this?

[rsunra]

G'day All

I have the following table:

User_ID   Start_Date       Start_Time       Session_Type_ID
1,050  30/09/2004 00:00  01/01/1900 14:29                1
1,050  30/09/2004 00:00  01/01/1900 14:28                8
1,037  30/09/2004 00:00  01/01/1900 14:25                1
1,019  30/09/2004 00:00  01/01/1900 14:25                1
1,019  30/09/2004 00:00  01/01/1900 14:24                8
1,037  30/09/2004 00:00  01/01/1900 14:24                8
1,022  30/09/2004 00:00  01/01/1900 14:05                6
1,054  30/09/2004 00:00  01/01/1900 14:02                1
1,054  30/09/2004 00:00  01/01/1900 14:02                8
1,022  30/09/2004 00:00  01/01/1900 14:00                6

I need to know the following:

How can I get a DISTINCT Count by hour of the User_ID and if the hour is between 22:00 and 05:00 and the user ID is Less than 2 then the USer_ID count for that hour should be 2

this need to be for all date in the database

The results should look something like this:

Date         00    01   02   03 . . .  20    21   22    23
2004/09/29    2    2     4    4        7      7    3     3
2004/09/30    2    2     3    4        6      6    5     5
etc.

Thank you for your help in this matter

Cheers

Rich
A newbie to SQL

 

[NoCoolHandle]

Try this

create table abc (User_ID  int,Start_Date smalldatetime,StartTime smalldatetime,Session_TypeID int)
go
insert into abc
select

1050,  '09/30/2004 00:00' , '01/01/1900 14:29',                1 
Union select
1050,  '09/30/2004 00:00', '01/01/1900 14:28',                8
Union select
1037,  '09/30/2004 00:00',  '01/01/1900 14:25' ,               1
union select
1019 , '09/30/2004 00:00' , '01/01/1900 14:25',                1
union select
1019,  '09/30/2004 00:00',  '01/01/1900 14:24',                8
union select
1037,  '09/30/2004 00:00',  '01/01/1900 14:24',                8
union select
1022,  '09/30/2004 00:00' , '01/01/1900 14:05' ,               6
union select
1054,  '09/30/2004 00:00' , '01/01/1900 14:02' ,               1
union select
1054,  '09/30/2004 00:00',  '01/01/1900 14:02',                8
union select
1022,  '09/30/2004 00:00' , '01/01/1900 14:00',                6
union select
1050,  '09/30/2004 00:00' , '01/01/1900 12:29',                1 
Union select
1050,  '09/30/2004 00:00', '01/01/1900 12:28',                8
Union select
1037,  '09/30/2004 00:00',  '01/01/1900 13:25' ,               1
union select
1019 , '09/30/2004 00:00' , '01/01/1900 13:25',                1
union select
1019,  '09/30/2004 00:00',  '01/01/1900 13:24',                8
union select
1037,  '09/30/2004 00:00',  '01/01/1900 13:24',                8
union select
1022,  '09/30/2004 00:00' , '01/01/1900 15:05' ,               6
union select
1054,  '09/30/2004 00:00' , '01/01/1900 15:02' ,               1
union select
1054,  '09/30/2004 00:00',  '01/01/1900 15:02',                8
union select
1022,  '09/30/2004 00:00' , '01/01/1900 16:00',                6

go
select user_id,start_date,
count(case left(convert(varchar(10), starttime,108),2)
		when 11
		then  1 else null end) as '11',
count(case left(convert(varchar(10), starttime,108),2)
		when 12
		then  1 else null end) as '12',
count(case left(convert(varchar(10), starttime,108),2)
		when 13
		then  1 else null end) as '13',
count(case left(convert(varchar(10), starttime,108),2)
		when 14
		then  1 else null end) as '14',
count(case left(convert(varchar(10), starttime,108),2)
		when 15
		then  1 else null end) as '15',
count(case left(convert(varchar(10), starttime,108),2)
		when 16
		then  1 else null end) as '16',
count(case left(convert(varchar(10), starttime,108),2)
		when 17
		then  1 else null end) as '17'
from abc
group by User_id,Start_Date
go
drop table abc

The keys' here are you must use the Null in the count otherwise it will count it (nulls are automaticly removed from agregates)
and the count outside the case...

HTH


Rob

 

[NoCoolHandle]

I forgot to say

she'll be right mate!