list of contiguous sessions

I have the code that is working fine . I Need to add a field to the final query called so SoloLock.

What that lock will do will tell me if there is a single contiguous  session . If it is then set the flag to 1 other wise 0.

And the same thing for adjoin session

<cfquery name="qGetResults" datasource="#request.datasource#">
        	DECLARE @CancelledSession   int,
                    @CancelledStart     datetime,
                    @CancelledEnd       datetime,
                    @CancelledCar       int,
                    @cancelledInstructor int
                    
                    SELECT @CancelledSession = #arguments.sessionkey#
            
           /* -------------------------------------------------------------------- */
            /*  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,
                   @cancelledInstructor = SU.instructorKey
            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     tinyint,
                hubLock         tinyint,
                cancelled       tinyint,
                contiguous      tinyint,
                adjoining       tinyint,
                isHub           int,
                soloLock      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,
                        CONVERT(tinyint, 0)     AS bookingLock,
                        CONVERT(tinyint, 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,
                        case WHEN s.locationKey IS not NULL
                        THEN (
                                 case 
                                    when (select top 1 loccatkey from Location WHere locationKey = S.locationKey) = 4
                                    THEN 1
                                    ELSE 0
                                 END
                             )
                     ELSE 0
                    END as isHub
                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 = @cancelledInstructor OR s.carKey = @CancelledCar)
                
                
                
                /* -------------------------------------------------------------------- */
                /*  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.                              */
                /* -------------------------------------------------------------------- */
              DECLARE @id tinyInt 
                SET @id = 0   
              UPDATE  a
                SET     a.adjoining = 1,
                        @id = a.hubLock = @id + 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) )
                
                /* -------------------------------------------------------------------- */
                /*  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.                                                   */
                /* -------------------------------------------------------------------- */
                
                ;WITH myCTE AS
                (
                SELECT  sessionKey, instructorKey, sessionStart, sessionEnd, carKey,hubLock
                FROM    @Results
                WHERE   adjoining = 1
                UNION ALL
                SELECT  a.sessionKey, a.instructorKey, a.sessionStart, a.sessionEnd, a.carKey,b.hubLock
                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=b.hubLock
                FROM    @Results r
                JOIN    myCTE b
                ON      b.sessionKey = r.sessionKey
                
                
                ;WITH myCTE AS
                (
                SELECT  sessionKey, instructorKey, sessionStart, sessionEnd, carKey,hubLock
                FROM    @Results
                WHERE   adjoining = 1
                UNION ALL
                SELECT  a.sessionKey, a.instructorKey, a.sessionStart, a.sessionEnd, a.carKey,b.hubLock
                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=b.hubLock
                FROM    @Results r
                JOIN    myCTE b
                ON      b.sessionKey = r.sessionKey
                
                SELECT  *
                FROM    @Results
                WHERE   1 IN (cancelled, contiguous, adjoining)

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.

PortletPaulEE Topic AdvisorCommented:
can you supply sample data?

btw: There is a case expression you are using that looks more complex than it needs to be
-- instead of this:
      CASE
            WHEN s.locationKey IS NOT NULL THEN (
                  CASE
                        WHEN (
                              SELECT TOP 1
                                    loccatkey
                              FROM Location
                              WHERE locationKey = S.locationKey
                        )
                        = 4 THEN 1
                        ELSE 0
                  END
                  )
            ELSE 0
      END AS isHub


-- try this
      CASE
            WHEN S.locationKey = 4 THEN 1
            ELSE 0
      END AS isHub

Open in new window

erikTsomikSystem Architect, CF programmer Author Commented:
here is some sample data:

sessionKey	locationKey	sessionStart	sessionEnd	instructorKey	carKey	cnt	bookingLock	hubLock	cancelled	contiguous	adjoining	isHub
166412	13	2015-08-06 09:45:00.000	2015-08-06 11:45:00.000	2644	32	1	0	0	1	0	0	0
166414	13	2015-08-06 12:00:00.000	2015-08-06 14:00:00.000	2644	32	0	0	0	0	1	0	0
166405	34	2015-08-06 14:15:00.000	2015-08-06 16:15:00.000	19128	32	0	0	1	0	1	1	1
166413	34	2015-08-06 16:30:00.000	2015-08-06 18:30:00.000	19128	32	0	0	1	0	1	1	1

Open in new window

PortletPaulEE Topic AdvisorCommented:
does that sample provide an example of a "single contiguous session"?

from that sample, what would the expected result be?
erikTsomikSystem Architect, CF programmer Author Commented:
the data is the  compiled records set. That include both contiguous and adjoining sessions. All i need to say if the instructor only have a single session raise the flag  soloLock   and set it to 1,otherwise ,0
PortletPaulEE Topic AdvisorCommented:
A way to do this might be to use COUNT(*) OVER(partition by instructorKey) which counts the total number of sessions for each instructor in the CTE

Then use the CTE in a subquery like the following so you can reference that counted valued in a case expression

e.g.
select
    *
    , case when IKcount > 1 then 0 else 1 end as sololock
from (
      select *, count(*) over(partition by instructorKey) as IKcount
      from  myCTE
      ) as derived

Open in new window

I have to say that there a great deal of guessing in this suggestion. Hopefully it will help.

This quite brief & single page website http://sscce.org/ supplies guidance on why an example and expected result is helpful to all parties.

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
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.