sql help

I need help building a list of contiguous sessions for the specific instructor OR A car.

For example, if the student  cancel a session from 1:00 to 3 :00pm I need to get all contiguous sessions for that sessions
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:
Can you post the layout of your table(s, if more than one). Any data representing your sessions would be great, too.

By contiguous, please verify: Do you want to see the sessions ending right at 1pm, as well as any beginning right at 3pm?
erikTsomikSystem Architect, CF programmer Author Commented:
Here is how I start. I start with a cancelled session

 SELECT S.sessionKey,SU.sessionStart,SU.sessionEnd,s.locationKey
                    FROM  Session S
                    INNER JOIN Sessionunit SU  on SU.sessionKey = S.sessionKey
                    INNER JOIN Product P  ON S.ProductKey = P.ProductKey AND P.ProductTypeKey IN (2,4)
                    WHERE
                    0=0
                     and (SU.instructorKey = 1397 OR S.carKey = 133 )
                   
                    and Convert(DATE,SU.sessionStart) = '7/1/2015'
                    and
                          (('2015-07-01 16:15:00.0' between DateAdd(minute, -29, SU.sessionStart) and DateAdd(minute, 29, SU.sessionEnd))
                             OR ' 2015-07-01 18:15:00.0' between DateAdd(minute, -29, SU.sessionStart) and DateAdd(minute, 29, SU.sessionEnd))
                           )
                           
                         

This will give me a list of all contiguous sessions for the cancelled sessions with a 29 minutes from start and end. For each record returned I need to know contiguous sessions as well.
erikTsomikSystem Architect, CF programmer Author Commented:
I think it will need a recursive call. I am using cold fusion as a back-end application.

Once I do that with a coldfusion I do a recursive call.

but some thing is not right there.
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:
Give me a moment with this. Question, though: What value in what field denotes a cancelled session?

I was thinking recursive, but also I was envisioning you needing something along the lines of one long record that has:

Cancelled SessionKey/Start/End/Loc, Previous Contig Session Key/Start/End/Loc, Next Contig Session Key/Start/End/Loc.

Open in new window

erikTsomikSystem Architect, CF programmer Author Commented:
there is field in the table session called btwSeatsOverride. If it set to null then it cancelled
erikTsomikSystem Architect, CF programmer Author Commented:
The idea if the session is attached to a location. location can be remote or a hub. If the session is scheduled to take place at the remote and get cancelled. I need to check if any continuous has no reservations. if the do not I will reset all of them to null, if the do just cancel the session  and do not change locations for any contiguous sessions including the one that being cancelled.

I hope it is clear what I am trying to do
dsackerContract ERP Admin/ConsultantCommented:
It's not completely clear, since I don't know what your UPDATE statement will be. Assuming the list will facilitate that, we may need to play with this a little bit. I'll pilot a query here, then you can tell me if it's headed in the right direction.

I'm going to assume the cancelled session is the boss hog. From that, I'll list its immediately previous and subsequent sessions (if any), as well as if they are cancelled (btwSeatsOverride IS NULL). See if this yields something that gives a start in the right direction:
SELECT  s.sessionKey,
        s.locationKey,
        su.sessionStart,
        su.sessionEnd,
        s.btwSeatsOverride AS Flag,
        t1.PrevKey,
        t1.PrevLoc,
        t1.PrevStart,
        t1.PrevEnd,
        t1.PrevFlag,
        t1.NextKey,
        t1.NextLoc,
        t1.NextStart,
        t1.NextEnd,
        t1.NextFlag
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)
OUTER APPLY ( SELECT TOP 1
                     s1.sessionKey       AS PrevKey,
                     s1.locationKey      AS PrevLoc,
                     su1.sessionStart    AS PrevStart,
                     su1.sessionEnd      AS PrevEnd,
                     s1.btwSeatsOverride AS PrevFlag
              FROM   session s1
              JOIN   sessionunit su1 ON su1.sessionKey = s1.sessionKey
              JOIN   product     p1  ON p1.productKey = s1.productKey AND p1.productTypeKey IN (2,4)
              WHERE  su1.sessionEnd <= su.sessionStart
              AND    su1.instructorKey = su.instructorKey
              AND    s1.carKey         = s.carKey
              ORDER BY su1.SessionEnd DESC ) t1
OUTER APPLY ( SELECT TOP 1
                     s2.sessionKey       AS NextKey,
                     s2.locationKey      AS NextLoc,
                     su2.sessionStart    AS NextStart,
                     su2.sessionEnd      AS NextEnd,
                     s2.btwSeatsOverride AS NextFlag
              FROM   session s2
              JOIN   sessionunit su2 ON su2.sessionKey = s2.sessionKey
              JOIN   product     p2  ON p2.productKey = s2.productKey AND p2.productTypeKey IN (2,4)
              WHERE  su2.sessionEnd >= su.sessionStart
              AND    su2.instructorKey = su.instructorKey
              AND    s2.carKey         = s.carKey
              ORDER BY su1.SessionEnd ) t2
WHERE   s.btwSeatsOverride IS NULL
AND    (su.instructorKey = 1397 OR s.carKey = 133)
AND     CONVERT(date, su.sessionStart) = '7/1/2015'

Open in new window

erikTsomikSystem Architect, CF programmer Author Commented:
It seems to be producing the right records with exception they not contiguous within a 29 minutes from the start of the session and the end of the session.

For example , below is the record set with the  session ID 164196 has the start time 9:45 till 1:45PM and the first session is from 6:00 AM to 8:00 AM they not contiguous.



164192	NULL	2015-07-01 06:00:00.000	2015-07-01 08:00:00.000	36754	NULL	NULL	NULL	NULL	NULL	NULL	164192	NULL	2015-07-01 06:00:00.000	2015-07-01 08:00:00.000	NULL
164196	34	2015-07-01 09:45:00.000	2015-07-01 11:45:00.000	13783	NULL	163713	34	2015-06-30 13:00:00.000	2015-06-30 15:00:00.000	NULL	164196	34	2015-07-01 09:45:00.000	2015-07-01 11:45:00.000	NULL
164195	34	2015-07-01 11:45:00.000	2015-07-01 13:45:00.000	13783	NULL	164196	34	2015-07-01 09:45:00.000	2015-07-01 11:45:00.000	NULL	164196	34	2015-07-01 09:45:00.000	2015-07-01 11:45:00.000	NULL
164085	13	2015-07-01 14:00:00.000	2015-07-01 16:00:00.000	39097	NULL	164073	NULL	2015-06-26 19:30:00.000	2015-06-26 21:30:00.000	NULL	164085	13	2015-07-01 14:00:00.000	2015-07-01 16:00:00.000	NULL
164096	13	2015-07-01 16:15:00.000	2015-07-01 18:15:00.000	39097	NULL	164085	13	2015-07-01 14:00:00.000	2015-07-01 16:00:00.000	NULL	164096	13	2015-07-01 16:15:00.000	2015-07-01 18:15:00.000	NULL
164133	13	2015-07-01 18:15:00.000	2015-07-01 20:15:00.000	39097	NULL	164096	13	2015-07-01 16:15:00.000	2015-07-01 18:15:00.000	NULL	164096	13	2015-07-01 16:15:00.000	2015-07-01 18:15:00.000	NULL

Open in new window

dsackerContract ERP Admin/ConsultantCommented:
I changed the su1.sessionStart and su2.sessionEnd to hopefullly limit them better. If you understand the OUTER APPLY (it's the LEFT JOIN version of a CROSS APPLY), it may be the key to getting there.

Please try this:

SELECT  s.sessionKey,
        s.locationKey,
        su.sessionStart,
        su.sessionEnd,
        s.btwSeatsOverride AS Flag,
        t1.PrevKey,
        t1.PrevLoc,
        t1.PrevStart,
        t1.PrevEnd,
        t1.PrevFlag,
        t1.NextKey,
        t1.NextLoc,
        t1.NextStart,
        t1.NextEnd,
        t1.NextFlag
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)
OUTER APPLY ( SELECT TOP 1
                     s1.sessionKey       AS PrevKey,
                     s1.locationKey      AS PrevLoc,
                     su1.sessionStart    AS PrevStart,
                     su1.sessionEnd      AS PrevEnd,
                     s1.btwSeatsOverride AS PrevFlag
              FROM   session s1
              JOIN   sessionunit su1 ON su1.sessionKey = s1.sessionKey
              JOIN   product     p1  ON p1.productKey = s1.productKey AND p1.productTypeKey IN (2,4)
              WHERE  su1.instructorKey = su.instructorKey
              AND    s1.carKey         = s.carKey
              AND    su1.sessionEnd BETWEEN DATEADD(m, -29, su.sessionStart) AND su.sessionStart
              ORDER BY su1.SessionEnd DESC ) t1
OUTER APPLY ( SELECT TOP 1
                     s2.sessionKey       AS NextKey,
                     s2.locationKey      AS NextLoc,
                     su2.sessionStart    AS NextStart,
                     su2.sessionEnd      AS NextEnd,
                     s2.btwSeatsOverride AS NextFlag
              FROM   session s2
              JOIN   sessionunit su2 ON su2.sessionKey = s2.sessionKey
              JOIN   product     p2  ON p2.productKey = s2.productKey AND p2.productTypeKey IN (2,4)
              WHERE  su2.instructorKey = su.instructorKey
              AND    s2.carKey         = s.carKey
              AND    su2.sessionStart BETWEEN su.sessionEnd AND DATEADD(m, -29, su.sessionEnd)
              ORDER BY su1.SessionEnd ) t2
WHERE   s.btwSeatsOverride IS NULL
AND    (su.instructorKey = 1397 OR s.carKey = 133)
AND     CONVERT(date, su.sessionStart) = '7/1/2015'

Open in new window

erikTsomikSystem Architect, CF programmer Author Commented:
I tried this query and it is very close enough but I do not able to remove the first record starting from 6:00 AM to 8:00AM, is not contiguous to any of the listed sessions  

SELECT  s.sessionKey,
        s.locationKey,
        su.sessionStart,
        su.sessionEnd,
		su.instructorKey,
        su.btwSeatsOverride AS Flag,
      /*  t1.PrevKey,
        t1.PrevLoc,
        t1.PrevStart,
        t1.PrevEnd,
        t1.PrevFlag,*/
        t2.NextKey,
        t2.NextLoc,
        t2.NextStart,
        t2.NextEnd,
        t2.NextFlag
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)
OUTER APPLY ( SELECT TOP 1
                     s1.sessionKey     
                     
              FROM   session s1
              JOIN   sessionunit su1 ON su1.sessionKey = s1.sessionKey
              JOIN   product     p1  ON p1.productKey = s1.productKey AND p1.productTypeKey IN (2,4)
              WHERE  
                  su1.instructorKey = su.instructorKey
              AND    s1.carKey         = s.carKey
			  AND    su1.sessionEnd BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)
              ORDER BY su1.SessionEnd  ) t1
OUTER APPLY ( SELECT TOP 1
                     s2.sessionKey       AS NextKey,
                     s2.locationKey      AS NextLoc,
                     su2.sessionStart    AS NextStart,
                     su2.sessionEnd      AS NextEnd,
                     su2.btwSeatsOverride AS NextFlag
              FROM   session s2
              JOIN   sessionunit su2 ON su2.sessionKey = s2.sessionKey
              JOIN   product     p2  ON p2.productKey = s2.productKey AND p2.productTypeKey IN (2,4)
              WHERE  
                  su2.instructorKey = su.instructorKey
              AND    s2.carKey         = s.carKey
			  AND    su2.sessionStart BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)
              ORDER BY su2.SessionEnd ) t2
WHERE   
    (su.instructorKey = 39097 OR s.carKey = 133)
AND     CONVERT(date, su.sessionStart) = '7/1/2015'

Open in new window

erikTsomikSystem Architect, CF programmer Author Commented:
Also I found that in outer apply in between is not working I believe is due to the fact that we doing top 1
dsackerContract ERP Admin/ConsultantCommented:
The TOP 1 would not prevent it from working, and it's actually needed as we only want to include the nearest contiguous session.

You commented out the Prev fields and removed most of them from the OUTER APPLY query. If you don't really want them, you can comment out the entire OUTER APPLY query, as I have done below. Also, I've changed the OUTER APPLY to a CROSS APPLY (similar to an INNER JOIN), which should omit the 6-8am issue and any others when no contiguous session is found.

SELECT  s.sessionKey,
        s.locationKey,
        su.sessionStart,
        su.sessionEnd,
		su.instructorKey,
        su.btwSeatsOverride AS Flag,
      /*  t1.PrevKey,
        t1.PrevLoc,
        t1.PrevStart,
        t1.PrevEnd,
        t1.PrevFlag,*/
        t2.NextKey,
        t2.NextLoc,
        t2.NextStart,
        t2.NextEnd,
        t2.NextFlag
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)
/*OUTER APPLY ( SELECT TOP 1
                     s1.sessionKey     
                     
              FROM   session s1
              JOIN   sessionunit su1 ON su1.sessionKey = s1.sessionKey
              JOIN   product     p1  ON p1.productKey = s1.productKey AND p1.productTypeKey IN (2,4)
              WHERE  
                  su1.instructorKey = su.instructorKey
              AND    s1.carKey         = s.carKey
			  AND    su1.sessionEnd BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)
              ORDER BY su1.SessionEnd  ) t1*/
CROSS APPLY ( SELECT TOP 1 -- change back to OUTER APPLY if you prefer an INNER JOIN type of APPLY
                     s2.sessionKey       AS NextKey,
                     s2.locationKey      AS NextLoc,
                     su2.sessionStart    AS NextStart,
                     su2.sessionEnd      AS NextEnd,
                     su2.btwSeatsOverride AS NextFlag
              FROM   session s2
              JOIN   sessionunit su2 ON su2.sessionKey = s2.sessionKey
              JOIN   product     p2  ON p2.productKey = s2.productKey AND p2.productTypeKey IN (2,4)
              WHERE  
                  su2.instructorKey = su.instructorKey
              AND    s2.carKey         = s.carKey
			  AND    su2.sessionStart BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)
              ORDER BY su2.SessionEnd ) t2
WHERE   
    (su.instructorKey = 39097 OR s.carKey = 133)
AND     CONVERT(date, su.sessionStart) = '7/1/2015'

Open in new window

erikTsomikSystem Architect, CF programmer Author Commented:
Just ran it but still get the same result. Also I added the field to calculate newDate by adding 29 minutes to it,but it does not do the calculation.

SELECT  s.sessionKey,
        s.locationKey,
        su.sessionStart,
        su.sessionEnd,
            su.instructorKey,
        su.btwSeatsOverride AS Flag,
      t2.newStart
       
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)
/*OUTER APPLY ( SELECT TOP 1
                     s1.sessionKey    
                     
              FROM   session s1
              JOIN   sessionunit su1 ON su1.sessionKey = s1.sessionKey
              JOIN   product     p1  ON p1.productKey = s1.productKey AND p1.productTypeKey IN (2,4)
              WHERE  
                  su1.instructorKey = su.instructorKey
              AND    s1.carKey         = s.carKey
                    AND    su1.sessionEnd BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)
              ORDER BY su1.SessionEnd  ) t1*/
CROSS APPLY ( SELECT TOP 1 -- change back to OUTER APPLY if you prefer an INNER JOIN type of APPLY
                   
                               DATEADD(m, -29, su.sessionStart) as newStart
              FROM   session s2
              JOIN   sessionunit su2 ON su2.sessionKey = s2.sessionKey
              JOIN   product     p2  ON p2.productKey = s2.productKey AND p2.productTypeKey IN (2,4)
              WHERE  
                  su2.instructorKey = su.instructorKey
              AND    s2.carKey         = s.carKey
                    AND    su2.sessionStart BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)
              ORDER BY su2.SessionEnd ) t2
WHERE  
    (su.instructorKey = 39097 OR s.carKey = 133)
AND     CONVERT(date, su.sessionStart) = '7/1/2015'

The result
 164192      NULL      2015-07-01 06:00:00.000      2015-07-01 08:00:00.000      36754      NULL      2013-02-01 06:00:00.000
164196      34      2015-07-01 09:45:00.000      2015-07-01 11:45:00.000      13783      NULL      2013-02-01 09:45:00.000
164195      34      2015-07-01 11:45:00.000      2015-07-01 13:45:00.000      13783      NULL      2013-02-01 11:45:00.000
164085      13      2015-07-01 14:00:00.000      2015-07-01 16:00:00.000      39097      NULL      2013-02-01 14:00:00.000
164096      13      2015-07-01 16:15:00.000      2015-07-01 18:15:00.000      39097      NULL      2013-02-01 16:15:00.000
164133      13      2015-07-01 18:15:00.000      2015-07-01 20:15:00.000      39097      NULL      2013-02-01 18:15:00.000
erikTsomikSystem Architect, CF programmer Author Commented:
Also I found that this statement need to change
AND    su2.sessionStart BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)

to AND    su2.sessionStart BETWEEN DATEADD(minute, -29, su.sessionStart) AND DATEADD(minute, 29, su.sessionEnd)
erikTsomikSystem Architect, CF programmer Author Commented:
Also in my understanding of CROSS APPLY it will scan against every record of the outer query. In this case the query will not work because for example we take first record from 6:00 AM to 8:00 AM and scan through all the records in the CROSS APPLY which will include 6:00AM and 8:00 AM
dsackerContract ERP Admin/ConsultantCommented:
That's why I had two OUTER APPLY's, one for the contiguous sessions before the StartDate, and the second for any contiguous sessions after the EndDate.

My original direction was to code the primary SELECT to grab only cancelled sessions, and then to list the "Prev" set and the "Next" set in the same record. However, if you prefer to list either the prev or next sessions in the same OUTER APPLY, the two BETWEEN statements need to not be inclusive of the primary SELECT.

With that in mind, I would think the two BETWEEN statements should be like so (bold shows my changes):

AND  ( su2.sessionStart BETWEEN DATEADD(m, -29, su.sessionEnd) AND DATEADD(m, 29, su.sessionEnd)
    OR  su2.sessionEnd BETWEEN DATEADD(minute, -29, su.sessionStart) AND DATEADD(minute, 29, su.sessionStart) )
erikTsomikSystem Architect, CF programmer Author Commented:
What would the whole query looks like
dsackerContract ERP Admin/ConsultantCommented:
I changed the "Next<field>" set to "Contig<field>" names, since they can be either before or after. The idea is to have either (1) the possible contiguous start date somewhere at the primary end date, give or take 29 minutes, or (2) the possible contiguous end date somewhere at the primary start date, give or take 29 minutes.

Removing the unneeded fields and that first OUTER APPLY, the query would look as follows:

SELECT  s.sessionKey,
        s.locationKey,
        su.sessionStart,
        su.sessionEnd,
		su.instructorKey,
        su.btwSeatsOverride AS Flag,
        t.ContigKey,
        t.ContigLoc,
        t.ContigStart,
        t.ContigEnd,
        t.ContigFlag
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)
CROSS APPLY ( SELECT TOP 1 -- change back to OUTER APPLY if you prefer an INNER JOIN type of APPLY
                     s2.sessionKey        AS ContigKey,
                     s2.locationKey       AS ContigLoc,
                     su2.sessionStart     AS ContigStart,
                     su2.sessionEnd       AS ContigEnd,
                     su2.btwSeatsOverride AS ContigFlag
              FROM   session s2
              JOIN   sessionunit su2 ON su2.sessionKey = s2.sessionKey
              JOIN   product     p2  ON p2.productKey = s2.productKey AND p2.productTypeKey IN (2,4)
              WHERE  
                  su2.instructorKey = su.instructorKey
              AND    s2.carKey         = s.carKey
			  AND    su2.sessionStart BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)
              ORDER BY su2.SessionEnd ) t2
WHERE  (su.instructorKey = 39097 OR s.carKey = 133)
AND     CONVERT(date, su.sessionStart) = '7/1/2015'
AND    (su2.sessionStart BETWEEN DATEADD(n, -29, su.sessionEnd)   AND DATEADD(n, 29, su.sessionEnd)
    OR  su2.sessionEnd   BETWEEN DATEADD(n, -29, su.sessionStart) AND DATEADD(n, 29, su.sessionStart))

Open in new window

