• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

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
0
Jimmy_inc
Asked:
Jimmy_inc
  • 2
3 Solutions
 
Patrick MatthewsCommented:
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 MatthewsCommented:
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
 
lwadwellCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now