I’m using SQL Server 2014.
I have a table:
CREATE TABLE [dbo].tmpJobStatus ( [JobNum] [INT] NOT NULL, [Action] [VARCHAR](8) NULL, [ActionDate] [DATE] NULL, [SeqNum] [INT] NULL ) ON [PRIMARY]
I have some data:
INSERT INTO tmpJobStatus ([JobNum], [Action], [ActionDate], [SeqNum]) VALUES (12345, N'TEL', CAST(N'2019-07-05' AS Date), 19), (12345, N'AL2', CAST(N'2019-07-02' AS Date), 15), (12345, N'AL1', CAST(N'2019-05-28' AS Date), 8), (12345, N'TELA', CAST(N'2019-05-16' AS Date), 2), (6789, N'MAIN', CAST(N'2019-11-04' AS Date), 25), (6789, N'MAIN', CAST(N'2019-11-04' AS Date), 21), (6789, N'TEL', CAST(N'2019-10-29' AS Date), 20), (6789, N'MACO', CAST(N'2019-10-28' AS Date), 13), (6789, N'UCIN', CAST(N'2019-09-12' AS Date), 11)
If I perform a LAG
and LEAD
using the below for one Job, I get the desired result:
SELECT [JobNum], [ActionDate], [SeqNum], LAG(p.[Action]) OVER (ORDER BY p.[JobNum],p.[SeqNum]) FromActionCode, LEAD(p.[Action]) OVER (ORDER BY p.[JobNum],p.[SeqNum]) ToActionCode FROM [tmpJobStatus] p WHERE [JobNum] = 6789 ORDER BY p.[JobNum], p.[SeqNum] DESC
Result:
JobNum ActionDate SeqNum FromActionCode ToActionCode 6789 2019-11-04 25 MAIN NULL 6789 2019-11-04 21 TEL MAIN 6789 2019-10-29 20 MACO MAIN 6789 2019-10-28 13 UCIN TEL 6789 2019-09-12 11 NULL MACO
Notice the NULL for the first FromActionCode and LastToCode, which is correct.
However, If I look at all records, it messes it up:
JobNum ActionDate SeqNum FromActionCode ToActionCode 6789 2019-11-04 25 MAIN TELA 6789 2019-11-04 21 TEL MAIN 6789 2019-10-29 20 MACO MAIN 6789 2019-10-28 13 UCIN TEL 6789 2019-09-12 11 NULL MACO 12345 2019-07-05 19 AL2 NULL 12345 2019-07-02 15 AL1 TEL 12345 2019-05-28 8 TELA AL2 12345 2019-05-16 2 MAIN AL1
What am I missing?
Advertisement
Answer
Did you try with PARTITION
SELECT [JobNum], [ActionDate], [SeqNum], LAG(p.[Action]) OVER (PARTITION BY p.[JobNum] ORDER BY p.[SeqNum]) FromActionCode, LEAD(p.[Action]) OVER (PARTITION BY p.[JobNum] ORDER BY p.[SeqNum]) ToActionCode FROM [tmpJobStatus] p ORDER BY p.[JobNum],p.[SeqNum] desc