sql get a class 30 minutes from the start of the previous class

I am trying to write a query where I need a need to get the class withing 29 minutes from the start of the previous session.

I have came up with the following query :

;WITH myCTE (sessionKey,instructorKey,sessionStart,sessionEnd,carKey) AS (
				SELECT S.sessionKey,Su.instructorKey,SU.sessionStart,SU.sessionEnd,S.carKey
        		FROM Session S 
        		inner join SessionUnit SU on Su.sessionKey = S.sessionKey
        		WHERE SU.sessionKey in (166816)

				UNION ALL 
				SELECT b.sessionKey,b.instructorKey,b.sessionStart,b.sessionEnd,b.carKey
				FROM sessionUnit a
				inner join Session S2 on S2.sessionKey = a.sessionkey
				inner 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 = S2.carKey
)

SELECT * FROM myCTE

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.

Brian CroweDatabase AdministratorCommented:
Is it working?  What is wrong with what you are receiving.  It is fairly difficult to debug a recursive cte without data to run it against.  This can also be done fairly simply with a self joining query.

SELECT ...
FROM SessionUnit AS S
LEFT OUTER JOIN SessionUnit AS S2
   ON ABS(DATEDIFF(MINUTE, S.Sessionend, S2.SessionStart)) < 30
erikTsomikSystem Architect, CF programmer Author Commented:
I have the session where start time is  2015-11-25 12:15:00.000. I need to find all the session that will end within 30 minutes from the given date.

For example  ,this is the existing session

166816      34      2015-11-25 12:15:00.000      2015-11-25 14:15:00.000      9576      133

The sessions that am trying to pull is the following :
166817      13      2015-11-25 07:45:00.000      2015-11-25 09:45:00.000      19128      133      
166806      13      2015-11-25 10:00:00.000      2015-11-25 12:00:00.000      19128      133      

Because session 166806      will  finish within 30 minutes from the 166816      
and the session 166817       will finish within 30 minutes from 166806      

Hope I've made it clear
erikTsomikSystem Architect, CF programmer Author Commented:
I am trying to run this query but only return the session with ID 166816. Which is my main session that said the recursion is not happening .Why ?

			;WITH myCTE  AS (
				SELECT S.sessionKey,Su.instructorKey,SU.sessionStart,SU.sessionEnd,S.carKey,0 as level
        		FROM Session S 
        		inner join SessionUnit SU on Su.sessionKey = S.sessionKey
        		WHERE SU.sessionKey in (166816)

				UNION ALL 
				SELECT b.sessionKey,b.instructorKey,b.sessionStart,b.sessionEnd,b.carKey,Level+ 1
				FROM sessionUnit a
				inner join myCTE b

				 ON      ((b.sessionStart BETWEEN DATEADD(n, -29, a.SessionEnd) AND DATEADD(n, 29, a.SessionEnd)) )
				WHERE       b.carKey = 133 and a.instructorKey <> 9576               
)

SELECT * FROM myCTE

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Brian CroweDatabase AdministratorCommented:
Recursion is only necessary in this case if you are looking to continue the logic past the first connection.  If you wanted to know the sessions that ended within N minutes of another starting and then you wanted to know the sessions that ended within 30 minutes of those starting etc. then absolutely a recursive cte is the way to go.  Otherwise, in my opinion it unnecessarily complicates the query and makes it harder to maintain should you need to adjust it later.

SELECT S.sessionKey, Su.instructorKey, SU.sessionStart, SU.sessionEnd, S.carKey
FROM [Session] AS S 
INNER JOIN SessionUnit AS SU
	ON Su.sessionKey = S.sessionKey
INNER JOIN SessionUnit AS SU_Near
	ON ABS(DATEDIFF(MINUTE, SU.sessionStart, SU_Near.sessionEnd)) <= 30 -- Find sessionUnits witin that end within 30 minutes of target sessionUnit
	AND SU.sessionKey <> SU_Near.SessionKey	-- Eliminate joins to same record
INNER JOIN [Session] AS S_Near
	ON SU_Near.sessionKey = S_Near.SessionKey
WHERE SU.sessionKey IN (166816)

Open in new window

Brian CroweDatabase AdministratorCommented:
I forgot to adjust the SELECT columns to include the SU_Near/S_Near columns you wanted so adjust as necessary.
PortletPaulEE Topic AdvisorCommented:
Why are you starting with 166816? isn't the the end of the chain?

Using the 3 rows of data shown:
| sessionKey |               sessionStart |                 sessionEnd |
|------------|----------------------------|----------------------------|
|     166817 | November, 25 2015 07:45:00 | November, 25 2015 09:45:00 |
|     166806 | November, 25 2015 10:00:00 | November, 25 2015 12:00:00 |
|     166816 | November, 25 2015 12:15:00 | November, 25 2015 14:15:00 |

Open in new window

Then using this query
SELECT *
FROM Session AS S
  LEFT OUTER JOIN Session AS S2
     ON S2.sessionStart > S.sessionEnd AND S2.sessionStart <= dateadd(minute,30,S.sessionEnd)
  LEFT OUTER JOIN Session AS S3
     ON S3.sessionStart > S2.sessionEnd AND S3.sessionStart <= dateadd(minute,30,S2.sessionEnd)
WHERE S.sessionKey = 166817

Open in new window

result:
| sessionKey | instructorKey |               sessionStart |                 sessionEnd | carKey |   x | sessionKey | instructorKey |               sessionStart |                 sessionEnd | carKey |   x | sessionKey | instructorKey |               sessionStart |                 sessionEnd | carKey |   x |
|------------|---------------|----------------------------|----------------------------|--------|-----|------------|---------------|----------------------------|----------------------------|--------|-----|------------|---------------|----------------------------|----------------------------|--------|-----|
|     166817 |            13 | November, 25 2015 07:45:00 | November, 25 2015 09:45:00 |  19128 | 133 |     166806 |            13 | November, 25 2015 10:00:00 | November, 25 2015 12:00:00 |  19128 | 133 |     166816 |            34 | November, 25 2015 12:15:00 | November, 25 2015 14:15:00 |   9576 | 133 |
        

Open in new window

see: http://sqlfiddle.com/#!6/bdfad/1 
CREATE TABLE Session 
    ([sessionKey] int, [instructorKey] int, [sessionStart] datetime, [sessionEnd] datetime, [carKey] int, [x] int)
;
    
INSERT INTO Session 
    ([sessionKey], [instructorKey], [sessionStart], [sessionEnd], [carKey], [x])
VALUES
    (166816, 34, '2015-11-25 12:15:00', '2015-11-25 14:15:00', 9576, 133),
    (166817, 13, '2015-11-25 07:45:00', '2015-11-25 09:45:00', 19128, 133),
    (166806, 13, '2015-11-25 10:00:00', '2015-11-25 12:00:00', 19128, 133)
;

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:
I think I got it . I do nee to use recursion

			WITH myCTE  AS (
				SELECT S.sessionKey,Su.instructorKey,SU.sessionStart,SU.sessionEnd,S.carKey,0 as level
        		FROM Session S 
        		inner join SessionUnit SU on Su.sessionKey = S.sessionKey
        		WHERE SU.sessionKey in (166816)

				UNION ALL 
				SELECT a.sessionKey,a.instructorKey,a.sessionStart,a.sessionEnd,b.carKey,Level+ 1
				FROM sessionUnit a
				inner join myCTE b

				 ON      ((a.sessionEnd BETWEEN DATEADD(n, -29, b.SessionStart) AND DATEADD(n, 29, b.SessionEnd)) ) 
				 and b.sessionKey != a.sessionKey
				WHERE       b.carKey = 133 and a.instructorKey <> 9576       
)

SELECT * FROM myCTE
WHERE level > 0

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
erikTsomik, do you still need help with this question?
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.