We help IT Professionals succeed at work.

MSSQL Datediff for time slot scheduling

GLIanimal
GLIanimal asked
on
Medium Priority
757 Views
Last Modified: 2012-05-07
I am in making a process for class registration. Each class has a corresponding time slot id, of which has a timeslotstart and timeslotend column. Currently they are varchar values since they only are hour & minute time slots. My issue is, when a person signs up for a class in timeslotid 2, starttime being 10 am endtime being 11am, I need to be able to remove other classes from the selection that have a starttime or endtime that is between those dates. I have something that sort of works below, but I am hoping to find something a little better. With the example below, it works for all entries except for ID 13, which has a start time of 9AM and end time of 17PM(5PM).
---------query
SELECT timeslotid, 
datepart(hh, convert(smalldatetime, timeslotstart, 14)) as StartTime, 
convert(smalldatetime, timeslotend, 14) as EndTime,
CASE 
WHEN datepart(hh, convert(smalldatetime, timeslotstart, 14)) 
between 
(SELECT datepart(hh, convert(smalldatetime, timeslotstart, 14)) 
from dbo.TimeSlots WHERE TimeSlotID = 2)
and 
(SELECT datepart(hh, convert(smalldatetime, timeslotend, 14))from dbo.TimeSlots WHERE TimeSlotID = 2)
THEN 'NOT'
ELSE 'SO'
END as StartTime,
CASE 
WHEN convert(smalldatetime, timeslotend, 14) 
between 
(SELECT  convert(smalldatetime, timeslotstart, 14) 
from dbo.TimeSlots WHERE TimeSlotID = 2)
and 
(SELECT convert(smalldatetime, timeslotend, 14)from dbo.TimeSlots WHERE TimeSlotID = 2)
THEN 'NOT'
ELSE 'SO'
END as Endtime
from dbo.timeslots
 
 
----results   bs= between start       be=between end
timeID		start			end		bs	be
1		9		1900-01-01 10:00:00	SO	NOT
2		10		1900-01-01 11:00:00	NOT	NOT
3		11		1900-01-01 12:00:00	NOT	SO
4		14		1900-01-01 15:00:00	SO	SO
5		15		1900-01-01 16:00:00	SO	SO
6		16		1900-01-01 17:00:00	SO	SO
7		9		1900-01-01 10:30:00	SO	NOT
8		9		1900-01-01 11:00:00	SO	NOT
9		10		1900-01-01 12:00:00	NOT	SO
10		10		1900-01-01 11:30:00	NOT	SO
11		14		1900-01-01 15:30:00	SO	SO
12		14		1900-01-01 16:00:00	SO	SO
13		9		1900-01-01 17:00:00	SO	SO
14		10		1900-01-01 12:00:00	NOT	SO

Open in new window

Comment
Watch Question

Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Had to add IN and count for multiple values, but all in all thanks!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.