0

I created this SQL and dont know how to query the next row. We use SQL 2008 so cant use lag/lead

SELECT P_MTN AS MTN
  ,P_DATE AS DATE
  ,CONVERT(VARCHAR(50), SEQU) AS NUM
  ,item_id + '  ||  ' + CONVERT(VARCHAR(50), PRODUCTION_ID) + '  ||  ' + 
    display_description AS ITEM_1
FROM dbo.Equipment INNER JOIN
  dbo.ERAT ON SEQUENCE = SEQU INNER JOIN
  Track.dbo.item_link ON link_id = PRODUCTION_ID INNER JOIN
  Track.dbo.LinkParentDescription ON item_id = item_id
ORDER BY NUM ASC

Basically what I want to do is..

If the NUM value is the same as the next row's NUM value then add a new column populated with 

item_id + '  ||  ' + CONVERT(VARCHAR(50), PRODUCTION_ID) + '  ||  ' + display_description AS ITEM_2

using the info from the next row.

I've had to anonnymise this code as I was told to do so, so hopefully the code is still valid (lol)

enter image description here

Wayne Gilliver
  • 125
  • 1
  • 12

1 Answers1

0

Can you use a window function on SQL Server 2008? The equivalent of LEAD:

MAX(NUM) OVER(ORDER BY NUM ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)

This expression will give you the NUM value of the next row, where the rows are ordered by NUM ascending. Then just do a comparison with a CASE expression:

CASE 
  WHEN NUM = MAX(NUM) OVER(ORDER BY NUM ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) 
    THEN item_id + '  ||  ' + CONVERT(VARCHAR(50), PRODUCTION_ID) + '  ||  ' 
         + display_description
  ELSE YYY
END AS ITEM_2
ravioli
  • 3,749
  • 3
  • 14
  • 28