Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivot to produce a timetable Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 2:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:36 AM
Points: 9, Visits: 43
Hi everyone,

I'm looking for a fix for an issue I'm working on. I've got a list of locations, start and end times and various textual attributes that I'd like portrayed in a timetable format. I've looked into using the PIVOT function, but can't seem get it working, especially when using date parameters (although I suspect this probably me rather than the function).

My code is below:

CREATE TABLE #Locations (
LocationID INTEGER NOT NULL
,LocationName VARCHAR(100) NOT NULL
)

CREATE TABLE #Schedules (
ScheduleID INTEGER NOT NULL
,LocationID INTEGER NOT NULL
,DayOfTheWeek INTEGER NOT NULL
,StartTime DATETIME
,EndTime DATETIME
,ScheduleName VARCHAR(100) NOT NULL
)

INSERT INTO #Locations
(LocationID,LocationName)
SELECT 1,'Village Hall'
UNION
SELECT 2,'High School'


INSERT INTO #Schedules
(ScheduleID,LocationID,DayOfTheWeek,StartTime,EndTime,ScheduleName)
SELECT 1,1,1,'1899-01-01 17:00','1899-01-01 18:00','Zumba'
UNION
SELECT 2,1,1,'1899-01-01 18:00','1899-01-01 19:00','Thai Boxing'
UNION
SELECT 3,1,2,'1899-01-01 16:30','1899-01-01 17:30','Cardio'
UNION
SELECT 4,1,2,'1899-01-01 17:45','1899-01-01 18:15','Nutrition'
UNION
SELECT 5,1,3,'1899-01-01 18:15','1899-01-01 19:15','Thai Boxing'
UNION
SELECT 6,1,3,'1899-01-01 19:30','1899-01-01 20:30','Legs, Bums and Tums'
UNION
SELECT 7,1,4,'1899-01-01 17:15','1899-01-01 18:15','Zumba'
UNION
SELECT 8,1,4,'1899-01-01 18:30','1899-01-01 19:30','Fat Burning'
UNION
SELECT 9,1,4,'1899-01-01 20:45','1899-01-01 22:00','Karate Level 3'
UNION
SELECT 10,1,5,'1899-01-01 09:30','1899-01-01 10:30','Cardio'
UNION
SELECT 11,1,5,'1899-01-01 11:00','1899-01-01 12:00','Legs, Bums and Tums'
UNION
SELECT 12,1,5,'1899-01-01 16:30','1899-01-01 17:30','Karate Level 1'
UNION
SELECT 13,1,5,'1899-01-01 17:45','1899-01-01 18:45','Karate Level 2'
UNION
SELECT 14,1,5,'1899-01-01 19:30','1899-01-01 21:00','Karate Level 3'
UNION
SELECT 15,1,7,'1899-01-01 11:00','1899-01-01 11:30','Kids Fun Time'
UNION
SELECT 16,1,7,'1899-01-01 11:45','1899-01-01 12:45','Zumba'
UNION
SELECT 17,1,7,'1899-01-01 13:00','1899-01-01 14:00','Nutrition'

