January 14, 2013 at 2:03 am
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 (
LocationIDINTEGER NOT NULL
,LocationNameVARCHAR(100) NOT NULL
)
CREATE TABLE #Schedules (
ScheduleIDINTEGER NOT NULL
,LocationIDINTEGER NOT NULL
,DayOfTheWeekINTEGER NOT NULL
,StartTimeDATETIME
,EndTimeDATETIME
,ScheduleNameVARCHAR(100) NOT NULL
)
INSERT INTO #Locations
(LocationID,LocationName)
SELECT1,'Village Hall'
UNION
SELECT2,'High School'
INSERT INTO #Schedules
(ScheduleID,LocationID,DayOfTheWeek,StartTime,EndTime,ScheduleName)
SELECT1,1,1,'1899-01-01 17:00','1899-01-01 18:00','Zumba'
UNION
SELECT2,1,1,'1899-01-01 18:00','1899-01-01 19:00','Thai Boxing'
UNION
SELECT3,1,2,'1899-01-01 16:30','1899-01-01 17:30','Cardio'
UNION
SELECT4,1,2,'1899-01-01 17:45','1899-01-01 18:15','Nutrition'
UNION
SELECT5,1,3,'1899-01-01 18:15','1899-01-01 19:15','Thai Boxing'
UNION
SELECT6,1,3,'1899-01-01 19:30','1899-01-01 20:30','Legs, Bums and Tums'
UNION
SELECT7,1,4,'1899-01-01 17:15','1899-01-01 18:15','Zumba'
UNION
SELECT8,1,4,'1899-01-01 18:30','1899-01-01 19:30','Fat Burning'
UNION
SELECT9,1,4,'1899-01-01 20:45','1899-01-01 22:00','Karate Level 3'
UNION
SELECT10,1,5,'1899-01-01 09:30','1899-01-01 10:30','Cardio'
UNION
SELECT11,1,5,'1899-01-01 11:00','1899-01-01 12:00','Legs, Bums and Tums'
UNION
SELECT12,1,5,'1899-01-01 16:30','1899-01-01 17:30','Karate Level 1'
UNION
SELECT13,1,5,'1899-01-01 17:45','1899-01-01 18:45','Karate Level 2'
UNION
SELECT14,1,5,'1899-01-01 19:30','1899-01-01 21:00','Karate Level 3'
UNION
SELECT15,1,7,'1899-01-01 11:00','1899-01-01 11:30','Kids Fun Time'
UNION
SELECT16,1,7,'1899-01-01 11:45','1899-01-01 12:45','Zumba'
UNION
SELECT17,1,7,'1899-01-01 13:00','1899-01-01 14:00','Nutrition'
UNION
SELECT1,2,2,'1899-01-01 17:00','1899-01-01 18:00','Zumba'
UNION
SELECT2,2,2,'1899-01-01 18:00','1899-01-01 19:00','Thai Boxing'
UNION
SELECT3,2,4,'1899-01-01 16:30','1899-01-01 17:30','Cardio'
UNION
SELECT4,2,4,'1899-01-01 17:45','1899-01-01 18:15','Nutrition'
UNION
SELECT5,2,6,'1899-01-01 18:15','1899-01-01 19:15','Thai Boxing'
UNION
SELECT6,2,6,'1899-01-01 19:30','1899-01-01 20:30','Legs, Bums and Tums'
UNION
SELECT7,2,1,'1899-01-01 17:15','1899-01-01 18:15','Zumba'
UNION
SELECT8,2,1,'1899-01-01 18:30','1899-01-01 19:30','Fat Burning'
UNION
SELECT9,2,1,'1899-01-01 20:45','1899-01-01 22:00','Karate Level 3'
UNION
SELECT10,2,7,'1899-01-01 09:30','1899-01-01 10:30','Cardio'
UNION
SELECT11,2,7,'1899-01-01 11:00','1899-01-01 12:00','Legs, Bums and Tums'
UNION
SELECT12,2,7,'1899-01-01 16:30','1899-01-01 17:30','Karate Level 1'
UNION
SELECT13,2,7,'1899-01-01 17:45','1899-01-01 18:45','Karate Level 2'
UNION
SELECT14,2,7,'1899-01-01 19:30','1899-01-01 21:00','Karate Level 3'
UNION
SELECT15,2,3,'1899-01-01 11:00','1899-01-01 11:30','Kids Fun Time'
UNION
SELECT16,2,3,'1899-01-01 11:45','1899-01-01 12:45','Zumba'
UNION
SELECT17,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.
For all your clubs - Our Clubs.
Try out our new site today and see how it can help your club!
January 14, 2013 at 9:24 am
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.
January 14, 2013 at 9:42 am
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.
January 14, 2013 at 6:16 pm
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 (
LocationIDINTEGER NOT NULL
,LocationNameVARCHAR(100) NOT NULL
)
CREATE TABLE #Schedules (
ScheduleIDINTEGER NOT NULL
,LocationIDINTEGER NOT NULL
,DayOfTheWeekINTEGER NOT NULL
,StartTimeTIME
,EndTimeTIME
,ScheduleNameVARCHAR(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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 10:48 am
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.
January 15, 2013 at 5:44 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 16, 2013 at 1:20 am
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.
For all your clubs - Our Clubs.
Try out our new site today and see how it can help your club!
January 16, 2013 at 5:19 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply