CODEWITHSUNDEEP
sqlsql-server
Joel Vega
Joel Vega

Reputation: 23

SQL Dynamic Lead

I need a way to use the offset for the LEAD function SQL dynamically. My data is structured as follows:

Sample Data

What I am trying to accomplish is to retrieve the value of D365Object from the next row with the same GMLD like this:

Results I Want

My query for the sample file is as follows:

SELECT [D365ODept],[D365OObject],[GMLDA]
FROM [JDE].[F0901]
WHERE isnumeric([D365OObject]) = 1  
GROUP BY [D365ODept],[D365OObject],[GMLDA]
ORDER BY [D365ODept],[D365OObject],[GMLDA]

Upvotes: 0

Views: 481

Answers (1)

Jon Jaussi
Jon Jaussi

Reputation: 1296

As per the advice from @ZLK in the comments...

CREATE TABLE F0901  
(
  D365ODept VARCHAR(50),
  D365OObject VARCHAR(10),
  GMLDA INT,
);

INSERT INTO F0901 (D365ODept, D365OObject, GMLDA) VALUES ('00', '01100', '6');
INSERT INTO F0901 (D365ODept, D365OObject, GMLDA) VALUES ('00', '01100', '7');
INSERT INTO F0901 (D365ODept, D365OObject, GMLDA) VALUES ('00', '01200', '5');
INSERT INTO F0901 (D365ODept, D365OObject, GMLDA) VALUES ('00', '01210', '6');
INSERT INTO F0901 (D365ODept, D365OObject, GMLDA) VALUES ('00', '01210', '7');
INSERT INTO F0901 (D365ODept, D365OObject, GMLDA) VALUES ('00', '01250', '6');

SELECT f.D365ODept
      ,f.D365OObject
      ,f.GMLDA
      ,LEAD(f.D365OObject) OVER (PARTITION BY f.GMLDA ORDER BY f.D365OObject) as next_D365OObject
  FROM F0901 f;

If you choose to include code that builds and loads a schema in your question as opposed to images, a larger number of folks will be interested in answering.

Click here to learn how to format code blocks

Hope this helps.

Upvotes: 1

Related Questions