UNION
SELECT 1,2,2,'1899-01-01 17:00','1899-01-01 18:00','Zumba'
UNION
SELECT 2,2,2,'1899-01-01 18:00','1899-01-01 19:00','Thai Boxing'
UNION
SELECT 3,2,4,'1899-01-01 16:30','1899-01-01 17:30','Cardio'
UNION
SELECT 4,2,4,'1899-01-01 17:45','1899-01-01 18:15','Nutrition'
UNION
SELECT 5,2,6,'1899-01-01 18:15','1899-01-01 19:15','Thai Boxing'
UNION
SELECT 6,2,6,'1899-01-01 19:30','1899-01-01 20:30','Legs, Bums and Tums'
UNION
SELECT 7,2,1,'1899-01-01 17:15','1899-01-01 18:15','Zumba'
UNION
SELECT 8,2,1,'1899-01-01 18:30','1899-01-01 19:30','Fat Burning'
UNION
SELECT 9,2,1,'1899-01-01 20:45','1899-01-01 22:00','Karate Level 3'
UNION
SELECT 10,2,7,'1899-01-01 09:30','1899-01-01 10:30','Cardio'
UNION
SELECT 11,2,7,'1899-01-01 11:00','1899-01-01 12:00','Legs, Bums and Tums'
UNION
SELECT 12,2,7,'1899-01-01 16:30','1899-01-01 17:30','Karate Level 1'
UNION
SELECT 13,2,7,'1899-01-01 17:45','1899-01-01 18:45','Karate Level 2'
UNION
SELECT 14,2,7,'1899-01-01 19:30','1899-01-01 21:00','Karate Level 3'
UNION
SELECT 15,2,3,'1899-01-01 11:00','1899-01-01 11:30','Kids Fun Time'
UNION
SELECT 16,2,3,'1899-01-01 11:45','1899-01-01 12:45','Zumba'
UNION
SELECT 17,2,3,'1899-01-01 13:00','1899-01-01 14:00','Nutrition'


SELECT *
FROM #Locations AS l

SELECT *
FROM #Schedules AS s

DROP TABLE #Locations
DROP TABLE #Schedules

I would like to be able to produce a report with Monday,Tuesday,Wednesday, etc... as the column headers and then a record (title,location,start,end) for each of the scheduled items, ordered by start time, for each day. For days where there are no scheduled items, I'd still like to show the day.

As always, all help is very appreciated.

Many thanks,

Kevin.
Post #1406599
Posted Monday, January 14, 2013 9:24 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:36 AM
Points: 1,565, Visits: 2,378
Kevin, there's some good stuff around about how to make calendar/schedule tables. Anyhow, I have a couple of questions. Do you want to see vacant time slots when there is nothing scheduled in that time slot all week? I might also re-think having separate start and end columns for each day of the week, since this will cause you to have a 'Sunday start', Sunday end', 'Monday start'... The code below will create a schedule for you to mess around with. I went ahead and put the start and end times in one column, but you can easily change that. Let us know if you have any questions.

