Time slots

I have a scheduling system and I am trying to get for each instructor available time slots. There are a lot of rules. But let start simple first.

For each days the service time is from  6:00am till 9:00pm. The session can be scheduled back to back for the same instructor OR with 15 minutes buffer for a different instructor. For that I run the query  to get all the scheduled sessions for a day . the duration of the session is 2 hours

select distinct su.sessionunitkey, SU.sessionKey, SU.sessionStart, SU.UnassignedIn, su.unit, U.firstname, U.lastname,isNull(L.buffer,0) as bufferTime,
							SU.unassignedIn, SU.instructorKey, S.status, IsNull(SU.btwSeatsOverride, S.seats) AS Seats,
							isnull(sm.type, 'BW') as type,
							U.email,
							U.homephone, L.name, L.address1, L.address2, L.city, L.state, L.zip,
			    			L.phone, L.instructions, C.carKey, C.carNumber,
						(SELECT COUNT(1)
					        FROM sessionMap SM
					        WHERE SM.sessionKey = S.SessionKey
					        	AND (SM.type = 'BW' or type = 'SBW')
					        	AND SM.UserKey != 0
						) AS CNT, A.availStateKey, A.availStateCode,A.btwMinute,A.bwtSpan,

						s.locationkeylist,c.description,S.locationkey,SU.SESSIONend,P.productKey

						
					from SessionUnit SU
						INNER JOIN Session S  ON SU.SessionKey = S.SessionKey
					    left JOIN Location L  ON (S.LocationKey = L.LocationKey or cast(l.locationkey as varchar) in (s.locationkeylist))
						INNER JOIN product P ON S.productKey = P.productKey  <!--- both inclass and btws need to be shown here --->
						LEFT OUTER JOIN SessionMap SM  ON S.sessionkey = SM.Sessionkey AND Sm.sessionkey <> 0
			    		INNER JOIN lkup_availState A  ON S.officeStateKey = A.availStateKey
						LEFT OUTER JOIN Users U  ON SU.InstructorKey = U.UserKey
			    		LEFT JOIN car C  ON S.carKey = C.carKey
					WHERE CONVERT(date, SU.sessionStart, 120) = <cfqueryparam value="#dateformat(arguments.startDt, "yyyy-mm-dd")#" cfsqltype="cf_sql_date">
					AND (SU.InstructorKey = <cfqueryparam value="#arguments.userKey#"cfsqltype="cf_sql_integer">
							OR c.carkey = <cfqueryparam value="#arguments.carKey#"cfsqltype="cf_sql_varchar">
						)		<!--- both Instructor sessions and Car sessions need to be pulled in this query --->
					ORDER BY su.sessionstart

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
You're not likely to get much of a response on this question.  First, you don't actually ask a question.  Second, you drop a fairly complex procedure without any support documentation or data and expect someone to "fix" it.
erikTsomikSystem Architect, CF programmer Author Commented:
The question I am trying to answer is how to build the available time slots based on the the time from 6:00 till 21:00 with a 15 minutes buffer for time (for example 6:00,6:15,6:30 and so on and the rules provided which is query that return already scheduled time slots for the day for instructor and the car )
dsackerContract ERP Admin/ConsultantCommented:
Hi Brian. On Erik's behalf I can say that he is quite amiable and will subdivide his needs out into multiple questions as is equitable, awarding points as he goes. I have helped him answer questions along this line, and he has always been more than fair.

That said, it may take a little time to understand the objective, but you won't find someone more willing to have a respectable approach.

I'm simply swamped with some work at the moment, but just wanted to chime in and let you know you're dealing with a good guy. :)
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

erikTsomikSystem Architect, CF programmer Author Commented:
I have created a clean schedule for a day from 6:00 to 21:00. Now I need help removing time slots.

DECLARE @t TABLE
    (OfficeId INT, StartTime TIME, EndTime TIME)

INSERT INTO @t
SELECT 1, '6:00', '21:00'


;WITH Tally (n) AS (
    SELECT TOP 100 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)
    FROM sys.all_columns)
SELECT OfficeID
    ,TSStart=DATEADD(minute, n, StartTime)
    ,TSEnd=DATEADD(minute, n + 120, StartTime)
    ,Timeslot=CONVERT(VARCHAR(100), DATEADD(minute, n, StartTime), 0) + ' - ' +
        CONVERT(VARCHAR(100), DATEADD(minute, n + 120, StartTime), 0)
FROM @t
CROSS APPLY (
    SELECT n 
    FROM Tally 
    WHERE n BETWEEN 0 AND DATEDIFF(minute, StartTime, DATEADD(minute, -15, EndTime))) a
ORDER BY OfficeID, TSStart

Open in new window

Brian CroweDatabase AdministratorCommented:
BTW that is a nice query for defining possible time slots.

Let's talk more about the existing sessions.  Can you provide some sample output from the query in your original posting?
erikTsomikSystem Architect, CF programmer Author Commented:
The query that being used for scheduled sessions is :

select distinct su.sessionunitkey, SU.sessionKey, SU.sessionStart,SU.SESSIONend,
							 SU.instructorKey, 
							isnull(sm.type, 'BW') as type,
							 C.carKey, C.carNumber,
						(SELECT COUNT(1)
					        FROM sessionMap SM
					        WHERE SM.sessionKey = S.SessionKey
					        	AND (SM.type = 'BW' or type = 'SBW')
					        	AND SM.UserKey != 0
						) AS CNT,

						s.locationkeylist,S.locationkey

						
					from SessionUnit SU
						INNER JOIN Session S  ON SU.SessionKey = S.SessionKey
					    left JOIN Location L  ON (S.LocationKey = L.LocationKey or cast(l.locationkey as varchar) in (s.locationkeylist))
						INNER JOIN product P ON S.productKey = P.productKey  
						LEFT OUTER JOIN SessionMap SM  ON S.sessionkey = SM.Sessionkey AND Sm.sessionkey <> 0
			    		INNER JOIN lkup_availState A  ON S.officeStateKey = A.availStateKey
						LEFT OUTER JOIN Users U  ON SU.InstructorKey = U.UserKey
			    		LEFT JOIN car C  ON S.carKey = C.carKey
					WHERE CONVERT(date, SU.sessionStart, 120) = '7/1/2015'
					AND (SU.InstructorKey = 39097
							OR c.carkey = 133
						)		
					ORDER BY su.sessionstart

Open in new window


The output of this query is the following:
sessionunitkey	sessionKey	sessionStart	SESSIONend	instructorKey	carKey	carNumber	CNT	locationkeylist	locationkey
182004	164192	2015-07-01 06:00:00.000	2015-07-01 08:00:00.000	36754	133	53        	0	13,34	NULL
182008	164196	2015-07-01 09:45:00.000	2015-07-01 11:45:00.000	13783	133	53        	1	13,34	34
182007	164195	2015-07-01 11:45:00.000	2015-07-01 13:45:00.000	13783	133	53        	0	13,34	34
181897	164085	2015-07-01 14:00:00.000	2015-07-01 16:00:00.000	39097	133	53        	0	13,34	13
181908	164096	2015-07-01 16:15:00.000	2015-07-01 18:15:00.000	39097	133	53        	0	13,34	13
181945	164133	2015-07-01 18:15:00.000	2015-07-01 20:15:00.000	39097	133	53        	1	13,34	13

Open in new window

Brian CroweDatabase AdministratorCommented:
So if I understand you correctly you are trying to find "available" time slots by removing those that overlap existing sessions.

Dump the "available" slots into a temp table or table variable...let's call it @Slot

WITH cteSession AS
(
   <Insert your session query here without the ORDER BY>
)
SELECT DISTINCT Slot.*  --Available slots
FROM @Slot AS Slot
LEFT OUTER JOIN cteSession AS Session
   ON (Session.sessionStart < Slot.TSStart AND Session.sessionEnd > Slot.TSStart)   -- Overlaps Start
      OR (Session.sessionStart < Slot.TSEnd AND Session.sessionEnd > Slot.TSEnd)    -- Overlaps End
      OR (Session.sessionStart >= Slot.TSStart AND Session.sessionEnd <= Slot.TSEnd)  -- Completely within slot
WHERE Session.sessionunitkey IS NULL

Are we on the right track here?
erikTsomikSystem Architect, CF programmer Author Commented:
How would I dump
I getting error message
DECLARE @slots TABLE  ( StartTime TIME, EndTime TIME)
WITH cteSession AS
(
   ;WITH Tally (n) AS (
	
    SELECT TOP 100 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)
    FROM sys.all_columns)
SELECT 
    TSStart=DATEADD(minute, n, StartTime)
    ,TSEnd=DATEADD(minute, n + 120, StartTime)
    ,Timeslot=CONVERT(VARCHAR(100), DATEADD(minute, n, StartTime), 0) + ' - ' +
        CONVERT(VARCHAR(100), DATEADD(minute, n + 120, StartTime), 0)
FROM @t
CROSS APPLY (
    SELECT n 
    FROM Tally 
    WHERE n BETWEEN 0 AND DATEDIFF(minute, StartTime, DATEADD(minute, -15, EndTime))) a


)

Open in new window

Brian CroweDatabase AdministratorCommented:
First thing i see is you need a semi-colon between the table variable declaration and the "WITH" of the cte.

CTE's require a semi-colon before the WITH so it can tell that it isn't a query hint.
erikTsomikSystem Architect, CF programmer Author Commented:
Still error message

;WITH cteSession AS
(
;WITH Tally (n) AS (
	
    SELECT TOP 100 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)
    FROM sys.all_columns)
SELECT 
    TSStart=DATEADD(minute, n, StartTime)
    ,TSEnd=DATEADD(minute, n + 120, StartTime)
    ,Timeslot=CONVERT(VARCHAR(100), DATEADD(minute, n, StartTime), 0) + ' - ' +
        CONVERT(VARCHAR(100), DATEADD(minute, n + 120, StartTime), 0)
FROM @t
CROSS APPLY (
    SELECT n 
    FROM Tally 
    WHERE n BETWEEN 0 AND DATEDIFF(minute, StartTime, DATEADD(minute, -15, EndTime))
	
	) a
)

Open in new window

Brian CroweDatabase AdministratorCommented:
What is the error message?
Brian CroweDatabase AdministratorCommented:
I don't believe you can have a nested cte which is why i suggested dumping it into a table variable or temp table first.
Brian CroweDatabase AdministratorCommented:
This is the Session query which I suggested putting in a cte...

select distinct su.sessionunitkey, SU.sessionKey, SU.sessionStart, SU.UnassignedIn, su.unit, U.firstname, U.lastname,isNull(L.buffer,0) as bufferTime,
							SU.unassignedIn, SU.instructorKey, S.status, IsNull(SU.btwSeatsOverride, S.seats) AS Seats,
							isnull(sm.type, 'BW') as type,
							U.email,
							U.homephone, L.name, L.address1, L.address2, L.city, L.state, L.zip,
			    			L.phone, L.instructions, C.carKey, C.carNumber,
						(SELECT COUNT(1)
					        FROM sessionMap SM
					        WHERE SM.sessionKey = S.SessionKey
					        	AND (SM.type = 'BW' or type = 'SBW')
					        	AND SM.UserKey != 0
						) AS CNT, A.availStateKey, A.availStateCode,A.btwMinute,A.bwtSpan,

						s.locationkeylist,c.description,S.locationkey,SU.SESSIONend,P.productKey

						
					from SessionUnit SU
						INNER JOIN Session S  ON SU.SessionKey = S.SessionKey
					    left JOIN Location L  ON (S.LocationKey = L.LocationKey or cast(l.locationkey as varchar) in (s.locationkeylist))
						INNER JOIN product P ON S.productKey = P.productKey  <!--- both inclass and btws need to be shown here --->
						LEFT OUTER JOIN SessionMap SM  ON S.sessionkey = SM.Sessionkey AND Sm.sessionkey <> 0
			    		INNER JOIN lkup_availState A  ON S.officeStateKey = A.availStateKey
						LEFT OUTER JOIN Users U  ON SU.InstructorKey = U.UserKey
			    		LEFT JOIN car C  ON S.carKey = C.carKey
					WHERE CONVERT(date, SU.sessionStart, 120) = <cfqueryparam value="#dateformat(arguments.startDt, "yyyy-mm-dd")#" cfsqltype="cf_sql_date">
					AND (SU.InstructorKey = <cfqueryparam value="#arguments.userKey#"cfsqltype="cf_sql_integer">
							OR c.carkey = <cfqueryparam value="#arguments.carKey#"cfsqltype="cf_sql_varchar">
						)		<!--- both Instructor sessions and Car sessions need to be pulled in this query --->
					

Open in new window

erikTsomikSystem Architect, CF programmer Author Commented:
This query is for session used
Brian CroweDatabase AdministratorCommented:
Yes, @Slot = available slots (the one with the CROSS APPLY) and cteSession = existing sessions.

The logic is ... select all possible slots that don't have an existing session that overlaps with it.  Hence:

SELECT DISTINCT Slot.*
FROM @Slot AS Slot -- This is all possible slots
LEFT OUTER JOIN cteSession AS Session  -- This is the existing sessions that would eliminate an available slot from availability
   ON (Session.sessionStart < Slot.TSStart AND Session.sessionEnd > Slot.TSStart)   -- Overlaps Start
      OR (Session.sessionStart < Slot.TSEnd AND Session.sessionEnd > Slot.TSEnd)    -- Overlaps End
      OR (Session.sessionStart >= Slot.TSStart AND Session.sessionEnd <= Slot.TSEnd)  -- Completely within slot
WHERE Session.sessionunitkey IS NULL -- Any Slot that doesn't have at least one session that overlaps with it is still available
erikTsomikSystem Architect, CF programmer Author Commented:
I am confused in here . I think I am getting the other way around.

DECLARE @t TABLE
    ( StartTime TIME, EndTime TIME)

	
	

INSERT INTO @t
SELECT  '6:00', '21:00'


;WITH Tally (n) AS (
	
    SELECT TOP 100 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)
    FROM sys.all_columns)
SELECT 
    TSStart=DATEADD(minute, n, StartTime)
    ,TSEnd=DATEADD(minute, n + 120, StartTime)
    ,Timeslot=CONVERT(VARCHAR(100), DATEADD(minute, n, StartTime), 0) + ' - ' +
        CONVERT(VARCHAR(100), DATEADD(minute, n + 120, StartTime), 0)
FROM @t
CROSS APPLY (
    SELECT n 
    FROM Tally 
    WHERE n BETWEEN 0 AND DATEDIFF(minute, StartTime, DATEADD(minute, -15, EndTime))
	
	) a
INSERT INTO @t(StartTime,EndTime)
SELECT * FROM @t


DECLARE @slots TABLE  ( sessionunitkey INT, sessionKey INT,sessionStart datetime,SESSIONend DATETIME,instructorKey INT,carKey INT,carNumber INT, locationkeylist VARCHAR(100),locationkey INT)

	INSERT INTO @slots
	select  distinct su.sessionunitkey, SU.sessionKey, SU.sessionStart,SU.SESSIONend,
							 SU.instructorKey, 
							 C.carKey, C.carNumber,
							s.locationkeylist,S.locationkey

						
					from SessionUnit SU
						INNER JOIN Session S  ON SU.SessionKey = S.SessionKey
					    left JOIN Location L  ON (S.LocationKey = L.LocationKey or cast(l.locationkey as varchar) in (s.locationkeylist))
						INNER JOIN product P ON S.productKey = P.productKey  
						LEFT OUTER JOIN SessionMap SM  ON S.sessionkey = SM.Sessionkey AND Sm.sessionkey <> 0
			    		INNER JOIN lkup_availState A  ON S.officeStateKey = A.availStateKey
						LEFT OUTER JOIN Users U  ON SU.InstructorKey = U.UserKey
			    		LEFT JOIN car C  ON S.carKey = C.carKey
					WHERE CONVERT(date, SU.sessionStart, 120) = '7/1/2015'
					AND (SU.InstructorKey = 39097
							OR c.carkey = 133
						)		
					

				


				SELECT * from @slots
				SELECT * from @t

				
				SELECT DISTINCT Slot.*  --Available slots
FROM @Slots AS Slot
LEFT OUTER JOIN @t AS Session
   ON (Slot.sessionStart < Session.StartTime AND Slot.sessionEnd > Session.StartTime)   -- Overlaps Start
      OR (Slot.sessionStart < Session.EndTime AND Slot.sessionEnd > Session.TSEnd)    -- Overlaps End
      OR (Slot.sessionStart >= Session.StartTime AND Slot.sessionEnd <= Session.TSEnd)  -- Completely within slot

Open in new window

erikTsomikSystem Architect, CF programmer Author Commented:
I am running this query and getting an error message .The data types datetime and time are incompatible in the less than operator.

SELECT DISTINCT Slot.*  --Available slots
FROM @slotsAvailable AS Slot
LEFT OUTER JOIN @slotsUsed AS Session
    ON (Session.sessionStart < Slot.TSStart AND Session.sessionEnd > Slot.TSStart)   -- Overlaps Start
      OR (Session.sessionStart < Slot.TSEnd AND Session.sessionEnd > Slot.TSEnd)    -- Overlaps End
      OR (Session.sessionStart >= Slot.TSStart AND Session.sessionEnd <= Slot.TSEnd)  -- Completely within slot
erikTsomikSystem Architect, CF programmer Author Commented:
I have rewrite the query but nothing works here

DECLARE @t TABLE
    ( StartTime TIME, EndTime TIME)



INSERT INTO @t
SELECT  '6:00', '21:00'

DECLARE @slotsAvailable TABLE (TSStart time,TSEnd time,timeSLot varchar(100))


;WITH Tally (n) AS (
    SELECT TOP 100 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)
    FROM sys.all_columns)
	INSERT INTO @slotsAvailable 
SELECT 
    TSStart=DATEADD(minute, n, StartTime)
    ,TSEnd=DATEADD(minute, n + 120, StartTime)
    ,Timeslot=CONVERT(VARCHAR(100), DATEADD(minute, n, StartTime), 0) + ' - ' +
        CONVERT(VARCHAR(100), DATEADD(minute, n + 120, StartTime), 0)
FROM @t
CROSS APPLY (
    SELECT n 
    FROM Tally 
    WHERE n BETWEEN 0 AND DATEDIFF(minute, StartTime, DATEADD(minute, -15, EndTime))
	
	) a



DECLARE @slotsUsed TABLE  ( sessionunitkey INT, sessionKey INT,sessionStart datetime,SESSIONend DATETIME,instructorKey INT,carKey INT,carNumber INT, locationkeylist VARCHAR(100),locationkey INT)

	INSERT INTO @slotsUsed
	select  distinct su.sessionunitkey, SU.sessionKey, convert(char(5), SU.sessionStart, 108) as sessionStart,convert(char(5), SU.sessionEnd, 108) as sessionEnd,
							 SU.instructorKey, 
							 C.carKey, C.carNumber,
							s.locationkeylist,S.locationkey

						
					from SessionUnit SU
						INNER JOIN Session S  ON SU.SessionKey = S.SessionKey
					    left JOIN Location L  ON (S.LocationKey = L.LocationKey or cast(l.locationkey as varchar) in (s.locationkeylist))
						INNER JOIN product P ON S.productKey = P.productKey  
						LEFT OUTER JOIN SessionMap SM  ON S.sessionkey = SM.Sessionkey AND Sm.sessionkey <> 0
			    		INNER JOIN lkup_availState A  ON S.officeStateKey = A.availStateKey
						LEFT OUTER JOIN Users U  ON SU.InstructorKey = U.UserKey
			    		LEFT JOIN car C  ON S.carKey = C.carKey
					WHERE CONVERT(date, SU.sessionStart, 120) = '7/1/2015'
					AND (SU.InstructorKey = 39097
							OR c.carkey = 133
						)		
					

				

				SELECT * from @slotsAvailable
				SELECT * from @slotsUsed

				SELECT DISTINCT Slot.*  --Available slots
FROM @slotsAvailable AS Slot
inner  JOIN @slotsUsed AS Session
   ON (cast(Session.sessionStart as time) < Slot.TSStart AND cast(Session.sessionEnd as time) > Slot.TSStart)   -- Overlaps Start
      OR (cast(Session.sessionStart as time) < Slot.TSEnd AND cast(Session.sessionEnd as time) > Slot.TSEnd)    -- Overlaps End
      OR (cast(Session.sessionStart as time) >= Slot.TSStart AND cast(Session.sessionEnd as time) <= Slot.TSEnd)  -- Completely within slot

Open in new window

Brian CroweDatabase AdministratorCommented:
Try this....we seem to be tripping over nomenclature.

DECLARE @StartTime	TIME = '6:00',
	@EndTime		TIME = '21:00',
	@Interval		INT = 120

DECLARE @Slot TABLE (StartTime TIME, EndTime TIME, TimeSlotString VARCHAR(50));

WITH Tally AS
(
    SELECT TOP 100 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) AS n
    FROM sys.all_columns
)
-- Populate the @Slot table with all available slots between @StartTime and @EndTime (-15 minutes)
INSERT INTO @Slot (StartTime, EndTime, TimeSlotString)
SELECT DATEADD(MINUTE, n, @StartTime),
    DATEADD(MINUTE, n + @Interval, @StartTime),
    CONVERT(VARCHAR(100), DATEADD(MINUTE, n, @StartTime), 0) + ' - ' +
        CONVERT(VARCHAR(100), DATEADD(MINUTE, n + @Interval, @StartTime), 0)
FROM Tally
WHERE n BETWEEN 0 AND DATEDIFF(minute, @StartTime, DATEADD(minute, -15, @EndTime));

WITH cteSession AS --All existing sessions
(
	SELECT DISTINCT su.sessionunitkey, SU.sessionKey, SU.sessionStart,SU.SESSIONend,
		SU.instructorKey, 
		C.carKey, C.carNumber,
		s.locationkeylist,S.locationkey
	from SessionUnit SU
	INNER JOIN [Session] AS S
		ON SU.SessionKey = S.SessionKey
	LEFT OUTER JOIN Location ASL
		ON S.LocationKey = L.LocationKey
		OR CAST(l.locationkey as varchar) IN (s.locationkeylist)
	INNER JOIN product AS P
		ON S.productKey = P.productKey  
	LEFT OUTER JOIN SessionMap AS SM
		ON S.sessionkey = SM.Sessionkey
		AND Sm.sessionkey <> 0
	INNER JOIN lkup_availState AS A
		ON S.officeStateKey = A.availStateKey
	LEFT OUTER JOIN Users AS U
		ON SU.InstructorKey = U.UserKey
	LEFT OUTER JOIN car AS C
		ON S.carKey = C.carKey
	WHERE CONVERT(date, SU.sessionStart, 120) = '7/1/2015'
	AND (SU.InstructorKey = 39097 OR c.carkey = 133)
)
SELECT DISTINCT Slot.*  --Available slots
FROM @Slot AS Slot
LEFT OUTER JOIN cteSession AS S
   ON (S.sessionStart <= Slot.StartTime AND S.sessionEnd >= S.StartTime)   -- Overlaps Start
      OR (S.sessionStart <= Slot.EndTime AND S.sessionEnd >= Slot.EndTime)    -- Overlaps End
      OR (S.sessionStart >= Slot.StartTime AND S.sessionEnd <= Slot.EndTime)  -- Completely within slot

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
erikTsomikSystem Architect, CF programmer Author Commented:
getting error message
Msg 402, Level 16, State 1, Line 50
The data types datetime and time are incompatible in the less than or equal to operator.
Msg 207, Level 16, State 1, Line 50
Invalid column name 'StartTime'.


I think THe problem is here

WITH Tally AS
(
    SELECT TOP 100 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) AS n
    FROM sys.all_columns
)
-- Populate the @Slot table with all available slots between @StartTime and @EndTime (-15 minutes)
INSERT INTO @Slot (StartTime, EndTime, TimeSlotString)
SELECT DATEADD(MINUTE, n, @StartTime),
    DATEADD(MINUTE, n + @Interval, @StartTime),
    CONVERT(VARCHAR(100), DATEADD(MINUTE, n, @StartTime), 0) + ' - ' +
        CONVERT(VARCHAR(100), DATEADD(MINUTE, n + @Interval, @StartTime), 0)
FROM Tally
WHERE n BETWEEN 0 AND DATEDIFF(minute, @StartTime, DATEADD(minute, -15, @EndTime));

THe result here is not in time format

TSStatrt      TSEnd      timeSLot
06:00:00.0000000      08:00:00.0000000      6:00AM - 8:00AM
06:15:00.0000000      08:15:00.0000000      6:15AM - 8:15AM
06:30:00.0000000      08:30:00.0000000      6:30AM - 8:30AM
06:45:00.0000000      08:45:00.0000000      6:45AM - 8:45AM
07:00:00.0000000      09:00:00.0000000      7:00AM - 9:00AM
07:15:00.0000000      09:15:00.0000000      7:15AM - 9:15AM
07:30:00.0000000      09:30:00.0000000      7:30AM - 9:30AM
07:45:00.0000000      09:45:00.0000000      7:45AM - 9:45AM
08:00:00.0000000      10:00:00.0000000      8:00AM - 10:00AM
08:15:00.0000000      10:15:00.0000000      8:15AM - 10:15AM
08:30:00.0000000      10:30:00.0000000      8:30AM - 10:30AM
08:45:00.0000000      10:45:00.0000000      8:45AM - 10:45AM
09:00:00.0000000      11:00:00.0000000      9:00AM - 11:00AM
09:15:00.0000000      11:15:00.0000000      9:15AM - 11:15AM
09:30:00.0000000      11:30:00.0000000      9:30AM - 11:30AM
09:45:00.0000000      11:45:00.0000000      9:45AM - 11:45AM
10:00:00.0000000      12:00:00.0000000      10:00AM - 12:00PM
10:15:00.0000000      12:15:00.0000000      10:15AM - 12:15PM
10:30:00.0000000      12:30:00.0000000      10:30AM - 12:30PM
10:45:00.0000000      12:45:00.0000000      10:45AM - 12:45PM
11:00:00.0000000      13:00:00.0000000      11:00AM - 1:00PM
11:15:00.0000000      13:15:00.0000000      11:15AM - 1:15PM
11:30:00.0000000      13:30:00.0000000      11:30AM - 1:30PM
11:45:00.0000000      13:45:00.0000000      11:45AM - 1:45PM
12:00:00.0000000      14:00:00.0000000      12:00PM - 2:00PM
12:15:00.0000000      14:15:00.0000000      12:15PM - 2:15PM
12:30:00.0000000      14:30:00.0000000      12:30PM - 2:30PM
12:45:00.0000000      14:45:00.0000000      12:45PM - 2:45PM
13:00:00.0000000      15:00:00.0000000      1:00PM - 3:00PM
13:15:00.0000000      15:15:00.0000000      1:15PM - 3:15PM
13:30:00.0000000      15:30:00.0000000      1:30PM - 3:30PM
13:45:00.0000000      15:45:00.0000000      1:45PM - 3:45PM
14:00:00.0000000      16:00:00.0000000      2:00PM - 4:00PM
14:15:00.0000000      16:15:00.0000000      2:15PM - 4:15PM
14:30:00.0000000      16:30:00.0000000      2:30PM - 4:30PM
14:45:00.0000000      16:45:00.0000000      2:45PM - 4:45PM
15:00:00.0000000      17:00:00.0000000      3:00PM - 5:00PM
15:15:00.0000000      17:15:00.0000000      3:15PM - 5:15PM
15:30:00.0000000      17:30:00.0000000      3:30PM - 5:30PM
15:45:00.0000000      17:45:00.0000000      3:45PM - 5:45PM
16:00:00.0000000      18:00:00.0000000      4:00PM - 6:00PM
16:15:00.0000000      18:15:00.0000000      4:15PM - 6:15PM
16:30:00.0000000      18:30:00.0000000      4:30PM - 6:30PM
16:45:00.0000000      18:45:00.0000000      4:45PM - 6:45PM
17:00:00.0000000      19:00:00.0000000      5:00PM - 7:00PM
17:15:00.0000000      19:15:00.0000000      5:15PM - 7:15PM
17:30:00.0000000      19:30:00.0000000      5:30PM - 7:30PM
17:45:00.0000000      19:45:00.0000000      5:45PM - 7:45PM
18:00:00.0000000      20:00:00.0000000      6:00PM - 8:00PM
18:15:00.0000000      20:15:00.0000000      6:15PM - 8:15PM
18:30:00.0000000      20:30:00.0000000      6:30PM - 8:30PM
18:45:00.0000000      20:45:00.0000000      6:45PM - 8:45PM
19:00:00.0000000      21:00:00.0000000      7:00PM - 9:00PM
19:15:00.0000000      21:15:00.0000000      7:15PM - 9:15PM
19:30:00.0000000      21:30:00.0000000      7:30PM - 9:30PM
19:45:00.0000000      21:45:00.0000000      7:45PM - 9:45PM
20:00:00.0000000      22:00:00.0000000      8:00PM - 10:00PM
20:15:00.0000000      22:15:00.0000000      8:15PM - 10:15PM
20:30:00.0000000      22:30:00.0000000      8:30PM - 10:30PM
20:45:00.0000000      22:45:00.0000000      8:45PM - 10:45PM
erikTsomikSystem Architect, CF programmer Author Commented:
I changed the code but is not working
DECLARE @StartTime	TIME = '6:00',
	@EndTime		TIME = '21:00',
	@Interval		INT = 120

DECLARE @Slot TABLE (StartTime TIME, EndTime TIME, TimeSlotString VARCHAR(50));

WITH Tally AS
(
    SELECT TOP 100 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) AS n
    FROM sys.all_columns
)
-- Populate the @Slot table with all available slots between @StartTime and @EndTime (-15 minutes)
INSERT INTO @Slot (StartTime, EndTime, TimeSlotString)
SELECT DATEADD(MINUTE, n, @StartTime),
    DATEADD(MINUTE, n + @Interval, @StartTime),
    CONVERT(VARCHAR(100), DATEADD(MINUTE, n, @StartTime), 0) + ' - ' +
        CONVERT(VARCHAR(100), DATEADD(MINUTE, n + @Interval, @StartTime), 0)
FROM Tally
WHERE n BETWEEN 0 AND DATEDIFF(minute, @StartTime, DATEADD(minute, -15, @EndTime));

WITH cteSession AS --All existing sessions
(
	SELECT DISTINCT su.sessionunitkey, SU.sessionKey, cast(SU.sessionStart as time) as sessionStart,cast(SU.SESSIONend as time) as sessionEnd,
		SU.instructorKey, 
		C.carKey, C.carNumber,
		s.locationkeylist,S.locationkey
	from SessionUnit SU
	INNER JOIN [Session] AS S
		ON SU.SessionKey = S.SessionKey
	LEFT OUTER JOIN Location AS L
		ON S.LocationKey = L.LocationKey
		OR CAST(l.locationkey as varchar) IN (s.locationkeylist)
	INNER JOIN product AS P
		ON S.productKey = P.productKey  
	LEFT OUTER JOIN SessionMap AS SM
		ON S.sessionkey = SM.Sessionkey
		AND Sm.sessionkey <> 0
	INNER JOIN lkup_availState AS A
		ON S.officeStateKey = A.availStateKey
	LEFT OUTER JOIN Users AS U
		ON SU.InstructorKey = U.UserKey
	LEFT OUTER JOIN car AS C
		ON S.carKey = C.carKey
	WHERE CONVERT(date, SU.sessionStart, 120) = '7/1/2015'
	AND (SU.InstructorKey = 39097 OR c.carkey = 133)
)
SELECT DISTINCT Slot.*  --Available slots
FROM @Slot AS Slot
LEFT OUTER JOIN cteSession AS S
   ON (S.sessionStart <= Slot.StartTime AND S.sessionEnd >= Slot.StartTime)   -- Overlaps Start
      OR (S.sessionStart <= Slot.EndTime AND S.sessionEnd >= Slot.EndTime)    -- Overlaps End
      OR (S.sessionStart >= Slot.StartTime AND S.sessionEnd <= Slot.EndTime)  -- Completely within slot

Open in new window

Brian CroweDatabase AdministratorCommented:
I was going to suggest exactly what you did for the SELECT statement in the CTE.  What error are you getting?
erikTsomikSystem Architect, CF programmer Author Commented:
I think I figured Out . We need to exclude the sessions.

SELECT DISTINCT Slot.*   --Available slots
FROM @Slot AS Slot
WHERE NOT EXISTS(
      SELECT *  from cteSession as S
   WHERE (S.sessionStart <= Slot.StartTime AND S.sessionEnd >= Slot.StartTime)   -- Overlaps Start
      OR (S.sessionStart <= Slot.EndTime AND S.sessionEnd >= Slot.EndTime)    -- Overlaps End
      OR (S.sessionStart >= Slot.StartTime AND S.sessionEnd <= Slot.EndTime)  -- Completely within slot

        )
erikTsomikSystem Architect, CF programmer Author Commented:
I change my previous post to simply delete from the temp query

DELETE Slot
FROM @slot as Slot
WHERE  EXISTS(
	SELECT *  from cteSession as S
   WHERE (S.sessionStart < Slot.StartTime AND S.sessionEnd > Slot.StartTime)   -- Overlaps Start
      OR (S.sessionStart < Slot.EndTime AND S.sessionEnd > Slot.EndTime)    -- Overlaps End
      OR (S.sessionStart >= Slot.StartTime AND S.sessionEnd <= Slot.EndTime)  -- Completely within slot

	  )

Open in new window

Brian CroweDatabase AdministratorCommented:
The left join should have taken care of that but a subquery should work fine as well.  Kind of hard to test on this end without session data.
PortletPaulEE Topic AdvisorCommented:
Below a date range Begins and Finishes shown by the ---B--- and the ---F---
events are indicated with a Start (S) and End (E)  
if you compare events Start (S) to range Finish (F) and event End (E) to range Begin (B) the filtering conditions are simpler
    ----B-----------F-----
     S------E                S <= F, E >= B
        S------E             S <= F, E >= B
          S------E           S <= F, E >= B
             S------E        S <= F, E >= B
               S------E      S <= F, E >= B
    S-------------------E    S <= F, E >= B 

Open in new window

You might find this useful here.

+edit:
The problem you are having however is that you are generating a range of TIME values, but not DATETIME

you cannot directly compare 09:15 to 2015-06-01 11:17:23
but you can compare 09:15 to 11:17:23

i.e. you need to compare TIME of each session start and TIME of each session end against those generated TIME  values.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.