CODEWITHSUNDEEP
sql-serversequence
Dizzy49
Dizzy49

Reputation: 1530

Return Previous and Next Value in Sequence Based on Current Value

I have the following table:

CREATE TABLE [opTest] 
(
    [name] varchar(50),
    [task] varchar(50),
    [desc] varchar(100),
    [seq] varchar(5)
)

INSERT INTO [opTest] VALUES 
('Yale', 'Paint', 'Prime Part', '100'),
('Yale', 'Paint', 'Paint Part', '200'),
('Yale', 'Assembly', 'Front Wheel Assembly', '100'),
('Yale', 'Assembly', 'Rear Wheel Assembly', '200'),
('Yale', 'Assembly', 'Chain Assembly', '300'),
('Yale', 'Assembly', 'HUB Assembly', '400'),
('Yale', 'Assembly', 'Final Assembly', '500'),
('Yale', 'CNC Inspection', 'CNC Machine Inspection', '100')

+------+----------------+------------------------+-----+
| name | task           | desc                   | seq |
+------+----------------+------------------------+-----+
| Yale | Paint          | Prime Part             | 100 |
| Yale | Paint          | Paint Part             | 200 |
| Yale | Assembly       | Front Wheel Assembly   | 100 |
| Yale | Assembly       | Rear Wheel Assembly    | 200 |
| Yale | Assembly       | Chain Assembly         | 300 |
| Yale | Assembly       | HUB Assembly           | 400 |
| Yale | Assembly       | Final Assembly         | 500 |
| Yale | CNC Inspection | CNC Machine Inspection | 100 |
+------+----------------+------------------------+-----+

I am want to output the data with the Previous, Current and Next task in the sequence. I want it to look like this:

+------+----------------+------------------------+-----------+-----------+-----------+
| Name | Task           | Description            | Prev Task | Curr Task | Next Task |
+------+----------------+------------------------+-----------+-----------+-----------+
| Yale | Assembly       | Front Wheel Assembly   | NULL      | 100       | 200       |
| Yale | Assembly       | Rear Wheel Assembly    | 100       | 200       | 300       |
| Yale | Assembly       | Chain Assembly         | 200       | 300       | 400       |
| Yale | Assembly       | HUB Assembly           | 300       | 400       | 500       |
| Yale | Assembly       | Final Assembly         | 400       | 500       | NULL      |
| Yale | CNC Inspection | CNC Machine Inspection | NULL      | 100       | NULL      |
| Yale | Paint          | Prime Part             | NULL      | 100       | 200       |
| Yale | Paint          | Paint Part             | 100       | 200       | NULL      |
+------+----------------+------------------------+-----------+-----------+-----------+

I am using the following query and I can't figure out why it's not working. I did a simple test with a single field table and it worked. The only diff was that the field was int in my test, but I am casting to int, so it shouldn't be a problem.

SELECT
    [Name] AS [Name],
    [Task] AS [Task],
    [Desc] AS [Description],
    (SELECT TOP(1) t1.[Seq] 
     FROM [opTest] t1 
     WHERE CAST(t1.[Seq] AS int) > CAST([Seq] AS int) 
     ORDER BY t1.[Seq] ASC) AS [Prev Task],
    [Seq] AS [Curr Task],
    (SELECT TOP(1) t1.[Seq] 
     FROM [opTest] t1 
     WHERE CAST(t1.[Seq] AS int) < CAST([Seq] AS int) 
     ORDER BY t1.[Seq] DESC) AS [Next Task]
FROM 
    [opTest]
ORDER BY 
    [Name] ASC, [Task] ASC, [Seq] ASC

That returns this result:

+------+----------------+------------------------+-----------+-----------+-----------+
| Name | Task           | Description            | Prev Task | Curr Task | Next Task |
+------+----------------+------------------------+-----------+-----------+-----------+
| Yale | Assembly       | Front Wheel Assembly   | NULL      | 100       | NULL      |
| Yale | Assembly       | Rear Wheel Assembly    | NULL      | 200       | NULL      |
| Yale | Assembly       | Chain Assembly         | NULL      | 300       | NULL      |
| Yale | Assembly       | HUB Assembly           | NULL      | 400       | NULL      |
| Yale | Assembly       | Final Assembly         | NULL      | 500       | NULL      |
| Yale | CNC Inspection | CNC Machine Inspection | NULL      | 100       | NULL      |
| Yale | Paint          | Prime Part             | NULL      | 100       | NULL      |
| Yale | Paint          | Paint Part             | NULL      | 200       | NULL      |
+------+----------------+------------------------+-----------+-----------+-----------+

Why am I not getting the previous and next sequences?

Upvotes: 0

Views: 78

Answers (2)

anderson spadoni
anderson spadoni

Reputation: 31

I see two problems.

First, in both subqueries you compare the field [seq] with itself. You have to be explicit in both [seq] mentions of the subqueries about which level of the query you want compared. When not explicit, it just defaults to the current level.

Second, you only filter the [seq] in both subqueries. Which means it will show the previous or next value for it regardless of it being actually related to the [task].

Also, just like the previous answer mentions. Why not just use LEAD and LAG functions?

Upvotes: 1

Squirrel
Squirrel

Reputation: 24813

On your query, you should use table alias and prefix all the column name with alias.

Your problem is you didn't specify the alias for the second column [Seq]. Is it from t1 or [opTest] ? SQL Server assumed it to be from t1

  CAST(t1.[Seq] AS int) 
> CAST([Seq] AS int)    -- this is from which table ?

To fix your query, add appropriate alias to the column. Eg.T.[Seq]

SELECT
    [Name]  AS [Name],
    [Task]  AS [Task],
    [Desc]  AS [Description],
    ( SELECT TOP(1) t1.[Seq] FROM [opTest] t1 
      WHERE CAST(t1.[Seq] AS int) > CAST(T.[Seq] AS int) 
      ORDER BY t1.[Seq] ASC )
            AS [Prev Task],
    [Seq]   AS [Curr Task],
    ( SELECT TOP(1) t1.[Seq] FROM [opTest] t1 
      WHERE CAST(t1.[Seq] AS int) < CAST(T.[Seq] AS int) 
      ORDER BY t1.[Seq] DESC )
            AS [Next Task]
  FROM [opTest] T
ORDER BY [Name] ASC, [Task] ASC, [Seq] ASC

You can use LEAD() or LAG() and avoid the use of sub-query

Upvotes: 1

Related Questions