dsackerContract ERP Admin/ConsultantCommented:
Hi Erik, you may need to grab my query again (above). I changed DATEADD(m to DATEADD(n, as "n" is the shortcut for minute, whereas "m" was the shortcut for "month". That certainly would have skewed things.
erikTsomikSystem Architect, CF programmer Author Commented:
I ran the query is you advised and now getting no records at all

SELECT  s.sessionKey,
        s.locationKey,
        su.sessionStart,
        su.sessionEnd,
            su.instructorKey,
        su.btwSeatsOverride AS Flag
       
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)
CROSS APPLY ( SELECT TOP 1 -- change back to OUTER APPLY if you prefer an INNER JOIN type of APPLY
                     s2.sessionKey        AS ContigKey,
                     s2.locationKey       AS ContigLoc,
                     su2.sessionStart     AS ContigStart,
                     su2.sessionEnd       AS ContigEnd,
                     su2.btwSeatsOverride AS ContigFlag
              FROM   session s2
              JOIN   sessionunit su2 ON su2.sessionKey = s2.sessionKey
              JOIN   product     p2  ON p2.productKey = s2.productKey AND p2.productTypeKey IN (2,4)
              WHERE  
                  su2.instructorKey = su.instructorKey
              AND    s2.carKey         = s.carKey
                    AND    su2.sessionStart BETWEEN DATEADD(MINUTE, -29, su.sessionStart) AND DATEADD(MINUTE, 29, su.sessionEnd)
              ORDER BY su2.SessionEnd ) t2
WHERE  (su.instructorKey = 39097 OR s.carKey = 133)
AND     CONVERT(date, su.sessionStart) = '7/1/2015'
AND    (t2.ContigStart BETWEEN DATEADD(MINUTE, -29, su.sessionEnd)   AND DATEADD(MINUTE, 29, su.sessionEnd)
    OR  t2.ContigEnd   BETWEEN DATEADD(MINUTE, -29, su.sessionStart) AND DATEADD(MINUTE, 29, su.sessionStart))
dsackerContract ERP Admin/ConsultantCommented:
That means you had no contiguous sessions available.

Of course, you're checking only for 7/1/2015. You could check for a range of days, perhaps change that to:

AND     CONVERT(date, su.sessionStart) BETWEEN '2015-07-01' AND '2015-07-31'
Which would check the entire month of July.

If you want to stay at 7/1/2015 for awhile, temporarily change CROSS APPLY to OUTER APPLY, simply to see all of the sessions that day, thus verifying by eyesight that indeed you have no contiguous sessions available.
dsackerContract ERP Admin/ConsultantCommented:
To be able to help you further, you may need to attach an Excel spreadsheet of your data, perhaps three tabs, one for your session, one for your sessionunit and one for your product tables. Include the table layouts. Without being able to see results and data mine, I'm advising only on principle.

I have work to tend to, so my replies may slow down a bit.
erikTsomikSystem Architect, CF programmer Author Commented:
I only wan to see sessions for one day
erikTsomikSystem Architect, CF programmer Author Commented:
Here is complete record SET for July 1,

sessionKey      locationKey      sessionStart      sessionEnd      instructorKey      Flag
164192      NULL      2015-07-01 06:00:00.000      2015-07-01 08:00:00.000      36754      NULL
164103      NULL      2015-07-01 08:15:00.000      2015-07-01 10:15:00.000      12383      NULL
164196      34      2015-07-01 09:45:00.000      2015-07-01 11:45:00.000      13783      NULL
164144      NULL      2015-07-01 10:30:00.000      2015-07-01 12:30:00.000      36754      NULL
164195      34      2015-07-01 11:45:00.000      2015-07-01 13:45:00.000      13783      NULL
164104      NULL      2015-07-01 13:00:00.000      2015-07-01 15:00:00.000      12383      NULL
164085      13      2015-07-01 14:00:00.000      2015-07-01 16:00:00.000      39097      NULL
164109      NULL      2015-07-01 15:15:00.000      2015-07-01 17:15:00.000      2644      NULL
164096      13      2015-07-01 16:15:00.000      2015-07-01 18:15:00.000      39097      NULL
164111      NULL      2015-07-01 17:30:00.000      2015-07-01 19:30:00.000      2644      NULL
164133      13      2015-07-01 18:15:00.000      2015-07-01 20:15:00.000      39097      NULL
164194      NULL      2015-07-01 19:45:00.000      2015-07-01 21:45:00.000      13783      NULL
dsackerContract ERP Admin/ConsultantCommented:
Please post the result set from this query:

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) = '7/1/2015'
AND    (su.instructorKey = 39097 OR s.carKey = 133)

Open in new window

erikTsomikSystem Architect, CF programmer Author Commented:
Here is the result set

sessionKey      locationKey      sessionStart      sessionEnd      instructorKey      Flag      carKey      productTypeKey
164192      NULL      2015-07-01 06:00:00.000      2015-07-01 08:00:00.000      36754      NULL      133      2
164196      34      2015-07-01 09:45:00.000      2015-07-01 11:45:00.000      13783      NULL      133      2
164195      34      2015-07-01 11:45:00.000      2015-07-01 13:45:00.000      13783      NULL      133      2
164085      13      2015-07-01 14:00:00.000      2015-07-01 16:00:00.000      39097      NULL      133      2
164096      13      2015-07-01 16:15:00.000      2015-07-01 18:15:00.000      39097      NULL      133      2
164133      13      2015-07-01 18:15:00.000      2015-07-01 20:15:00.000      39097      NULL      133      2
dsackerContract ERP Admin/ConsultantCommented:
I took your results set and put it into a @Results temporary table. I then ran the query below, which returns the attached spreadsheet:

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 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  r.*,
        CASE
            WHEN r2.sessionEnd > r.sessionEnd THEN 'AFTER'
            ELSE 'BEFORE'
        END AS Which,
        r2.sessionStart,
        r2.sessionEnd,
        r2.instructorKey
FROM    @Results r
JOIN    @Results r2
ON     (r2.instructorKey = r.instructorKey OR r2.carKey = r.carKey)
AND    (r2.sessionStart BETWEEN DATEADD(n, -29, r.sessionEnd)   AND DATEADD(n, 29, r.sessionEnd)
    OR  r2.sessionEnd   BETWEEN DATEADD(n, -29, r.sessionStart) AND DATEADD(n, 29, r.sessionStart))
WHERE   r2.Flag IS NULL
ORDER BY r.sessionStart, Which DESC

Open in new window

The @Results table is simply a result of the joined session, sessionunit and product tables, filtered only by the carKey and instructorKey, so look at it as you would the bigger SELECT statement.

I have abandoned the CROSS APPLY for a JOIN (same as INNER JOIN, code whichever you like), which may be more what you want. The attached spreadsheet shows the "BEFORE" and "AFTER" sessions which are available. Some of the data to the right will be redundant until you start filling in cancelled sessions (hence the WHERE r2.Flag IS NULL).

If this looks good, you may want to re-code the larger query along the principle of this one.

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:
I tried this code and it partially works. How can i do it in regards of the cancelled session. I may have  a 2 blocks.

from 6 to 8 ,8 to 10,10 to 12 which are contiguous. And then 2 to 4,4 to 6 . If I cancel 2 to 4 session the only contiguous session is  4 to 6 and  6 to 8 ,8 to 10,10 to 12 are not contiguous based on the cancelled session
dsackerContract ERP Admin/ConsultantCommented:
Your result set in post #40785634 (three posts above) did not show an 8-10 session, which is why my query did not show the 6-8 record.

I'm not sure exactly what you mean in your last paragraph above, but if it means you would like to list the next available session whenever the immediately contiguous session is not available, then we're back to using an OUTER APPLY or CROSS APPLY to get the next or previous closest session. Then contiguous really doesn't matter.

Did I interpret correctly?
dsackerContract ERP Admin/ConsultantCommented:
Also, Erik, we're going far beyond just listing contiguous sessions, into a complexity that is worthy of far more than just one 500-point question.

If you need to go any further, I suggest you award the 500 points, and open up a subsequent question.
erikTsomikSystem Architect, CF programmer Author Commented:
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
erikTsomikSystem Architect, CF programmer Author Commented:
I just award you with a points and post a new question. Please assist. My head is start boiling over this issue. Thank you

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28676589.html
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.