SQL Time Grid - 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 2005 Forums

Transact-SQL (2005)

SQL Time Grid

Author

Topic

hermiod
Starting Member

7 Posts

Posted - 2008-01-21 : 07:05:03

Hi all.

I have the following SQL statement


SELECT DISTINCT CONVERT(VARCHAR,start_time,108) + ' - ' + CONVERT(VARCHAR,end_time,108), 
SUM((CASE day_of_week when 0 then 1 ELSE 0 END)) AS Monday, 
SUM((CASE day_of_week when 1 then 1 ELSE 0 END)) AS Tuesday, 
SUM((CASE day_of_week when 2 then 1 ELSE 0 END)) AS Wednesday, 
SUM((CASE day_of_week when 3 then 1 ELSE 0 END)) AS Thursday, 
SUM((CASE day_of_week when 4 then 1 ELSE 0 END)) AS Friday 
FROM ct_module, ct_event, ct_event_module, ct_event_room, ct_room, ct_site
WHERE ct_event.event_id = ct_event_module.event_id
AND ct_event_module.module_id = ct_module.module_id
AND ct_event.event_id = ct_event_room.event_id
AND ct_event_room.room_id = ct_room.room_id
AND ct_room.site_id = ct_site.site_id
AND SUBSTRING(ct_site.name,1,3) = 'KWA'
GROUP BY start_time, end_time, day_of_week

which produces the following results.
Column1 Monday Tuesday Wednesday Thursday Friday
08:30:00 - 09:30:00 0 0 0 0 28
08:30:00 - 09:30:00 0 0 0 17 0
08:30:00 - 09:30:00 0 0 23 0 0
08:30:00 - 09:30:00 0 21 0 0 0
08:30:00 - 09:30:00 19 0 0 0 0
08:30:00 - 10:00:00 0 0 2 0 0
08:30:00 - 10:00:00 0 1 0 0 0
08:30:00 - 10:20:00 1 0 0 0 0
08:30:00 - 10:30:00 0 0 0 0 1
08:30:00 - 10:30:00 0 0 0 3 0
08:30:00 - 10:30:00 0 0 1 0 0
08:30:00 - 10:30:00 0 3 0 0 0
08:30:00 - 10:30:00 1 0 0 0 0

what I want is to have one row per time period and have the counts for all the days on the same lines. eg.

08:30:00 - 09:30:00 19 21 23 17 28

I tried using WITH ROLLUP but was a bit confused that it produced about 20 rows where the time column was null despite the fact that there are no null values for either start_time or end_time!
That got me the single line on some time periods but there are still all the other individual lines included too.

any ideas?

ns_nataly
Starting Member

13 Posts

Posted - 2008-01-21 : 07:15:50

try to exclude DAY_Of_THE WEEK from the group

Natalia

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 07:36:51

Also learn to write ANSI style JOINs.

SELECT		CONVERT(CHAR(8), start_time, 108) + ' - ' + CONVERT(CHAR(8), end_time, 108),
		SUM(CASE day_of_week when 0 then 1 ELSE 0 END) AS Monday, 
		SUM(CASE day_of_week when 1 then 1 ELSE 0 END) AS Tuesday, 
		SUM(CASE day_of_week when 2 then 1 ELSE 0 END) AS Wednesday, 
		SUM(CASE day_of_week when 3 then 1 ELSE 0 END) AS Thursday, 
		SUM(CASE day_of_week when 4 then 1 ELSE 0 END) AS Friday 
FROM		ct_module AS m
INNER JOIN	ct_event_module AS em ON em.module_id = m.module_id
INNER JOIN	ct_event AS e ON e.event_id = em.event_id
INNER JOIN	ct_event_room AS er ON er.event_id = e.event_id
INNER JOIN	ct_room AS r ON r.room_id = er.room_id
INNER JOIN	ct_site AS s ON s.site_id = r.site_id
WHERE		s.name LIKE 'KWA%'
GROUP BY	start_time,
		end_time

E 12°55'05.25"
N 56°04'39.16"

hermiod
Starting Member

7 Posts

Posted - 2008-01-24 : 09:44:56

Is there any difference in functionality between ANSI style joins and Oracle style?

Or is it just two ways of doing exactly the same thing?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-24 : 10:05:08

They are doing the same thing, but "oracle" style is deprecated in next version of sql server.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources