Summarizing Previous - Next - SQL Server Pt2

Hi,
I have the following:

CREATE TABLE [dbo].[EETest](
      [Rank] [int] NULL,
      [EmployeeID] [int] NULL,
      [Category] [nvarchar](15) NOT NULL
)
--
INSERT INTO dbo.EETest VALUES
(1, 1245, 'TestA')
,(2,1245 ,'TestB')
,(3,1245 ,'TestC')
,(4, 1245,'TestA')
,(5,1245 ,'TestB')
,(1,1200 ,'Test3')
,(2, 1200, 'Test4')
,(1, 1321 ,'TestA')
,(1, 1765 ,'TestB');

While this is returning almost what I need :

Select  A.EmployeeID,A.Category CategoryFrom, B.Category CategoryTo
From EETest A Join EETest B
on A.Rank = B.Rank-1 and A.EmployeeID = B.EmployeeID

But I need the results of the query to show  the following:

pls run for example:

CREATE TABLE [dbo].[EEResultsNeeded](
      [EmployeeID] [int] NULL,
      [CategoryFrom] [nvarchar](15) NOT NULL,
      [CategoryTo] [nvarchar](15) NOT NULL
)
--
INSERT INTO dbo.EEResultsNeeded VALUES
(1245, 'TestA','TestB')
,(1245 ,'TestB','TestC')
,(1245 ,'TestC','TestA')
,(1245,'TestA','TestB')
,(1200 ,'Test3','Test4')
,(1321 ,'TestA','No CategoryTo')
,(1765 ,'TestB','No CategoryTo');

Running SQL Server 2008
Jimmy_incAsked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
How about

Select  A.EmployeeID, A.Category CategoryFrom, ISNULL(B.Category,'No CategoryTo') CategoryTo
From EETest A 
Left Join EETest B on A.Rank = B.Rank-1 and A.EmployeeID = B.EmployeeID
Where a.Rank = 1 OR b.Rank >= 2

Open in new window

0
 
Patrick MatthewsConnect With a Mentor Commented:
This seems to be working for me:

SELECT a.EmployeeID, a.Category AS CategoryFrom,
    CASE WHEN z.MaxRank > 1 THEN b.Category ELSE 'No CategoryTo' END AS CategoryTo
FROM EETest a INNER JOIN
    (SELECT c.EmployeeID, MAX(c.Rank) AS MaxRank
    FROM EETest c
    GROUP BY c.EmployeeID) z ON a.EmployeeID = z.EmployeeID LEFT JOIN
    EETest b ON a.Rank = b.Rank - 1 AND a.EmployeeID = b.EmployeeID
WHERE a.Rank = 1 OR a.Rank < z.MaxRank

Open in new window

0
 
Patrick MatthewsConnect With a Mentor Commented:
This also works:

SELECT a.EmployeeID, a.Category AS CategoryFrom,
    COALESCE(b.Category, 'No CategoryTo') AS CategoryTo
FROM EETest a INNER JOIN
    (SELECT c.EmployeeID, MAX(c.Rank) AS MaxRank
    FROM EETest c
    GROUP BY c.EmployeeID) z ON a.EmployeeID = z.EmployeeID LEFT JOIN
    EETest b ON a.Rank = b.Rank - 1 AND a.EmployeeID = b.EmployeeID
WHERE a.Rank = 1 OR a.Rank < z.MaxRank

Open in new window

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.