contiguous time slot

The question is continue from this post.

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28675840.html#a40785634

I am just giving a different data set that may happened. and no where in the code we take a cancelled session in consideration. I something get cancelled for example from 6 to 8 I need to find all contiguous sessions which will be 8 to 10, once 8 to 10 is found the next  contiguous session will be 10 to 12 and no more.

INSERT INTO @Results
SELECT 164192, NULL, '2015-07-01 06:00:00', '2015-07-01 08:00:00', 36754, NULL, 133, 2 UNION ALL
SELECT 164196, 34,   '2015-07-01 08:00:00', '2015-07-01 10:00:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164195, 34,   '2015-07-01 14:00:00', '2015-07-01 16:00:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164085, 13,   '2015-07-01 18:00:00', '2015-07-01 20:00:00', 39097, NULL, 133, 2
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:
That's the beauty and frustration of piloting. But I think we'll get there.

I slightly modified your data, adding only one or two more records, but leaving a gap after the 8-10am session. Also, I'm selecting ONLY the 6-8 session, as we'll pretend that one has just been cancelled on you.

The query below will show the first contiguous session after that. I'm playing with a recursive, which I think will have to come into play. So take this as a first draft of a shift in direction. :)

DECLARE @Results 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,
    productTypeKey  smallint    NOT NULL    )

INSERT INTO @Results
SELECT 164192, NULL, '2015-07-01 06:00:00', '2015-07-01 08:00:00', 36754, NULL, 133, 2 UNION ALL
SELECT 164193, 34,   '2015-07-01 08:00:00', '2015-07-01 10:00:00', 36754, NULL, 133, 2 UNION ALL
--SELECT 164196, 34,   '2015-07-01 09:45:00', '2015-07-01 11:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164195, 34,   '2015-07-01 11:45:00', '2015-07-01 13:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164085, 13,   '2015-07-01 14:00:00', '2015-07-01 16:00:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164096, 13,   '2015-07-01 16:15:00', '2015-07-01 18:15:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164133, 13,   '2015-07-01 18:15:00', '2015-07-01 20:15:00', 39097, NULL, 133, 2

SELECT  a.sessionKey,
        a.locationKey,
        a.instructorKey,
        a.sessionStart,
        a.sessionEnd,
        b.sessionStart  AS NextStart,
        b.sessionEnd    AS NextEnd,
        b.instructorKey AS NextInstr
FROM    @Results a
JOIN    @Results b
ON     (b.instructorKey = a.instructorKey OR b.carKey = a.carKey)
AND     b.sessionStart BETWEEN DATEADD(n, -29, a.sessionEnd) AND DATEADD(n, 29, a.sessionEnd)
WHERE   a.sessionStart = '2015-07-01 06:00:00'

Open in new window

erikTsomikSystem Architect, CF programmer Author Commented:
I ran this code and it works for session at 6:00. Then I change to cancel session 11:45 and that only returns 1 records instead of 3 .I guess recursion will be the key
dsackerContract ERP Admin/ConsultantCommented:
Yeah. I'm working on the recursion now.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

dsackerContract ERP Admin/ConsultantCommented:
Check this bad boy out. :)

DECLARE @Results 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,
    productTypeKey  smallint    NOT NULL    )

INSERT INTO @Results
SELECT 164192, NULL, '2015-07-01 06:00:00', '2015-07-01 08:00:00', 36754, NULL, 133, 2 UNION ALL
SELECT 164193, 34,   '2015-07-01 08:00:00', '2015-07-01 10:00:00', 36754, NULL, 133, 2 UNION ALL
--SELECT 164196, 34,   '2015-07-01 09:45:00', '2015-07-01 11:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164195, 34,   '2015-07-01 11:45:00', '2015-07-01 13:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164085, 13,   '2015-07-01 14:00:00', '2015-07-01 16:00:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164096, 13,   '2015-07-01 16:15:00', '2015-07-01 18:15:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164133, 13,   '2015-07-01 18:15:00', '2015-07-01 20:15:00', 39097, NULL, 133, 2

;WITH myCTE AS
(
SELECT  sessionKey,
        locationKey,
        instructorKey,
        sessionStart,
        sessionEnd,
        instructorKey,
        carKey,
        sessionKey AS FirstKey
FROM    @Results
WHERE   sessionStart = '2015-07-01 06:00:00'
UNION ALL
SELECT  a.sessionKey,
        a.locationKey,
        a.instructorKey,
        a.sessionStart,
        a.sessionEnd,
        a.instructorKey,
        a.carKey,
        FirstKey
FROM    @Results a
JOIN    myCTE b
ON      b.sessionEnd BETWEEN DATEADD(n, -29, a.SessionStart) AND DATEADD(n, 29, a.SessionStart)
)
SELECT * FROM myCTE

Open in new window

Add more data if you wish, play with it some. I think the best way to go about it may be to preload this @Results table with the day you want to scan, then use the particular time of cancellation inside the WITH recursive as your starting point.

Both can be made into DECLARE @Variables.
erikTsomikSystem Architect, CF programmer Author Commented:
I think you got it great . I will test it out. Thank you. I Need to learn more about SQL
erikTsomikSystem Architect, CF programmer Author Commented:
Found a bug. Replied too soon

If I change the time to be 2015-07-01 16:15:00. I only get 2 records back.

DECLARE @Results 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,
    productTypeKey  smallint    NOT NULL    )

INSERT INTO @Results
SELECT 164192, NULL, '2015-07-01 06:00:00', '2015-07-01 08:00:00', 36754, NULL, 133, 2 UNION ALL
SELECT 164193, 34,   '2015-07-01 08:00:00', '2015-07-01 10:00:00', 36754, NULL, 133, 2 UNION ALL
--SELECT 164196, 34,   '2015-07-01 09:45:00', '2015-07-01 11:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164195, 34,   '2015-07-01 11:45:00', '2015-07-01 13:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164085, 13,   '2015-07-01 14:00:00', '2015-07-01 16:00:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164096, 13,   '2015-07-01 16:15:00', '2015-07-01 18:15:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164133, 13,   '2015-07-01 18:15:00', '2015-07-01 20:15:00', 39097, NULL, 133, 2

;WITH myCTE AS
(
SELECT  sessionKey,
        locationKey,
        instructorKey,
        sessionStart,
        sessionEnd,
        instructorKey,
        carKey,
        sessionKey AS FirstKey
FROM    @Results
WHERE   sessionStart = '2015-07-01 16:15:00'
UNION ALL
SELECT  a.sessionKey,
        a.locationKey,
        a.instructorKey,
        a.sessionStart,
        a.sessionEnd,
        a.instructorKey,
        a.carKey,
        FirstKey
FROM    @Results a
JOIN    myCTE b
ON      b.sessionEnd BETWEEN DATEADD(n, -29, a.SessionStart) AND DATEADD(n, 29, a.SessionStart)
)
SELECT * FROM myCTE

Open in new window

erikTsomikSystem Architect, CF programmer Author Commented:
I entered 16:15 and got 16:15 and 18:15. I should have also got 2 more records. from 14:00 and 11:45, because they are contiguous
dsackerContract ERP Admin/ConsultantCommented:
I'm figuring out how to combine two separate WITH statements. Check this out. It shows the proof of concept:

DECLARE @Results 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,
    productTypeKey  smallint    NOT NULL    )

INSERT INTO @Results
SELECT 164192, NULL, '2015-07-01 06:00:00', '2015-07-01 08:00:00', 36754, NULL, 133, 2 UNION ALL
SELECT 164193, 34,   '2015-07-01 08:00:00', '2015-07-01 10:00:00', 36754, NULL, 133, 2 UNION ALL
--SELECT 164196, 34,   '2015-07-01 09:45:00', '2015-07-01 11:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164195, 34,   '2015-07-01 11:45:00', '2015-07-01 13:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164085, 13,   '2015-07-01 14:00:00', '2015-07-01 16:00:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164096, 13,   '2015-07-01 16:15:00', '2015-07-01 18:15:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164133, 13,   '2015-07-01 18:15:00', '2015-07-01 20:15:00', 39097, NULL, 133, 2

;WITH myCTE AS
(
SELECT  sessionKey,
        locationKey,
        instructorKey,
        sessionStart,
        sessionEnd,
        instructorKey,
        carKey,
        sessionKey AS FirstKey
FROM    @Results
WHERE   sessionStart = '2015-07-01 16:15:00'
UNION ALL
SELECT  a.sessionKey,
        a.locationKey,
        a.instructorKey,
        a.sessionStart,
        a.sessionEnd,
        a.instructorKey,
        a.carKey,
        FirstKey
FROM    @Results a
JOIN    myCTE b
ON      b.sessionEnd BETWEEN DATEADD(n, -29, a.SessionStart) AND DATEADD(n, 29, a.SessionStart)
)
SELECT * FROM myCTE
ORDER BY sessionStart

;WITH myCTE AS
(
SELECT  sessionKey,
        locationKey,
        instructorKey,
        sessionStart,
        sessionEnd,
        instructorKey,
        carKey,
        sessionKey AS FirstKey
FROM    @Results
WHERE   sessionStart = '2015-07-01 16:15:00'
UNION ALL
SELECT  a.sessionKey,
        a.locationKey,
        a.instructorKey,
        a.sessionStart,
        a.sessionEnd,
        a.instructorKey,
        a.carKey,
        FirstKey
FROM    @Results a
JOIN    myCTE b
ON      b.sessionStart BETWEEN DATEADD(n, -29, a.SessionEnd) AND DATEADD(n, 29, a.SessionEnd)
)
SELECT * FROM myCTE
ORDER BY sessionStart

Open in new window

erikTsomikSystem Architect, CF programmer Author Commented:
I have change the on to this ON      b.sessionStart BETWEEN DATEADD(n, -29, a.sessionEnd) AND DATEADD(n, 29, a.sessionEnd)

ANd now I am only short by 1 record.

DECLARE @Results 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,
    productTypeKey  smallint    NOT NULL    )

INSERT INTO @Results
SELECT 164192, NULL, '2015-07-01 06:00:00', '2015-07-01 08:00:00', 36754, NULL, 133, 2 UNION ALL
SELECT 164193, 34,   '2015-07-01 08:00:00', '2015-07-01 10:00:00', 36754, NULL, 133, 2 UNION ALL
--SELECT 164196, 34,   '2015-07-01 09:45:00', '2015-07-01 11:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164195, 34,   '2015-07-01 11:45:00', '2015-07-01 13:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164085, 13,   '2015-07-01 14:00:00', '2015-07-01 16:00:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164096, 13,   '2015-07-01 16:15:00', '2015-07-01 18:15:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164133, 13,   '2015-07-01 18:15:00', '2015-07-01 20:15:00', 39097, NULL, 133, 2

;WITH myCTE AS
(
SELECT  sessionKey,
        locationKey,
        instructorKey,
        sessionStart,
        sessionEnd,
        instructorKey,
        carKey,
        sessionKey AS FirstKey
FROM    @Results
WHERE   sessionStart = '2015-07-01 16:15:00'
UNION ALL
SELECT  a.sessionKey,
        a.locationKey,
        a.instructorKey,
        a.sessionStart,
        a.sessionEnd,
        a.instructorKey,
        a.carKey,
        FirstKey
FROM    @Results a
JOIN    myCTE b
ON      b.sessionStart BETWEEN DATEADD(n, -29, a.sessionEnd) AND DATEADD(n, 29, a.sessionEnd)
)
SELECT * FROM myCTE

Open in new window

dsackerContract ERP Admin/ConsultantCommented:
Check my post above yours.
dsackerContract ERP Admin/ConsultantCommented:
I think this will do the trick. I've changed the temporary table to @DataFromYourTables, because you will really need to load it from a simple session, sessionunit and product JOIN query, selecting all records where its sessionStart = CONVERT(date, @CancelTime). From there, the code below should work:

DECLARE @CancelTime   datetime
SET @CancelTime = '2015-07-01 16:15: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,
    productTypeKey  smallint    NOT NULL    )

INSERT INTO @DataFromYourTables
SELECT 164192, NULL, '2015-07-01 06:00:00', '2015-07-01 08:00:00', 36754, NULL, 133, 2 UNION ALL
SELECT 164193, 34,   '2015-07-01 08:00:00', '2015-07-01 10:00:00', 36754, NULL, 133, 2 UNION ALL
--SELECT 164196, 34,   '2015-07-01 09:45:00', '2015-07-01 11:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164195, 34,   '2015-07-01 11:45:00', '2015-07-01 13:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164085, 13,   '2015-07-01 14:00:00', '2015-07-01 16:00:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164096, 13,   '2015-07-01 16:15:00', '2015-07-01 18:15:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164133, 13,   '2015-07-01 18:15:00', '2015-07-01 20:15:00', 39097, NULL, 133, 2

DECLARE @Results TABLE (
    sessionKey      int,
    locationKey     smallint,
    instructorKey   int,
    sessionStart    datetime,
    sessionEnd      datetime,
    carKey          smallint,
    productTypeKey  smallint,
    FirstKey        int )

;WITH myCTE AS
(
SELECT  sessionKey,
        locationKey,
        instructorKey,
        sessionStart,
        sessionEnd,
        carKey,
        productTypeKey,
        sessionKey AS FirstKey
FROM    @DataFromYourTables
WHERE   sessionStart = @CancelTime
UNION ALL
SELECT  a.sessionKey,
        a.locationKey,
        a.instructorKey,
        a.sessionStart,
        a.sessionEnd,
        a.carKey,
        a.productTypeKey,
        FirstKey
FROM    @DataFromYourTables a
JOIN    myCTE b
ON      b.sessionEnd BETWEEN DATEADD(n, -29, a.SessionStart) AND DATEADD(n, 29, a.SessionStart)
)
INSERT INTO @Results
SELECT * FROM myCTE

;WITH myCTE AS
(
SELECT  sessionKey,
        locationKey,
        instructorKey,
        sessionStart,
        sessionEnd,
        carKey,
        productTypeKey,
        sessionKey AS FirstKey
FROM    @DataFromYourTables
WHERE   sessionStart = @CancelTime
UNION ALL
SELECT  a.sessionKey,
        a.locationKey,
        a.instructorKey,
        a.sessionStart,
        a.sessionEnd,
        a.carKey,
        a.productTypeKey,
        FirstKey
FROM    @DataFromYourTables a
JOIN    myCTE b
ON      b.sessionStart BETWEEN DATEADD(n, -29, a.SessionEnd) AND DATEADD(n, 29, a.SessionEnd)
)
INSERT INTO @Results
SELECT * FROM myCTE
WHERE  sessionKey <> FirstKey

SELECT  *,
        CASE
            WHEN sessionKey <> FirstKey THEN ''
            ELSE 'This is the cancelled session'
        END Comment
FROM    @Results
ORDER BY sessionStart

Open in new window

dsackerContract ERP Admin/ConsultantCommented:
This is a follow-up to the post above, as the next logical step is to change the code slightly, so that you are loading the temporary table with data from your database tables, rather than mock data. The following should be pretty close to what you want:

DECLARE @CancelTime   datetime
SET @CancelTime = '2015-07-01 16:15: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,
    productTypeKey  smallint    NOT NULL    )
/*
INSERT INTO @DataFromYourTables
SELECT 164192, NULL, '2015-07-01 06:00:00', '2015-07-01 08:00:00', 36754, NULL, 133, 2 UNION ALL
SELECT 164193, 34,   '2015-07-01 08:00:00', '2015-07-01 10:00:00', 36754, NULL, 133, 2 UNION ALL
--SELECT 164196, 34,   '2015-07-01 09:45:00', '2015-07-01 11:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164195, 34,   '2015-07-01 11:45:00', '2015-07-01 13:45:00', 13783, NULL, 133, 2 UNION ALL
SELECT 164085, 13,   '2015-07-01 14:00:00', '2015-07-01 16:00:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164096, 13,   '2015-07-01 16:15:00', '2015-07-01 18:15:00', 39097, NULL, 133, 2 UNION ALL
SELECT 164133, 13,   '2015-07-01 18:15:00', '2015-07-01 20:15:00', 39097, NULL, 133, 2
*/

-- Rather than load the inserts above, load up @DataFromYourTables with
-- this query straight from your database tables.

INSERT INTO @DataFromYourTables
SELECT  s.sessionKey,
        s.locationKey,
        su.sessionStart,
        su.sessionEnd,
        su.instructorKey,
        su.btwSeatsOverride AS Flag,
        s.carKey,
        p.productTypeKey
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 = 39097 OR s.carKey = 133)

DECLARE @Results TABLE (
    sessionKey      int,
    locationKey     smallint,
    instructorKey   int,
    sessionStart    datetime,
    sessionEnd      datetime,
    carKey          smallint,
    productTypeKey  smallint,
    Flag            int,
    FirstKey        int )

;WITH myCTE AS
(
SELECT  sessionKey,
        locationKey,
        instructorKey,
        sessionStart,
        sessionEnd,
        carKey,
        productTypeKey,
        Flag,
        sessionKey AS FirstKey
FROM    @DataFromYourTables
WHERE   sessionStart = @CancelTime
UNION ALL
SELECT  a.sessionKey,
        a.locationKey,
        a.instructorKey,
        a.sessionStart,
        a.sessionEnd,
        a.carKey,
        a.productTypeKey,
        a.Flag,
        FirstKey
FROM    @DataFromYourTables a
JOIN    myCTE b
ON      b.sessionEnd BETWEEN DATEADD(n, -29, a.SessionStart) AND DATEADD(n, 29, a.SessionStart)
)
INSERT INTO @Results
SELECT * FROM myCTE

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

SELECT  *,
        CASE
            WHEN sessionKey <> FirstKey THEN ''
            ELSE 'This is the cancelled session'
        END Comment
FROM    @Results
ORDER BY sessionStart

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:
Looking good still testing
erikTsomikSystem Architect, CF programmer Author Commented:
I Still need some help. Please reply
dsackerContract ERP Admin/ConsultantCommented:
Is the query above working out for you?
erikTsomikSystem Architect, CF programmer Author Commented:
It does I just have the logic flow in my thinking. I need to define 2 separate block and then combine it together.

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)
erikTsomikSystem Architect, CF programmer Author Commented:
I hope that clear.

With an existing code I am getting the record set that looks like this

sessionKey      instructorKey      sessionstart      sessionEnd      carKey      firstkey      Comment
164192      36754      2015-07-01 06:00:00.000      2015-07-01 08:00:00.000      133      164144      0
164202      8      2015-07-01 06:00:00.000      2015-07-01 08:00:00.000      139      164144      0
164103      12383      2015-07-01 08:15:00.000      2015-07-01 10:15:00.000      139      164144      0
164144      36754      2015-07-01 10:30:00.000      2015-07-01 12:30:00.000      139      164144      1

AS you can see the cancelled session is 164144 and it does not have a contiguous session withing 30 minutes according to a defenition (same instructor within 30 minutes) so the first record should not appear.

Now look at the adjoing session (for a different instructor with same car within 30 minutes). that the  session 164103 and that it do not need to go any further. Because this is the only adjoin session
erikTsomikSystem Architect, CF programmer Author Commented:
Any suggestions?
erikTsomikSystem Architect, CF programmer Author Commented:
I have made a change to the code but now I am getting more records for adjoin sessions instead of just 1. the change is in BOLD

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)
                )
                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
               [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[/b]
				
                )

                INSERT INTO @Results
                SELECT * FROM myCTE
               WHERE  sessionKey <> FirstKey
           select * from @Results
		   
		        
                SELECT  sessionKey,instructorKey,sessionstart,sessionEnd,carKey,firstkey,
                        CASE
                            WHEN sessionKey <> FirstKey THEN 0
                            ELSE 1
                        END Comment
                FROM    @Results
                ORDER BY sessionStart

Open in new window

dsackerContract ERP Admin/ConsultantCommented:
In the two WITH statements, add the instructor in the SELECT (you'll also need to define him in the @Results table. Then make sure your unioned query joins by that instructor.

This is if I understand you right. I'm in the middle if work, so if you'll play with it awhile, I can help more later.
erikTsomikSystem Architect, CF programmer Author Commented:
OK. I would really appreciate your help ASAP
erikTsomikSystem Architect, CF programmer Author Commented:
I already have the instructor in the @result table
dsackerContract ERP Admin/ConsultantCommented:
Ah, cool. I see what you're doing in line 120 of your code in post #40791671 (above). Didn't you say further up that the contiguous sessions should be the same instructor?

If so, I would think that line 120 should say:

and b.instructorKey = a.instructorKey  (take out the !, the "not")
You should also add that after line 84 in your code (above).
erikTsomikSystem Architect, CF programmer Author Commented:
if I change as you suggested I ma only getting 1 record in return the cancelled session.

I think line 120 should say and b.instructorKey != a.instructorKey  because we are taking about adjoin session (sessions that are done by a different instructor).

If I do that I get 2 adjoin session and I Need 1 (the one that is 30 minutes from the cancelled session)
dsackerContract ERP Admin/ConsultantCommented:
You'll want to put the same in the first WITH query, too.
erikTsomikSystem Architect, CF programmer Author Commented:
I believe the first with should only look into contiguous sessions for the same instructor. The second with should look for adjoin session (different instructor). I am correct .

SO the the first with I added b.instructorKey = a.instructorKey and for the second WITH b.instructorKey != a.instructorKey and b.carKey = a.carKey
dsackerContract ERP Admin/ConsultantCommented:
Did that make a difference?
erikTsomikSystem Architect, CF programmer Author Commented:
It did. I am getting one session for the same instructor, but 2 records for a different instructors who sits in the same car. That will true if second and third session will be occupied by the same instructor then they will also  be contiguous .Since the adjoin I only wan to process one instructor and check if that instructor have any contiguous sessions
dsackerContract ERP Admin/ConsultantCommented:
Well, that's where you'll have to join by the same instructor.

You could add a third WITH query for that. They're all being inserted into the @Results table, and you can filter out of that in the end.

It appears you're being able to take this baby and run with it a little more. Those recursives are pretty cool, eh?
erikTsomikSystem Architect, CF programmer Author Commented:
O ye. Can you help me filter them out to complete the query. I start understanding how it works, but help will be significant.
dsackerContract ERP Admin/ConsultantCommented:
I can certainly help you through the weekend more, just it's Friday around 1:10pm here, and I'm finishing up for the week. Will that be cool?
erikTsomikSystem Architect, CF programmer Author Commented:
If you can please help me know. it is 2:13 pm here I need to finish this up. This is the only piece that stopping me from processing other code. PLease
erikTsomikSystem Architect, CF programmer Author Commented:
Any suggestions
dsackerContract ERP Admin/ConsultantCommented:
Allow me to finish work. :)
erikTsomikSystem Architect, CF programmer Author Commented:
Sure.
dsackerContract ERP Admin/ConsultantCommented:
If you want to close this one out and start a new question, since we're now tying up the loose ends, I should be free in a few hours. I appreciate your patience. You were out all day a day or two ago, probably busy. We gotta recognize that work is as work must be, and I'm glad to continue helping. Just gotta keep my job. :)
erikTsomikSystem Architect, CF programmer Author Commented:
dsackerContract ERP Admin/ConsultantCommented:
I'll answer there in just a little while. If others decide to chime in, they'll have a lot to read, but if they are able to offer any help while I'm finishing my work this afternoon, that will of course benefit you. But I'll continue there soon as I can. Again, I appreciate your patience. Been good working with you so far.
erikTsomikSystem Architect, CF programmer Author Commented:
I hope you come back soon. I need help ASAP to figure this out
erikTsomikSystem Architect, CF programmer Author Commented:
Are you still working on your staff. I need your help desperately
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.