January 15, 2009 at 3:05 pm
I need to create a query/SP that will take appointment data and fill in the blanks with data from a time table. An example would be a person is scheduled for an appointment from 8:00 to 8:45. That person is available for scheduling from 7 am to 6 pm so I need to fill in the time from 7-8 and from 8:45-6 with empty 15 minute time slots. My time table is populated with all the 15 minute time frames from 7-6.
This seems simple at face value but the schedule table that I pull from only has a start time and a duration field that is a char(5) (Not my design). I have parsed it out to a start time and end time but cannot seem to get the query to work. Any help would be appreciated.
Sample data
Schedule Table
Start_Time End_Time Duration
1/1/1900 8:00:00 AM1/1/1900 8:45:00 AM0045
Time Table
Schedule_Time
1/1/1900 7:00:00 AM
1/1/1900 7:15:00 AM
1/1/1900 7:30:00 AM
TIA
Doug
January 16, 2009 at 6:23 am
Hi doug,
[edit]Alright, take 2
I did not find a way to take advantage of the Duration column.
Please be warned that this query will most probably not perform very well on bigger datasets.
With no index, 3 tables scans and a sort it could definitly be a killer with real data.
I suggest posting your execution plan here after adapting this to your model if you need help tuning the query.
Maybe some people here have a solution that would avoid the triangular join?
Fun friday morning challenge 😀
DROP TABLE #Schedule
DROP TABLE #Time
CREATE TABLE #Schedule(
Start_Time smalldatetime,
End_Time smalldatetime,
Duration int
)
CREATE TABLE #Time(
Schedule_Time smalldatetime
)
INSERT #Schedule
SELECT '1/1/1900 8:00:00 AM','1/1/1900 8:45:00 AM',45 UNION
SELECT '1/1/1900 9:00:00 AM','1/1/1900 9:15:00 AM',15 UNION
SELECT '1/1/1900 9:15:00 AM','1/1/1900 9:30:00 AM',15 UNION
SELECT '1/1/1900 10:00:00 AM','1/1/1900 10:15:00 AM',30
INSERT #Time
SELECT '1/1/1900 7:00:00 AM' UNION
SELECT '1/1/1900 7:15:00 AM' UNION
SELECT '1/1/1900 7:30:00 AM' UNION
SELECT '1/1/1900 7:45:00 AM' UNION
SELECT '1/1/1900 8:00:00 AM' UNION
SELECT '1/1/1900 8:15:00 AM' UNION
SELECT '1/1/1900 8:30:00 AM' UNION
SELECT '1/1/1900 8:45:00 AM' UNION
SELECT '1/1/1900 9:00:00 AM' UNION
SELECT '1/1/1900 9:15:00 AM' UNION
SELECT '1/1/1900 9:30:00 AM' UNION
SELECT '1/1/1900 9:45:00 AM' UNION
SELECT '1/1/1900 10:00:00 AM' UNION
SELECT '1/1/1900 10:15:00 AM' UNION
SELECT '1/1/1900 10:30:00 AM' UNION
SELECT '1/1/1900 10:45:00 AM' UNION
SELECT '1/1/1900 11:00:00 AM'
SELECT
#Time.Schedule_Time as Schedule_Start_Time
, DateAdd(minute,15,#Time.Schedule_Time) AS Schedule_End_Time
FROM
#Time
LEFT JOIN (
SELECT
T.Schedule_Time
FROM
#Time T,#Schedule S
WHERE
T.Schedule_Time >= S.Start_Time
AND DateAdd(minute,15,T.Schedule_Time) <= S.End_Time
) AS UsedTimeSlots
ON #Time.Schedule_Time = UsedTimeSlots.Schedule_Time
WHERE
UsedTimeSlots.Schedule_Time IS NULL
ORDER BY
#Time.Schedule_Time
January 16, 2009 at 7:38 am
If I understand your question correctly then I think this will work (assumes you have the code to get the end time):
CREATE PROCEDURE fill_schedule
AS
SET NOCOUNT ON
-- code to populate #schedule here
INSERT INTO dbo.schedule
SELECT
T.Schedule_Time,
'0015' AS duration
FROM
time AS T LEFT JOIN
#schedule AS S ON
T.Schedule_Time BETWEEN S.Start_Time AND S.End_Time
WHERE
S.Start_Time IS NULL
RETURN
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 16, 2009 at 8:24 am
Using the test data I posted and your query Jack I am missing 3 time slots
08:45-09:00
09:30-09:45
10:00-10:15
But your join is much more straightforward, I guess a mix of our two queries does the job the best:
SELECT
T.Schedule_Time
, DateAdd(minute,15,T.Schedule_Time) AS Schedule_End_Time
FROM
#time AS T LEFT JOIN
#schedule AS S ON
T.Schedule_Time >= S.Start_Time
AND DateAdd(minute,15,T.Schedule_Time) <= S.End_Time
WHERE
S.Start_Time IS NULL
It still suffers the table scan / unordered data issue
January 16, 2009 at 9:04 am
Right, the between got me. This will actually scale better because using a function on a column in a JOIN or WHERE will eliminate the possibility of an Index Seek:
Select
T.schedule_time,
DATEADD(minute, 15, T.schedule_time) as end_Time,
'0015'
From
#Time As T Left Join
#Schedule As S On
T.Schedule_Time >= S.Start_Time and T.Schedule_Time < S.End_Time
Where
S.Start_Time is null
Order BY
T.Schedule_Time
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply