February 23, 2010 at 11:48 pm
Hi all,
I've been away from the SQL world for a bit and have just returned... I have the following problem which is giving me a bit of a headache. I'm sure there's a "simple" way to do this but it's not coming to me....
The problem is that I need to 'flatten' the results of a query
from this:
MondayTuesdayWednesdayThursday
09:00 to 10:00
09:00 to 10:00
09:00 to 10:00
09:00 to 10:00
09:30 to 11:30
09:30 to 11:30
09:30 to 11:30
12:30 to 14:30
To this:
Monday Tuesday Wednesday Thursday
09:00 to 10:00 09:00 to 10:00 09:30 to 11:30 09:30 to 11:30
09:00 to 10:00 09:00 to 10:00 09:30 to 11:30 12:30 to 14:30
There can be a variable number of events for each day e.g 3 events on Monday, 2 on Tuesday etc AND there can be multiple events in the same timeslot on each day.
CREATE TABLE [dbo].[Schedule](
[ScheduleGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[PkTableId] int NOT NULL,
[ScheduleStart] [smalldatetime] NOT NULL,
[ScheduleEnd] [smalldatetime] NOT NULL
)
go
Insert Schedule (ScheduleStart, ScheduleEnd, PkTableGuid) Values('2009-09-07 09:00:00','2009-09-07 10:00:00', 1)
Insert Schedule (ScheduleStart, ScheduleEnd, PkTableGuid) Values('2009-09-08 09:00:00','2009-09-08 10:00:00', 1)
Insert Schedule (ScheduleStart, ScheduleEnd, PkTableGuid) Values('2009-09-09 09:30:00','2009-09-09 11:30:00', 1)
Insert Schedule (ScheduleStart, ScheduleEnd, PkTableGuid) Values('2009-09-07 09:00:00','2009-09-07 10:00:00', 1)
Insert Schedule (ScheduleStart, ScheduleEnd, PkTableGuid) Values('2009-09-10 12:30:00','2009-09-10 14:30:00', 1)
Insert Schedule (ScheduleStart, ScheduleEnd, PkTableGuid) Values('2009-09-08 09:00:00','2009-09-08 10:00:00', 1)
Insert Schedule (ScheduleStart, ScheduleEnd, PkTableGuid) Values('2009-09-10 09:30:00','2009-09-10 11:30:00', 1)
Insert Schedule (ScheduleStart, ScheduleEnd, PkTableGuid) Values('2009-09-09 09:30:00','2009-09-09 11:30:00', 1)
go
SELECT
Case DATEPART(weekday, S.ScheduleStart)
WHEN 2 THEN
CONVERT(varchar(5), S.ScheduleStart, 108) + ' to ' + CONVERT(varchar(5), S.ScheduleEnd, 108) ELSE ' ' END
AS Monday,
case DATEPART(weekday, S.ScheduleStart)
WHEN 3 THEN
CONVERT(varchar(5), S.ScheduleStart, 108) + ' to ' + CONVERT(varchar(5), S.ScheduleEnd, 108) ELSE ' ' END
AS Tuesday,
case DATEPART(weekday, S.ScheduleStart)
WHEN 4 THEN
CONVERT(varchar(5), S.ScheduleStart, 108) + ' to ' + CONVERT(varchar(5), S.ScheduleEnd, 108) ELSE ' ' END
AS Wednesday,
case DATEPART(weekday, S.ScheduleStart)
WHEN 5 THEN
CONVERT(varchar(5), S.ScheduleStart, 108) + ' to ' + CONVERT(varchar(5), S.ScheduleEnd, 108) ELSE ' ' END
AS Thursday
FROM Schedule S
I'm using SQL 2008
Thanks in advance
February 24, 2010 at 4:33 am
Use the ROW_NUMBER() function to work out which row the information should be on.
Also, PIVOT tends to be more readable than lots of CASE statements.
Try something like:
SELECT SYear, SWeek
,[2] AS Monday
,[3] As Tuesday
,[4] AS Wednesday
,[5] AS Thursday
FROM
(
SELECT
DATEPART(year, ScheduleStart) AS SYear
,DATEPART(week, ScheduleStart) AS SWeek
,DATEPART(weekday, ScheduleStart)AS SWeekday
,ROW_NUMBER()
OVER (PARTITION BY DATEADD(d, DATEDIFF(d, 0, ScheduleStart), 0) ORDER BY ScheduleStart, ScheduleGUID) AS RowNum
,CONVERT(varchar(5), ScheduleStart, 108) + ' to ' + CONVERT(varchar(5), ScheduleEnd, 108) AS Period
FROM Schedule
) D
PIVOT
(
MIN(Period)
FOR SWeekDay IN ([2], [3], [4], [5])
)
AS P
February 24, 2010 at 12:09 pm
Excellent, thanks a lot.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply