Finding open time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding open time

Author

Topic

h_latha2k
Starting Member

10 Posts

Posted - 2010-04-13 : 12:35:06

I have a course schedule table consisting of fields Course_ID, Weekday, start_time,End_time,Instructor.Instructors are scheduled for various courses. How do I select a time range for a particular day when all the instructors are free to attend staff meeting?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-13 : 13:13:30

Can you post some sample data and your expected output.
If possible, provide the create table statement and insert stmt. It will save some time for us.

Regards,
Bohra.

I am here to learn from Masters and help new bees in learning.

h_latha2k
Starting Member

10 Posts

Posted - 2010-04-13 : 13:35:55

select CourseID,CourseTitle,Starttime,Endtime,Weekday, Instructor from CourseSchedule.This tables represents the weekly schedule for instructors handling courses.

The above query shows the fields in my table CourseSchedule.However, My expected query output should display the following fields where none of the instructors are scheduled for a particular time period which is represented as starttime and Endtime in the output:

Weekday , Starttime,Endtime.

I hope I am clear.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-13 : 14:02:00

Here's something I knocked up which sort of does what you want. Trouble is, you're trying to select timeslots that don't exist. So you need a query that will generate a list of timeslots, and then filter out the timeslots where instructors are scheduled for classes. This code does that. It has some hard coded values, and isn't intended to be used as is. It's just a base, something to get you started. I've included a temp table based on your table, and a script to populate it.

CREATE TABLE #CourseSchedule (
	Course_ID INT, 
	Weekday VARCHAR(5), 
	start_time DATETIME,
	End_time DATETIME,
	Instructor INT
)

INSERT INTO #CourseSchedule 
SELECT 1, 'Mon', '20100412 09:00:00', '20100412 12:00:00', 1
UNION ALL SELECT 1, 'Mon', '20100412 13:00:00', '20100412 16:00:00', 1
UNION ALL SELECT 2, 'Tue', '20100413 09:10:00', '20100413 09:20:00', 2
UNION ALL SELECT 2, 'Tue', '20100413 14:00:00', '20100413 17:00:00', 2
UNION ALL SELECT 2, 'Wed', '20100414 09:00:00', '20100414 12:00:00', 2
UNION ALL SELECT 2, 'Wed', '20100414 14:00:00', '20100414 17:00:00', 1
UNION ALL SELECT 2, 'Thu', '20100415 09:00:00', '20100415 12:00:00', 2
UNION ALL SELECT 2, 'Thu', '20100415 14:00:00', '20100415 17:00:00', 1
UNION ALL SELECT 2, 'Fri', '20100416 09:00:00', '20100416 12:00:00', 2
UNION ALL SELECT 2, 'Fri', '20100416 14:00:00', '20100416 17:00:00', 1



DECLARE @InitStartDate DATETIME SET @InitStartDate = '20100412 09:00:00'

;WITH mt AS (
	SELECT StartTime, DATEADD(mi, 30, StartTime) AS EndTime 
	FROM (	SELECT DATEADD(mi, Number * 30, @InitStartDate) AS StartTime
			FROM master..spt_values
			WHERE Number < 210 
				AND Type = 'P') z
	WHERE CONVERT(VARCHAR, StartTime, 8) BETWEEN '09:00:00' AND '17:30:00' )


SELECT DATENAME(dw, mt.StartTime), mt.StartTime, mt.EndTime
FROM mt
LEFT JOIN #CourseSchedule cs
	ON mt.StartTime < cs.End_time
	AND mt.EndTime > cs.start_time
WHERE cs.End_time IS NULL
	
DROP TABLE #CourseSchedule

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources