contiguous time slot

DECLARE @CancelTime   datetime
SET @CancelTime = '2015-07-01 10:30:00'

 
            DECLARE @DataFromYourTables TABLE (
                sessionKey      int         NOT NULL,
                locationKey     smallint,
                sessionStart    datetime    NOT NULL,
                sessionEnd      datetime    NOT NULL,
                instructorKey   int         NOT NULL,
                Flag            int,
                carKey          smallint    NOT NULL,
                cnt             int,
				bookingLock     int,
				hubLock         int  
            )
            
            INSERT INTO @DataFromYourTables
            
            SELECT  s.sessionKey,
                    s.locationKey,
                    su.sessionStart,
                    su.sessionEnd,
                    su.instructorKey,
                    su.btwSeatsOverride AS Flag,
                    s.carKey,
                    (SELECT count(sm.sessionKey) from sessionMap SM
                        where SM.sessionKey = s.sessionKey
                    )cnt,
					isNUll(S.bookingLock,0) as bookingLock,
					isNUll(S.hubLock,0) as hubLock
            FROM    session s
            JOIN    sessionunit su ON su.sessionKey = s.sessionKey
            JOIN    product     p  ON p.productKey = s.productKey AND p.productTypeKey IN (2,4)
            WHERE   CONVERT(date, su.sessionStart) = CONVERT(date, @CancelTime)
            AND    (su.instructorKey = 36754 OR s.carKey = 139)
       
            
            DECLARE @Results TABLE (
                sessionKey      int,
                locationKey     smallint,
                instructorKey   int,
                sessionStart    datetime,
                sessionEnd      datetime,
                carKey          smallint,
                Flag            int,
                FirstKey        int,
                cnt             int,
				bookingLOck     int,
				hubLock         int 
           )
           
           ;WITH myCTE AS
                (
                SELECT  sessionKey,
                        locationKey,
                        instructorKey,
                        sessionStart,
                        sessionEnd,
                        carKey,
                        Flag,
                        sessionKey AS FirstKey,
                        cnt,
						bookingLock,
						hubLock
                FROM    @DataFromYourTables
                WHERE   sessionStart = @CancelTime
				
                
                UNION ALL
                SELECT  a.sessionKey,
                        a.locationKey,
                        a.instructorKey,
                        a.sessionStart,
                        a.sessionEnd,
                        a.carKey,
                        a.Flag,
                        FirstKey,
                        a.cnt,
						a.bookingLock,
						a.hubLock
                FROM    @DataFromYourTables a
                JOIN    myCTE b
                ON      b.sessionEnd BETWEEN DATEADD(n, -29, a.SessionStart) AND DATEADD(n, 29, a.SessionStart) and b.instructorKey = a.instructorKey 
                )
                INSERT INTO @Results
                SELECT * FROM myCTE

      
                ;WITH myCTE AS
                (
                SELECT  sessionKey,
                        locationKey,
                        instructorKey,
                        sessionStart,
                        sessionEnd,
                        carKey,
                        Flag,
                        sessionKey AS FirstKey,
                        cnt,
						bookingLock,
						hubLock
                FROM     @DataFromYourTables
                WHERE   sessionStart = @CancelTime
                UNION ALL
                SELECT  a.sessionKey,
                        a.locationKey,
                        a.instructorKey,
                        a.sessionStart,
                        a.sessionEnd,
                        a.carKey,
                        a.Flag,
                        FirstKey,
                        a.cnt,
						a.bookingLock,
						a.hubLock
                FROM    @DataFromYourTables a
                JOIN    myCTE b
                ON      b.sessionStart BETWEEN DATEADD(n, -29, a.SessionEnd) AND DATEADD(n, 29, a.SessionEnd) 
				and b.instructorKey <> a.instructorKey 
				and b.carKey = a.carKey
				
                )

                INSERT INTO @Results
                SELECT * FROM myCTE
               WHERE  sessionKey <> FirstKey
           
				
		        
                SELECT  *,
                        CASE
                            WHEN sessionKey <> FirstKey THEN 0
                            ELSE 1
                        END Comment
                FROM    @Results
                ORDER BY sessionStart

Select allOpen in new window