contiguous time slot

I am trying to filter out the result . from the post made before .http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28676589.html#a40791909

THe definitions are here
1. Contiguous block - session for the same instructor (car does not matter) within 30 minutes
2.Adjoining sessions - session for a different instructor (in the same car withing 30 minutes of another instructor session)

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

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.

dsackerContract ERP Admin/ConsultantCommented:
Please do the following:

1.

Please show the results of the query above.

2.

Please show what the results actually should be.If you need to give me some more data, that will help too, else I'll use the mockup data from the previous question.
erikTsomikSystem Architect, CF programmer Author Commented:
the data that is shown is

sessionKey      locationKey      instructorKey      sessionStart      sessionEnd      carKey      Flag      FirstKey      cnt      Comment
164144      13      36754      2015-07-01 10:30:00.000      2015-07-01 12:30:00.000      139      NULL      164144      1      
164103      34      12383      2015-07-01 08:15:00.000      2015-07-01 10:15:00.000      139      NULL      164144      0      
164202      34      8      2015-07-01 06:00:00.000      2015-07-01 08:00:00.000      139      NULL      164144      0      0      

the data that should be is: The third record should removed because is an adjoin session to the cancelled session
sessionKey      locationKey      instructorKey      sessionStart      sessionEnd      carKey      Flag      FirstKey      cnt      Comment
164144      13      36754      2015-07-01 10:30:00.000      2015-07-01 12:30:00.000      139      NULL      164144      1      
164103      34      12383      2015-07-01 08:15:00.000      2015-07-01 10:15:00.000      139      NULL      164144      0      
1
dsackerContract ERP Admin/ConsultantCommented:
Please do the following:

In your query, right after you complete your first INSERT statement (after line 36), add this line:

SELECT * FROM @DataFromYourTables
Run your query only up to that line (you can highlight from the top to that line, and it will only run that).

I need to see what's in your @DataFromYourTables.

Don't forget to take out (or comment out) that SELECT statement afterwards, so that it doesn't remain in there permanently.
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:
sessionKey      locationKey      sessionStart      sessionEnd      instructorKey      Flag      carKey      cnt      bookingLock      hubLock      newFlag
164192      13      2015-07-01 06:00:00.000      2015-07-01 08:00:00.000      36754      NULL      133      0      1      0      0
164202      34      2015-07-01 06:00:00.000      2015-07-01 08:00:00.000      8      NULL      139      0      0      1      0
164103      34      2015-07-01 08:15:00.000      2015-07-01 10:15:00.000      12383      NULL      139      0      0      1      0
164144      13      2015-07-01 10:30:00.000      2015-07-01 12:30:00.000      36754      NULL      139      1      1      0      0
164104      NULL      2015-07-01 13:00:00.000      2015-07-01 15:00:00.000      12383      NULL      139      0      0      0      0
164109      NULL      2015-07-01 15:15:00.000      2015-07-01 17:15:00.000      2644      NULL      139      0      0      0      0
164111      NULL      2015-07-01 17:30:00.000      2015-07-01 19:30:00.000      2644      NULL      139      0      0      0      0
164194      NULL      2015-07-01 19:45:00.000      2015-07-01 21:45:00.000      13783      NULL      139      0      0      0      0
dsackerContract ERP Admin/ConsultantCommented:
What's that "newFlag" at the end? Is that the only new field that I don't see in your @DataFromYourTables layout above (lines 5-16)?  I assume it's an "int" field, but  I don't see it in the SELECT query, so curious what it is.
erikTsomikSystem Architect, CF programmer Author Commented:
I was just trying to come up with some ideas you can disregard this
dsackerContract ERP Admin/ConsultantCommented:
What is the definition or the rule for an adjoin? Is there any data that you sent me that will tell me why 164202 is an adjoin?

I know it's a lot of questions, but we must connect the dots to the adjoin that you don't want in the list.
erikTsomikSystem Architect, CF programmer Author Commented:
The data will not tell you that is adjoin.

THe definitions are here
1. Contiguous block - session for the same instructor (car does not matter) within 30 minutes
2.Adjoining sessions - session for a different instructor (in the same car withing 30 minutes of another instructor session from the cancelled session)
dsackerContract ERP Admin/ConsultantCommented:
Would the above definition #2 (an adjoin) session be a different instructor who does NOT have a cancelled session?

If the session is cancelled, is there a value in su.btwSeatsOverride?
erikTsomikSystem Architect, CF programmer Author Commented:
All adjoining sessions will have a different instructor from the session that  being cancelled. Disregard btwSeatsOverride its being being used for different purpose.
erikTsomikSystem Architect, CF programmer Author Commented:
What the cancelled means it just being remove from the client account, that said that this session becomes available for scheduling
dsackerContract ERP Admin/ConsultantCommented:
I'm more confused. Let's see if we can communicate from another angle.

I need to determine from looking at these three rows why 164202 should not be there. The data has to be able to tell me that.

SessionData.jpgWhat makes this confusing is that 164103 has a different instructor, but you want to keep him. And yet, 164202 also has a different instructor, but you want to keep him.

Verify if what you mean by adjoining sessions with different instructor is this:

If the immediately next session is a different instructor, then it must be the same instructor in any further contiguous records.
That would mean, that since instructor 12383 is different from 36754, that session 164202 would have been okay if the instructor there was 12383 (and not 8).
Is that what that means?
erikTsomikSystem Architect, CF programmer Author Commented:
164202 session is within 30 minutes from start and end that why I want to keep him
erikTsomikSystem Architect, CF programmer Author Commented:
For each canceled session there would be 2 adjoining sessions maximum. 1 from the start  and 1 from the end time
dsackerContract ERP Admin/ConsultantCommented:
You said you did NOT want to keep164202 in post #40792159.

In that post you first listed the three records that the query is returning.

Then you listed two records, which did NOT have 164202. You said it should not be there.

But just above (two posts) you now say we should keep 164202.

You're really confusing me now.
erikTsomikSystem Architect, CF programmer Author Commented:
Sorry you are correct I don't want that session
dsackerContract ERP Admin/ConsultantCommented:
Please re-read my post #40792275  carefully, and please verify my assumptions in it.
erikTsomikSystem Architect, CF programmer Author Commented:
The assumption is incorrect. The cancelled session is 164144. Then we need to check for contiguous session (sessions for the same instructor within 30 minutes). In our case there is no contiguous sessions. Then check  for adjoing sessions (sessions for a different instructor with the same car as a cancelled session within 30 minutes). In our case is the session 164103. (for adjoining sessions find a contiguous sessions ). We can only have 2 adjoining session maximum. Fox example, in  our case it will be session164103 and if we would have a session from 12:45 till 2:45,but we don't having so it is only 1 adjoing session in our case.

Session 164202 is not adjoining session because is more than 30 minutes from start to end from the cancelled session
dsackerContract ERP Admin/ConsultantCommented:
I appreciate the explanation above. That helps. This is my rewording of an adjoining session:

So an adjoining session is another session in the same time frame, as long as that session uses the same car and is a cancelled session? For instance, sessions 164192 and 164202 have the same time frame, which means they could be adjoining if they only had the same cars, but because they are different cars, that disqualifies them as adjoining?
If this is now the correct assumption, then:

It seems we really need to know whether that adjoining session is cancelled or not. Previously you said the field btwSeatsOverride tells that, but then later you said it doesn't.
We probably will need to verify that adjoining records are truly eligible after we have loaded the @DataFromYourTables.
Which field in the session, sessionunit or product tables tells if a session is cancelled? I'm not 100% sure it is essential to know this (perhaps you want to decide that online), but it sure seems so, as we need to determine whether an adjoining (or contiguous) session is eligible.
erikTsomikSystem Architect, CF programmer Author Commented:
We do not need to know if the adjoining session have been cancelled. The important key IT MUST BE 30 MINUTES from start and END. Disregard the thing about cancelled sessions. The only cancelled sessions is the session that we currently cancelling
dsackerContract ERP Admin/ConsultantCommented:
Okay, I won't worry about cancelled sessions. One more clarification, and I think we can march on. Wouldn't the important key be:

It must be 30 minutes from start and END (i.e., a later session), OR
It must be 30 minutes from end and START (i.e., a prior session)
??
dsackerContract ERP Admin/ConsultantCommented:
A little side note: I've been doing SQL, .NET development and projects for over 35 years. These are the kinds of questions I always have to pursue with users. It gets a little frustrating at times, but unless you are relentless in getting the analysis correct, the end product will not be correct.

Not meaning to be nit-picky in my questions, but gotta know to do this right.

Also, I will be away from my computer for awhile today (it's Saturday), but will resume later.
erikTsomikSystem Architect, CF programmer Author Commented:
It must 30 minute on both ends.
erikTsomikSystem Architect, CF programmer Author Commented:
OK.Please let me know once your back I really need to sort this out ASAP
dsackerContract ERP Admin/ConsultantCommented:
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

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:
SO far so good
erikTsomikSystem Architect, CF programmer Author Commented:
Still having issue
dsackerContract ERP Admin/ConsultantCommented:
Let's close this question out, and start a new one with the post of my code above. If you have modified my code above, post it instead. Also post good details of your issue.
erikTsomikSystem Architect, CF programmer Author Commented:
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.