Contiguous time slot

I have the code from the previous post. THe issue is this it does not go all the way and find all adjoining sessions.

For example, the e session get cancelled at 6:15pm. the contiguous sessions 4:15pm and 2:00pm to 4pm. and it has 1 adjoin session at 11:45 AM

 the fulll record set 
sessionKey	locationKey	sessionStart	sessionEnd	instructorKey	carKey	cnt	bookingLock	hubLock	cancelled	contiguous	adjoining
164192	NULL	2015-07-01 06:00:00.000	2015-07-01 08:00:00.000	36754	133	0	1	0	0	0	0
164196	NULL	2015-07-01 09:45:00.000	2015-07-01 11:45:00.000	13783	133	0	0	1	0	0	0
164195	NULL	2015-07-01 11:45:00.000	2015-07-01 13:45:00.000	13783	133	0	0	1	0	0	0
164085	13	2015-07-01 14:00:00.000	2015-07-01 16:00:00.000	39097	133	0	1	0	0	0	0
164096	13	2015-07-01 16:15:00.000	2015-07-01 18:15:00.000	39097	133	0	1	0	0	0	0
164133	13	2015-07-01 18:15:00.000	2015-07-01 20:15:00.000	39097	133	1	1	0	1	0	0

Open in new window



Processed record SET

sessionKey	locationKey	sessionStart	sessionEnd	instructorKey	carKey	cnt	bookingLock	hubLock	cancelled	contiguous	adjoining
164085	13	2015-07-01 14:00:00.000	2015-07-01 16:00:00.000	39097	133	0	1	0	0	1	0
164096	13	2015-07-01 16:15:00.000	2015-07-01 18:15:00.000	39097	133	0	1	0	0	1	0
164133	13	2015-07-01 18:15:00.000	2015-07-01 20:15:00.000	39097	133	1	1	0	1	0	0

Open in new window


DECLARE @CancelledSession   int,
        @CancelledStart     datetime,
        @CancelledEnd       datetime,
        @CancelledCar       int

/* -------------------------------------------------------------------- */
/*  Set the @CancelledSession to the session that has been cancelled.   */
/*  Try this with different cancelled sessions to test it out.          */
/* -------------------------------------------------------------------- */

SET @CancelledSession = 164144

/* -------------------------------------------------------------------- */
/*  Get the start/end dates and carKey for the cancelled session. They  */
/*  are needed at the bottom.                                           */
/* -------------------------------------------------------------------- */

SELECT @CancelledStart = su.sessionStart,
       @CancelledEnd   = su.sessionEnd,
       @CancelledCar   = s.carKey
FROM    sessionunit su
JOIN    session s ON s.sessionKey = su.sessionKey
WHERE   su.sessionKey = @CancelledSession

/* -------------------------------------------------------------------- */
/*  Create a temporary table to hold the results from the SELECT query  */
/*  below. We are creating three "flags" on the end, to designate the   */
/*  original cancelled session, contiguous and adjoining sessions.      */
/* -------------------------------------------------------------------- */

DECLARE @Results TABLE (
    sessionKey      int         NOT NULL,
    locationKey     smallint,
    sessionStart    datetime    NOT NULL,
    sessionEnd      datetime    NOT NULL,
    instructorKey   int         NOT NULL,
    carKey          smallint    NOT NULL,
    cnt             int,
    bookingLock     int,
    hubLock         int,
    cancelled       tinyint,
    contiguous      tinyint,
    adjoining       tinyint )

INSERT INTO @Results
SELECT  s.sessionKey,
        s.locationKey,
        su.sessionStart,
        su.sessionEnd,
        su.instructorKey,
        s.carKey,
       (SELECT COUNT(1) FROM sessionMap sm
        WHERE  sm.sessionKey = s.sessionKey) AS cnt,
        ISNUll(s.bookingLock, 0) AS bookingLock,
        ISNUll(s.hubLock, 0)     AS hubLock,
        CASE WHEN s.sessionKey = @CancelledSession THEN 1 ELSE 0 END AS cancelled,
        CONVERT(tinyint, 0)      AS contiguous,
        CONVERT(tinyint, 0)      AS adjoining
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, @CancelledStart)
AND    (su.instructorKey = 36754 OR s.carKey = 139)

/* -------------------------------------------------------------------- */
/*  Find any continuous sessions BEFORE the original cancelled session. */
/*  A contiguous session will have the SAME INSTRUCTOR.                 */
/* -------------------------------------------------------------------- */

;WITH myCTE AS
(
SELECT  sessionKey, instructorKey, sessionStart, sessionEnd, carKey
FROM    @Results
WHERE   sessionKey = @CancelledSession
UNION ALL
SELECT  a.sessionKey, a.instructorKey, a.sessionStart, a.sessionEnd, a.carKey
FROM    @Results a
JOIN    myCTE b
ON      b.sessionEnd BETWEEN DATEADD(n, -29, a.SessionStart) AND DATEADD(n, 29, a.SessionStart)
AND     b.instructorKey = a.instructorKey 
)
UPDATE  r
SET     r.contiguous = 1
FROM    @Results r
JOIN    myCTE b
ON      b.sessionKey = r.sessionKey
WHERE   r.cancelled  = 0

/* -------------------------------------------------------------------- */
/*  Find any continuous sessions AFTER the original cancelled session.  */
/*  A contiguous session will have the SAME INSTRUCTOR.                 */
/* -------------------------------------------------------------------- */

;WITH myCTE AS
(
SELECT  sessionKey, instructorKey, sessionStart, sessionEnd, carKey
FROM    @Results
WHERE   sessionKey = @CancelledSession
UNION ALL
SELECT  a.sessionKey, a.instructorKey, a.sessionStart, a.sessionEnd, a.carKey
FROM    @Results a
JOIN    myCTE b
ON      b.sessionStart BETWEEN DATEADD(n, -29, a.SessionEnd) AND DATEADD(n, 29, a.SessionEnd)
AND     b.instructorKey = a.instructorKey 
)
UPDATE  r
SET     r.contiguous = 1
FROM    @Results r
JOIN    myCTE b
ON      b.sessionKey = r.sessionKey
WHERE   r.cancelled  = 0

/* -------------------------------------------------------------------- */
/*  Lastly, find any adjoining sessions. These will be sessions within  */
/*  30 minutes of the original cancelled session, where the car is the  */
/*  same, but the instructor is different.                              */
/* -------------------------------------------------------------------- */

UPDATE  @Results
SET     adjoining = 1
WHERE   carKey = @CancelledCar
AND     contiguous = 0
AND   ( sessionStart BETWEEN DATEADD(n, -29, @CancelledEnd)   AND DATEADD(n, 29, @CancelledEnd)
    OR  sessionEnd   BETWEEN DATEADD(n, -29, @CancelledStart) AND DATEADD(n, 29, @CancelledStart) )

/* -------------------------------------------------------------------- */
/*  Let's see what we got. There will be only ONE cancelled flag set,   */
/*  which is the original cancelled session. There will be up to one    */
/*  adjoining session on either side, and any number of contiguous      */
/*  sessions on either side. Comment out the WHERE clause if you want   */
/*  see ALL the data.                                                   */
/* -------------------------------------------------------------------- */

SELECT  *
FROM    @Results
WHERE   1 IN (cancelled, contiguous, adjoining)

                                          

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
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.

dsackerContract ERP Admin/ConsultantCommented:
Please post the code you used to run the query and get the above results.

You surely didn't run the code above, because (1) on line 11 SET @CancelledSession = 164144 points to a sessionKey not in the raw data you showed, (2) your contiguous and adjoining flags are empty, and (3) there should have been one record in the results where cancelled = 1, showing the original cancelled session.
0
erikTsomikSystem Architect, CF programmer Author Commented:
I think I know where the problem is. We set abjoin session from the cancelledstart and cancelledend. And we need all contentious sessions and find the earliest and latest sessionStart and sessionEnd and use those for filter instead of using
@CancelledEnd and @CancelledStart

UPDATE  @Results
SET     adjoining = 1
WHERE   carKey = @CancelledCar
AND     contiguous = 0
AND   ( sessionStart BETWEEN DATEADD(n, -29, @CancelledEnd)   AND DATEADD(n, 29, @CancelledEnd)
    OR  sessionEnd   BETWEEN DATEADD(n, -29, @CancelledStart) AND DATEADD(n, 29, @CancelledStart) )

Open in new window

0
dsackerContract ERP Admin/ConsultantCommented:
So there can be an adjoining session on any contiguous session, on either side (before or after), as long as it's within 30 minutes of the contiguous session AND as long as it's the same car?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

erikTsomikSystem Architect, CF programmer Author Commented:
Yes you'are correct. Basically just dealing with a block of contiguous ssessions
0
dsackerContract ERP Admin/ConsultantCommented:
I noticed the values for the contiguous and adjoining flags were missing. We need those. I need to see what code of mine you modified, because you lost initializing those to zero.

Look at my code in your first post. Lines 11 should have been set to 164096, I would think, but it isn't there.

Look at lines  56, 57 and 58. I see one record in the results where cancelled = 1, nor any values in the contiguous and adjoining flags. Those flags are important to this working.

That said, here is some changed code below. Replace the UPDATE with this code.
UPDATE  a
SET     a.adjoining = 1
FROM    @Results a
JOIN    @Results c
ON      c.carKey = a.carKey
AND     c.contiguous = 1
AND   ( a.sessionStart BETWEEN DATEADD(n, -29, c.sessionEnd)   AND DATEADD(n, 29, c.sessionEnd)
    OR  a.sessionEnd   BETWEEN DATEADD(n, -29, c.sessionStart) AND DATEADD(n, 29, c.sessionStart) )

Open in new window

If you have any issues, I will need to see YOUR code.
0
erikTsomikSystem Architect, CF programmer Author Commented:
It works fine except it set 1 to all contiguous  sessions.

Can I do something like this instead .

SELECT min(sessionStart),max(sessionEnd)
from @results
WHERE   1 IN (cancelled, contiguous)

Open in new window


and then pass sessionStart and session end to the block . Just do not know how to pass it to the next query

UPDATE  @Results
SET     adjoining = 1
WHERE   carKey = @CancelledCar
AND     contiguous = 0
AND   ( sessionStart BETWEEN DATEADD(n, -29, @CancelledEnd)   AND DATEADD(n, 29, @CancelledEnd)
    OR  sessionEnd   BETWEEN DATEADD(n, -29, @CancelledStart) AND DATEADD(n, 29, @CancelledStart) )

Open in new window

0
dsackerContract ERP Admin/ConsultantCommented:
Try this for the UPDATE (remember, those contiguous flags must have been set already):
UPDATE  a
SET     a.adjoining = 1
FROM    @Results a
JOIN   (SELECT carKey, min(sessionStart) AS minStart, max(sessionEnd) AS maxEnd
        FROM @Results
        WHERE  1 IN (cancelled, contiguous)
        GROUP BY carKey) c
ON      c.carKey = a.carKey
AND   ( a.sessionStart BETWEEN DATEADD(n, -29, maxEnd)   AND DATEADD(n, 29, maxEnd)
    OR  a.sessionEnd   BETWEEN DATEADD(n, -29,minStart) AND DATEADD(n, 29, minStart) )

Open in new window

0
erikTsomikSystem Architect, CF programmer Author Commented:
JUst tested the code and it works fine , however I am missing one record. We find 1 adjoin  session sessiokKey 164195 at 11:45 to 13:45 that adjoin session has 1 contiguous sessions 164196 from 9:45 till 11:45 it must be included as well
0
erikTsomikSystem Architect, CF programmer Author Commented:
I think that Update statement  also need a recursive call to figure out all contiguous sessions for that adjoin session
0
dsackerContract ERP Admin/ConsultantCommented:
It doesn't need a recursive. It already cruises all the contiguous sessions, and adjoining sessions are based on them.

It probably needs both the minStart and maxEnd, AS WELL AS the maxStart and minEnd. However, I'm a little leary of that. Have to be careful, or it will return too many records.

I don't feel 100% about this, but try this code to see if it works better:
UPDATE  a
SET     a.adjoining = 1
FROM    @Results a
JOIN   (SELECT carKey, min(sessionStart) AS minStart, max(sessionEnd) AS maxEnd,
                       max(sessionStart) AS maxEnd,   min(sessionEnd) AS minEnd
        FROM @Results
        WHERE  1 IN (cancelled, contiguous)
        GROUP BY carKey) c
ON      c.carKey = a.carKey
AND ( ( a.sessionStart BETWEEN DATEADD(n, -29, maxEnd)   AND DATEADD(n, 29, maxEnd)
    OR  a.sessionEnd   BETWEEN DATEADD(n, -29, minStart) AND DATEADD(n, 29, minStart) )
  OR  ( a.sessionStart BETWEEN DATEADD(n, -29, minEnd)   AND DATEADD(n, 29, minEnd)
    OR  a.sesisonEnd   BETWEEN DATEADD(n, -29, maxStart) AND DATEADD(n, 29, maxStart) ) )

Open in new window

0
erikTsomikSystem Architect, CF programmer Author Commented:
It does not work. It return this .

sessionKey	locationKey	sessionStart	sessionEnd	instructorKey	carKey	cnt	bookingLock	hubLock	cancelled	contiguous	adjoining
164195	34	2015-07-01 11:45:00.000	2015-07-01 13:45:00.000	13783	133	0	0	1	0	0	1
164085	13	2015-07-01 14:00:00.000	2015-07-01 16:00:00.000	39097	133	0	1	0	0	1	0
164096	13	2015-07-01 16:15:00.000	2015-07-01 18:15:00.000	39097	133	0	1	0	0	1	1
164133	13	2015-07-01 18:15:00.000	2015-07-01 20:15:00.000	39097	133	1	1	0	1	0	0

Open in new window

0
erikTsomikSystem Architect, CF programmer Author Commented:
I thinbk the logic should be find adjoin session once it finds then find all contiguous session for that adjoin session
0
dsackerContract ERP Admin/ConsultantCommented:
Try this:
UPDATE  a
SET     a.adjoining = 1
FROM    @Results a
JOIN   (SELECT carKey, min(sessionStart) AS minStart, max(sessionEnd) AS maxEnd,
                       max(sessionStart) AS maxStart, min(sessionEnd) AS minEnd
        FROM @Results
        WHERE  1 IN (cancelled, contiguous)
        GROUP BY carKey) c
ON      c.carKey = a.carKey
AND ( ( a.sessionStart BETWEEN DATEADD(n, -29, maxEnd)   AND DATEADD(n, 29, maxEnd)
    OR  a.sessionEnd   BETWEEN DATEADD(n, -29, minStart) AND DATEADD(n, 29, minStart) )
  OR  ( a.sessionStart BETWEEN DATEADD(n, -29, minEnd)   AND DATEADD(n, 29, minEnd)
    OR  a.sessionEnd   BETWEEN DATEADD(n, -29, maxStart) AND DATEADD(n, 29, maxStart) ) )

Open in new window

0
dsackerContract ERP Admin/ConsultantCommented:
>>> I think the logic should be find adjoin session once it finds,
>>> then find all contiguous session for that adjoin session

The adjoin sessions hook off the contiguous, not the other way around, so you have to know the contiguous first. At least that's how it has been so far.
0
erikTsomikSystem Architect, CF programmer Author Commented:
Still not right
0
dsackerContract ERP Admin/ConsultantCommented:
Of the raw data in the first post at the top, which ones should be in the final set?
0
erikTsomikSystem Architect, CF programmer Author Commented:
THe final set should be

sessionKey	locationKey	sessionStart	sessionEnd	instructorKey	carKey	cnt	bookingLock	hubLock	cancelled	contiguous	adjoining
164196	NULL	2015-07-01 09:45:00.000	2015-07-01 11:45:00.000	13783	133	0	0	1	0	0	0
164195	34	2015-07-01 11:45:00.000	2015-07-01 13:45:00.000	13783	133	0	0	1	0	0	1
164085	13	2015-07-01 14:00:00.000	2015-07-01 16:00:00.000	39097	133	0	1	0	0	1	0
164096	13	2015-07-01 16:15:00.000	2015-07-01 18:15:00.000	39097	133	0	1	0	0	1	1
164133	13	2015-07-01 18:15:00.000	2015-07-01 20:15:00.000	39097	133	1	1	0	1	0	0

Open in new window

0
erikTsomikSystem Architect, CF programmer Author Commented:
Is there any updates
0
dsackerContract ERP Admin/ConsultantCommented:
Pulled off on work. Will look later.
0
erikTsomikSystem Architect, CF programmer Author Commented:
OK. Thank you
0
erikTsomikSystem Architect, CF programmer Author Commented:
Have you look at the code yet
0
erikTsomikSystem Architect, CF programmer Author Commented:
will this work .

Right after we do adjoin session update can we just do this

;WITH myCTE AS
(
SELECT  sessionKey, instructorKey, sessionStart, sessionEnd, carKey
FROM    @Results
WHERE   adjoining = 1
UNION ALL
SELECT  a.sessionKey, a.instructorKey, a.sessionStart, a.sessionEnd, a.carKey
FROM    @Results a
JOIN    myCTE b
ON      ((b.sessionStart BETWEEN DATEADD(n, -29, a.SessionEnd) AND DATEADD(n, 29, a.SessionEnd)) )
AND     b.instructorKey = a.instructorKey 
)
UPDATE  r
SET     r.adjoining = 1,r.contiguous=1
FROM    @Results r
JOIN    myCTE b
ON      b.sessionKey = r.sessionKey

Open in new window

0
dsackerContract ERP Admin/ConsultantCommented:
Give it a shot. You can comment out the UPDATE  statement to test it.
0
erikTsomikSystem Architect, CF programmer Author Commented:
It works. would the code works if I need to check on both sides for adjoin sessions
0
dsackerContract ERP Admin/ConsultantCommented:
You'll need it in the second recursive query, too. You can't check both sides at the same time, which is why there are two recursive queries.
0
erikTsomikSystem Architect, CF programmer Author Commented:
I tested the code and it seems to work. The only thing I need your attention is to set hubLock flag for each side.

below is the code . When I ran the second query it overwrites the Hublock that was set to 1 and now it is all 2

UPDATE  a
SET     a.adjoining = 1,
		a.hubLock = 1	
FROM    @Results a
JOIN   (SELECT carKey, min(sessionStart) AS minStart, max(sessionEnd) AS maxEnd
        FROM @Results
        WHERE  1 IN (cancelled, contiguous)
        GROUP BY carKey) c
ON      c.carKey = a.carKey
AND   ( a.sessionStart BETWEEN DATEADD(n, -29, maxEnd)   AND DATEADD(n, 29, maxEnd)
    OR  a.sessionEnd   BETWEEN DATEADD(n, -29,minStart) AND DATEADD(n, 29, minStart) )
 ;WITH myCTE AS
                (
                SELECT  sessionKey, instructorKey, sessionStart, sessionEnd, carKey
                FROM    @Results
                WHERE   adjoining = 1
                UNION ALL
                SELECT  a.sessionKey, a.instructorKey, a.sessionStart, a.sessionEnd, a.carKey
                FROM    @Results a
                JOIN    myCTE b
                ON      ((b.sessionStart BETWEEN DATEADD(n, -29, a.SessionEnd) AND DATEADD(n, 29, a.SessionEnd)) )
                AND     b.instructorKey = a.instructorKey 
                )
                UPDATE  r
                SET     r.adjoining = 1,
                        r.contiguous=1,
                        r.hubLock=1
                FROM    @Results r
                JOIN    myCTE b
                ON      b.sessionKey = r.sessionKey
                
                
                ;WITH myCTE AS
                (
                SELECT  sessionKey, instructorKey, sessionStart, sessionEnd, carKey
                FROM    @Results
                WHERE   adjoining = 1
                UNION ALL
                SELECT  a.sessionKey, a.instructorKey, a.sessionStart, a.sessionEnd, a.carKey
                FROM    @Results a
                JOIN    myCTE b
                ON      b.sessionEnd BETWEEN DATEADD(n, -29, a.SessionStart) AND DATEADD(n, 29, a.SessionStart)
                AND     b.instructorKey = a.instructorKey 
                )
                UPDATE  r
                SET     r.adjoining = 1,
                        r.contiguous=1,
                        r.hubLock=2
                FROM    @Results r
                JOIN    myCTE b
                ON      b.sessionKey = r.sessionKey

Open in new window

0
dsackerContract ERP Admin/ConsultantCommented:
This week went crazy. I'll be able to help in about 2 hours.
0
erikTsomikSystem Architect, CF programmer Author Commented:
OK.
0
dsackerContract ERP Admin/ConsultantCommented:
Is your hublock field initialized to zero (0)?

Are you wanting to set hublock = 2 in that second WITH query for only hublocks that have a value of zero?

If that be the case, try this:
UPDATE  a
SET     a.adjoining = 1,
		a.hubLock = 1	
FROM    @Results a
JOIN   (SELECT carKey, min(sessionStart) AS minStart, max(sessionEnd) AS maxEnd
        FROM @Results
        WHERE  1 IN (cancelled, contiguous)
        GROUP BY carKey) c
ON      c.carKey = a.carKey
AND   ( a.sessionStart BETWEEN DATEADD(n, -29, maxEnd)   AND DATEADD(n, 29, maxEnd)
    OR  a.sessionEnd   BETWEEN DATEADD(n, -29,minStart) AND DATEADD(n, 29, minStart) )

 ;WITH myCTE AS
                (
                SELECT  sessionKey, instructorKey, sessionStart, sessionEnd, carKey
                FROM    @Results
                WHERE   adjoining = 1
                UNION ALL
                SELECT  a.sessionKey, a.instructorKey, a.sessionStart, a.sessionEnd, a.carKey
                FROM    @Results a
                JOIN    myCTE b
                ON      ((b.sessionStart BETWEEN DATEADD(n, -29, a.SessionEnd) AND DATEADD(n, 29, a.SessionEnd)) )
                AND     b.instructorKey = a.instructorKey 
                )
                UPDATE  r
                SET     r.adjoining = 1,
                        r.contiguous=1,
                        r.hubLock=1
                FROM    @Results r
                JOIN    myCTE b
                ON      b.sessionKey = r.sessionKey
                
                
                ;WITH myCTE AS
                (
                SELECT  sessionKey, instructorKey, sessionStart, sessionEnd, carKey
                FROM    @Results
                WHERE   adjoining = 1
                UNION ALL
                SELECT  a.sessionKey, a.instructorKey, a.sessionStart, a.sessionEnd, a.carKey
                FROM    @Results a
                JOIN    myCTE b
                ON      b.sessionEnd BETWEEN DATEADD(n, -29, a.SessionStart) AND DATEADD(n, 29, a.SessionStart)
                AND     b.instructorKey = a.instructorKey 
                )
                UPDATE  r
                SET     r.adjoining = 1,
                        r.contiguous=1,
                        r.hubLock = CASE WHEN r.hubLock = 0 THEN 2 ELSE r.hubLock END
                FROM    @Results r
                JOIN    myCTE b
                ON      b.sessionKey = r.sessionKey

Open in new window

0
erikTsomikSystem Architect, CF programmer Author Commented:
Not really.

Remember I can have max of 2 adjoining sessions. 1 from the top and 1 from the button. I just wan to identify them. The one bofore as 1 and one after after . That why I have this SELECT carKey, min(sessionStart) AS minStart, max(sessionEnd
0
dsackerContract ERP Admin/ConsultantCommented:
Since your first update sets the hubLock to 1, perhaps the next WITH should set it to 2, then the final WITH to 3. Try this, and tell me if it overwrites any of them. If so, we may have to put a CASE statement in the WITH updates:
UPDATE  a
SET     a.adjoining = 1,
		a.hubLock = 1	
FROM    @Results a
JOIN   (SELECT carKey, min(sessionStart) AS minStart, max(sessionEnd) AS maxEnd
        FROM @Results
        WHERE  1 IN (cancelled, contiguous)
        GROUP BY carKey) c
ON      c.carKey = a.carKey
AND   ( a.sessionStart BETWEEN DATEADD(n, -29, maxEnd)   AND DATEADD(n, 29, maxEnd)
    OR  a.sessionEnd   BETWEEN DATEADD(n, -29,minStart) AND DATEADD(n, 29, minStart) )

 ;WITH myCTE AS
                (
                SELECT  sessionKey, instructorKey, sessionStart, sessionEnd, carKey
                FROM    @Results
                WHERE   adjoining = 1
                UNION ALL
                SELECT  a.sessionKey, a.instructorKey, a.sessionStart, a.sessionEnd, a.carKey
                FROM    @Results a
                JOIN    myCTE b
                ON      ((b.sessionStart BETWEEN DATEADD(n, -29, a.SessionEnd) AND DATEADD(n, 29, a.SessionEnd)) )
                AND     b.instructorKey = a.instructorKey 
                )
                UPDATE  r
                SET     r.adjoining = 1,
                        r.contiguous=1,
                        r.hubLock=2
                FROM    @Results r
                JOIN    myCTE b
                ON      b.sessionKey = r.sessionKey
                
                
                ;WITH myCTE AS
                (
                SELECT  sessionKey, instructorKey, sessionStart, sessionEnd, carKey
                FROM    @Results
                WHERE   adjoining = 1
                UNION ALL
                SELECT  a.sessionKey, a.instructorKey, a.sessionStart, a.sessionEnd, a.carKey
                FROM    @Results a
                JOIN    myCTE b
                ON      b.sessionEnd BETWEEN DATEADD(n, -29, a.SessionStart) AND DATEADD(n, 29, a.SessionStart)
                AND     b.instructorKey = a.instructorKey 
                )
                UPDATE  r
                SET     r.adjoining = 1,
                        r.contiguous=1,
                        r.hubLock=3
                FROM    @Results r
                JOIN    myCTE b
                ON      b.sessionKey = r.sessionKey

Open in new window

If you're functioning without the hubLock value, this may actually be good to go.
0

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
dsackerContract ERP Admin/ConsultantCommented:
Been a pleasure working with you. All the best, and good luck on your project.
0
erikTsomikSystem Architect, CF programmer Author Commented:
I have questions for you. I've  been great. I have learned a lot of new sql Technics.
0
erikTsomikSystem Architect, CF programmer Author Commented:
can you help me with one problem. I will open a new question
0
dsackerContract ERP Admin/ConsultantCommented:
Sure. Thanks.
0
erikTsomikSystem Architect, CF programmer Author Commented:
0
dsackerContract ERP Admin/ConsultantCommented:
It will be just a while before I begin on it. Thanks.
0
erikTsomikSystem Architect, CF programmer Author Commented:
Hi. I need you help I found you is the best helper. Thank you

http://www.experts-exchange.com/questions/28702608/list-of-contiguous-sessions.html
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.