Solved

SQL help

Posted on 2016-09-13
5
62 Views
Last Modified: 2016-09-20
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

0
Comment
Question by:erikTsomik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 15

Expert Comment

by:Megan Brooks
ID: 41796715
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
 
LVL 19

Author Comment

by:erikTsomik
ID: 41796718
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41796750
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
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41797226
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41797227
Enjoy :)
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question