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

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

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

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

Select allOpen in new window

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)

Select allOpen in new window

I forgot to adjust the SELECT columns to include the SU_Near/S_Near columns you wanted so adjust as necessary.

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

Select allOpen in new window

erikTsomik, do you still need help with this question?