SQL Query to Convert Duration into rows per time period
Posted on 2004-04-19
My first question, hope the points aren't stingy.
Using SQL 2000
I'm trying to come up with an "elegant" way to convert appointment rows into a visible schedule. By elegant I mean I would prefer this to work in a view (i.e. no TSQL), but if it must be a query or even TSQL, so be it. More specifically, I want to go from start and finish times to individual slots based on a predefined duration (for example, 30 minutes). I already have a SQL view so that the data can be queried via Excel, but would like to show it in a similar fashion to a "schedule for the day". I'm pretty sure I could create a TSQL query that does this but I was hoping for something a little more elegant.
Here's an example: Given an appointment at 8:30am with duration of 1.5 hours, create a row for every 15 minute slot so it can show up in Excel like a calendar. Currently, Excel only sees this at 8:30am and 1.5 hours...I want it to see this as:
Appointments will never carry past a given day.
Here is a portion of the existing query's SELECT
Duration=CAST((DATEDIFF(minute, ap.ApptStart, ap.ApptStop)) AS int),