WITH T1 (n) AS
(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
Tally (n) AS (SELECT n = ROW_NUMBER() OVER (PARTITION BY a.n ORDER BY a.n)
FROM T1 a, T1 b, T1 c),
FullDay (Slots) AS
(SELECT DATEADD(minute,t.n * 15,CAST('00:00:00' AS TIME))
FROM Tally t
WHERE t.n <= 84),
Data AS
(
SELECT
l.LocationName,
s.ScheduleID,
s.LocationID,
DayOfTheWeek = DATENAME(dw,s.DayOfTheWeek),
StartTime = CAST(s.StartTime AS TIME),
EndTime = CAST(s.EndTime AS TIME),
s.ScheduleName
FROM #Schedules s INNER JOIN #Locations l
ON s.LocationId = l.LocationID
)

SELECT
fd.Slots,
LocationName,
ScheduleName,
CASE WHEN DayOfTheWeek = 'Sunday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +
CONVERT(CHAR(5), d.EndTime)
ELSE NULL
END AS Sunday,
CASE WHEN DayOfTheWeek = 'Monday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +
CONVERT(CHAR(5), d.EndTime)
ELSE NULL
END AS Monday,
CASE WHEN DayOfTheWeek = 'Tuesday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +
CONVERT(CHAR(5), d.EndTime)
ELSE NULL
END AS Tuesday,
CASE WHEN DayOfTheWeek = 'Wednesday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +
CONVERT(CHAR(5), d.EndTime)
ELSE NULL
END AS Wednesday,
CASE WHEN DayOfTheWeek = 'Thursday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +
CONVERT(CHAR(5), d.EndTime)
ELSE NULL
END AS Thursday,
CASE WHEN DayOfTheWeek = 'Friday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +
CONVERT(CHAR(5), d.EndTime)
ELSE NULL
END AS Friday,
CASE WHEN DayOfTheWeek = 'Saturday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +
CONVERT(CHAR(5), d.EndTime)
ELSE NULL
END AS Saturday
FROM FullDay fd LEFT OUTER JOIN Data d
ON fd.Slots = CAST(d.StartTime AS TIME)
WHERE fd.Slots >= '09:00:00'
AND fd.Slots <= '21:00:00'



Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1406805
Posted Monday, January 14, 2013 9:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:36 AM
Points: 1,565, Visits: 2,378
Kevin, also, I did not notice this was your first post, so welcome to the forum, and thank you for posting sample data as you have. You will notice a couple of CTE's in my code, 'T1', and 'Tally' that may be unfamiliar to you. The 'Tally' cte is used simply to generate all possible 15 minute incremental time slots. For an explanation of what a 'Tally' table is, and how/why you might want to use it, you should read Jeff Moden's article http://www.sqlservercentral.com/articles/T-SQL/62867/ It's a great tool to have when you need it. Also, I'm just curious, why are you storing DayOfTheWeek as an integer, and the StartTime and EndTime as DATETIME when you are only using the time component? Why not just store the start and stop times as TIME? Also, you don't really have a way to produce a calendar view over time. For example, what happens if I change, say, 'Karate Level 3' from Saturday to Monday, but I make the change on Tuesday? Once you make the change, the class will no longer appear on your Saturday schedule, which will be what you eventually want, but you may miss the upcoming Saturday class. Maybe not an issue, but many things to think about when doing something like this. Basically what you have here is like a paper calendar, where you have all your stuff written in pencil. When you want to change something, you have to erase what already exists.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1406816
Posted Monday, January 14, 2013 6:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
I have to agree with Greg that TIME data type is much easier to work with in this case. So your DDL would be:

CREATE TABLE #Locations (
LocationID INTEGER NOT NULL
,LocationName VARCHAR(100) NOT NULL
)

CREATE TABLE #Schedules (
ScheduleID INTEGER NOT NULL
,LocationID INTEGER NOT NULL
,DayOfTheWeek INTEGER NOT NULL
,StartTime TIME
,EndTime TIME
,ScheduleName VARCHAR(100) NOT NULL
)


Then the same sample data (the 1899 dates will get saved as TIME just fine) and then this (alternate solution), which works similarly to what Greg has done.

;WITH Schedule (TimeSlot) AS (
SELECT CAST(CAST(DATEADD(minute, 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1), '09:00') AS TIME) AS VARCHAR(5))
FROM (VALUES ($),($),($),($),($),($),($)) a(n)
,(VALUES ($),($),($),($),($),($),($)) b(n)) -- exactly 49 time slots are needed 09:00 - 21:00
SELECT TimeSlot
,ScheduleName
,LocationName
,Sunday=MAX(CASE WHEN DayOfTheWeek = 1 THEN TimePeriod END)
,Monday=MAX(CASE WHEN DayOfTheWeek = 2 THEN TimePeriod END)
,Tuesday=MAX(CASE WHEN DayOfTheWeek = 3 THEN TimePeriod END)
,Wednesday=MAX(CASE WHEN DayOfTheWeek = 4 THEN TimePeriod END)
,Thursday=MAX(CASE WHEN DayOfTheWeek = 5 THEN TimePeriod END)
,Friday=MAX(CASE WHEN DayOfTheWeek = 6 THEN TimePeriod END)
,Saturday=MAX(CASE WHEN DayOfTheWeek = 7 THEN TimePeriod END)
FROM Schedule a
LEFT OUTER JOIN #Schedules c ON a.TimeSlot = c.StartTime
LEFT OUTER JOIN #Locations d ON c.LocationID = d.LocationID
CROSS APPLY (SELECT CAST(StartTime AS VARCHAR(5)) + ' - ' + CAST(StartTime AS VARCHAR(5))) b(TimePeriod)
GROUP BY TimeSlot, ScheduleName, LocationName

DROP TABLE #Locations
DROP TABLE #Schedules


My personal preferences in cases like this is to combine the calendar (time slot) generation with the Tally table. And I'm also using a different form of a Tally table, just to show you that you have many options.

I also through in a CROSS APPLY to create TimePeriod, which significantly shortens the code.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1406998
Posted Tuesday, January 15, 2013 10:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:36 AM
Points: 1,565, Visits: 2,378
dwain.c My personal preferences in cases like this is to combine the calendar (time slot) generation with the Tally table

Yep, mine is to always keep things as separate and granular as possible so as to make troubleshooting easier, then combine only when necessary. It's funny how folks perceive the same thing in different ways. I'm thinking maybe your way results in a little less memory usage.

I also through in a CROSS APPLY to create TimePeriod, which significantly shortens the code.

I've not really used CROSS APPLY, or any APPLY for that matter, since I'm mainly hitting DB2, but you guys sure do some nifty stuff with it. Thanks Dwain, I always learn something from your posts.


Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1407376
Posted Tuesday, January 15, 2013 5:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
Greg Snidow (1/15/2013)
dwain.c My personal preferences in cases like this is to combine the calendar (time slot) generation with the Tally table

Yep, mine is to always keep things as separate and granular as possible so as to make troubleshooting easier, then combine only when necessary. It's funny how folks perceive the same thing in different ways. I'm thinking maybe your way results in a little less memory usage.

I also through in a CROSS APPLY to create TimePeriod, which significantly shortens the code.

I've not really used CROSS APPLY, or any APPLY for that matter, since I'm mainly hitting DB2, but you guys sure do some nifty stuff with it. Thanks Dwain, I always learn something from your posts.


Greg - Thanks for the compliment!

I understand your notion of granularity and agree that it is sound. I'm not sure if you ever heard of a programming language called APL (in case not: http://en.wikipedia.org/wiki/APL_(programming_language))? I worked for a number of years in it and it is known to be cryptic and famous for its one liners. I was involved frequently in competitions to solve a problem in the single, shortest line of code possible. Perhaps my roots are showing. [I'm a strong believer that my experience using it has allowed me to much easier embrace the concept of sets as employed by SQL - but that's another story.]

On the other hand, granularity might be to a certain extent in the eye of the beholder. For example if you often have to work with generated calendars, the construct may become familiar enough to you that you find it sufficiently granular to stand alone.

Paul White (http://www.sqlservercentral.com/Authors/Articles/Paul_White/46040/) has a couple of excellent articles on using APPLY that are a must read.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1407525
Posted Wednesday, January 16, 2013 1:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:36 AM
Points: 9, Visits: 43
Firstly, thank you all for your replies...in a word, you guys are pretty awesome at this whole SQL "thing"

I went back to my original design and changed the StartTime and EndTime values to TIME as Greg mentioned and have used both Greg and Dwain.C's implementations.

Thanks again for all the code, but more importantly your reasoning and discussion behind why you did what you did. At least now I'll have some knowledge and some examples to back it up!

Cheers,

Kev.
Post #1407661
Posted Wednesday, January 16, 2013 5:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:36 AM
Points: 1,565, Visits: 2,378
kevin.phillips
Thanks again for all the code, but more importantly your reasoning and discussion behind why you did what you did. At least now I'll have some knowledge and some examples to back it up!


Kevin, thank you for the feedback and kind words. If you want to get better at your SQL, this is a great place to be. Here you will see problems you might not ever encounter with your own data, and working on other's problems helps you learn and will keep up your skills (that's why I do it). I am truly humbled by the ability and tenacity of some of the folks here. Good luck.


Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1407766
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse