SQL help

I need to get the next and a previous instructor for the session, I got the code working with one little thing The instructor have to distinct.

DECLARE @sessionKey INT
         SET @sessionKey = 211928

        ;WITH CTE AS(
            SELECT  S.sessionKey,rownum = ROW_NUMBER() OVER(ORDER BY SU.sessionStart desc),S.carKey,SU.sessionStart,U.lastName,U.firstName,U.cellPhone,SU.sessionEnd,SU.instructorKey
            FROM Session S
            INNER join SessionUnit SU on SU.sessionKey = S.sessionKey
            INNER join users U on U.userKey = SU.instructorKey
            where S.carKey =72
            and Exists (
                SELECT 1
                FROM sessionMap SM
                WHERE SM.sessionKey = S.sessionKey
            )
        )

        SELECT
        prev.lastname + ', ' + prev.firstname as prevInsName,
        nex.lastname + ', ' + nex.firstname as nexInsName,
        prev.cellPhone as prevInsCell,
        nex.cellPhone as nextInsCell,
        prev.sessionEnd  as prevFinish,
        nex.sessionStart as nextStart
        FROM CTE
      	LEFT JOIN CTE nex ON nex.rownum = CTE.rownum - 1 and CTE.instructorKey <> nex.instructorKey
		LEFT JOIN CTE prev ON prev.rownum = CTE.rownum + 1 and CTE.instructorKey <> prev.instructorKey
        WHERE CTE.sessionKey = @sessionKey
        and CTE.carKey =72
        order by CTE.sessionStart desc

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
SQL Server Query – 2 | [ Finding Next Value ]

--Create table
 
CREATE TABLE lag
(
 BusinessEntityID INT
,SalesYear   INT
,CurrentQuota DECIMAL(20,4)
)
 
GO
 
--Insert Data
INSERT INTO lag
SELECT 275 , 2005 , '367000.00'
UNION ALL
SELECT 275 , 2005 , '556000.00'
UNION ALL
SELECT 275 , 2006 , '502000.00'
UNION ALL
SELECT 275 , 2006 , '550000.00'
UNION ALL
SELECT 275 , 2006 , '1429000.00'
UNION ALL
SELECT 275 , 2006 ,   '1324000.00'
 
--Check data
SELECT BusinessEntityID,SalesYear,CurrentQuota FROM lag

---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------
 
 
;WITH CTE AS
(
    SELECT BusinessEntityID,SalesYear,CurrentQuota,ROW_NUMBER() OVER (ORDER BY %%Physloc%%) rnk  
    FROM lag
)
SELECT    BusinessEntityID,SalesYear,CurrentQuota
        , ISNULL(( SELECT TOP 1 CurrentQuota FROM CTE c2 WHERE c1.rnk < c2.rnk ),0) NextCurrentData
FROM CTE c1
 
 
---------------------------------------
--Sol 2 | Pawan Kumar Khowal
---------------------------------------
 
 
;WITH CTE AS
(
    SELECT BusinessEntityID,SalesYear,CurrentQuota,ROW_NUMBER() OVER (ORDER BY %%Physloc%%) rnk  
    FROM lag
)
SELECT    BusinessEntityID,SalesYear,CurrentQuota
          ,ISNULL(LEAD(CurrentQuota) OVER (ORDER BY rnk),0) NextCurrentData     
FROM CTE c1
 
 
--

Open in new window



[SQL Server -  Finding Previous Value from the List]

--So we first create the table
 
CREATE TABLE lag
(
BusinessEntityID INT
,SalesYear   INT
,CurrentQuota  DECIMAL(20,4)
)
GO
 
INSERT INTO lag
SELECT 275 , 2005 , '367000.00'
UNION ALL
SELECT 275 , 2005 , '556000.00'
UNION ALL
SELECT 275 , 2006 , '502000.00'
UNION ALL
SELECT 275 , 2006 , '550000.00'
UNION ALL
SELECT 275 , 2006 , '1429000.00'
UNION ALL
SELECT 275 , 2006 ,  '1324000.00'


--
 
/************   SOLUTION 1    | Pawan Kumar Khowal     ****************/
 
;WITH CTE AS
( 
    SELECT  BusinessEntityID ,SalesYear ,CurrentQuota
    , ROW_NUMBER() OVER (ORDER BY BusinessEntityID ) AS ID
    FROM lag
)
SELECT c.BusinessEntityID ,c.SalesYear , c.CurrentQuota
    , ISNULL((SELECT TOP 1 d.CurrentQuota FROM CTE d WHERE c.ID > d.ID ORDER BY ID DESC ),0) lagCurrentData
FROM CTE c
 
 
/************   SOLUTION 2    | Pawan Kumar Khowal     ****************/
 
;WITH CTE AS
(
 SELECT BusinessEntityID ,SalesYear ,CurrentQuota ,ROW_NUMBER()OVER (ORDERBY BusinessEntityID )AS ID
 FROM lag
)
SELECT c.Id ,c.BusinessEntityID ,c.SalesYear , c.CurrentQuota,ISNULL(d.CurrentQuota,0) lagCurrentData
FROM CTE c LEFTOUTERJOIN CTE d ON c.ID =(d.ID+1)
 
 
--

Open in new window

0
 
Megan BrooksSQL Server ConsultantCommented:
I can't stop very long at the moment but I can tell you that LEAD and LAG window functions, introduced in SQL Server 2012 I believe, are a very convenient way to move forward and backward through an ordering sequence.

Could you please clarify what you mean by "The instructor have to distinct"?
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
Yes the instructor have to distinct (so there is no misunderstanding. When I say unique I mean different instructor). I can not use LEAD OR lag I am using SAL server 2008
0
 
Scott PletcherSenior DBACommented:
In SQL 2008, I think it's easiest/best to use a temp table for that, something like this:

DECLARE @sessionKey INT
SET @sessionKey = 211928

IF OBJECT_ID('tempdb.dbo.#session_work') IS NOT NULL
    DROP TABLE #session_work

SELECT TOP (0)
        S.sessionKey,rownum = ROW_NUMBER() OVER(ORDER BY SU.sessionStart desc),S.carKey,SU.sessionStart,U.lastName,U.firstName,U.cellPhone,SU.sessionEnd,SU.instructorKey
FROM Session S
INNER join SessionUnit SU on SU.sessionKey = S.sessionKey
INNER join users U on U.userKey = SU.instructorKey
where S.carKey =72
and Exists (
    SELECT 1
    FROM sessionMap SM
    WHERE SM.sessionKey = S.sessionKey
);

ALTER TABLE #session_work ADD PRIMARY KEY ( rownum );

INSERT INTO #session_work
SELECT S.sessionKey,rownum = ROW_NUMBER() OVER(ORDER BY SU.sessionStart desc),S.carKey,SU.sessionStart,U.lastName,U.firstName,U.cellPhone,SU.sessionEnd,SU.instructorKey
FROM Session S
INNER join SessionUnit SU on SU.sessionKey = S.sessionKey
INNER join users U on U.userKey = SU.instructorKey
where
S.sessionKey = @sessionKey
and S.carKey =72
and Exists (
    SELECT 1
    FROM sessionMap SM
    WHERE SM.sessionKey = S.sessionKey
);

SELECT
prev.lastname + ', ' + prev.firstname as prevInsName,
next.lastname + ', ' + nex.firstname as nexInsName,
prev.cellPhone as prevInsCell,
next.cellPhone as nextInsCell,
prev.sessionEnd  as prevFinish,
next.sessionStart as nextStart
FROM #session_work sw
OUTER APPLY (
    SELECT TOP (1) *
    FROM #session_work sw_prev
    WHERE sw_prev.rownum > sw.rownum AND sw_prev.instructorKey <> sw.instructorKey
    ORDER BY sw_prev.rownum
) AS prev
OUTER APPLY (
    SELECT TOP (1) *
    FROM #session_work sw_next
    WHERE sw_next.rownum < sw.rownum AND sw_next.instructorKey <> sw.instructorKey
    ORDER BY sw_next.rownum DESC
) AS next
order by sw.sessionStart desc
0
 
Pawan KumarDatabase ExpertCommented:
Enjoy :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.