How to use a window function to refer to a newer record?

Question

I believe I have a requirement to use a window function, but I am not too sure. My data is in the below format:

JobID   ActiveFromDate  RecID   TypeID
J1      01/12/2020      441166  LI2
J1      25/11/2020      375213  LO1
J1      24/11/2020      375207  LO2
J2      02/12/2020      441378  LO1
J2      01/12/2020      376896  LO2
J2      30/11/2020      375288  LI1
J3      03/12/2020      441894  LO2
J3      01/11/2020      374558  LI1
J3      31/10/2020      371285  LI2
J4      02/12/2020      441295  LI1
J4      13/11/2020      374598  LO2
J4      01/11/2020      374423  LO1

I want to get to this:

JobID   ActiveFromDate  ActiveToDate    RecID   TypeID
J1      01/12/2020                      441166  LI2
J1      25/11/2020      01/12/2020      375213  LO1
J1      24/11/2020      25/11/2020      375207  LO2
J2      02/12/2020                      441378  LO1
J2      01/12/2020      02/12/2020      376896  LO2
J2      30/11/2020      01/12/2020      375288  LI1
J3      03/12/2020                      441894  LO2
J3      01/11/2020      03/12/2020      374558  LI1
J3      31/10/2020      01/11/2020      371285  LI2
J4      02/12/2020                      441295  LI1
J4      13/11/2020      02/12/2020      374598  LO2
J4      01/11/2020      13/11/2020      374423  LO1

The AuditToDate is calculated per JobID. If there is a newer audit record for that JobID, then the AuditToDate is the AuditFromDate of the newer audit record. Otherwise it is blank.

Any ideas how I could do this?

Popeye · Accepted Answer · 2020-12-04 11:09:17Z

2

LEAD will fit here perfectly as follows:

SELECT JobID, ActiveFromDate,
       LEAD(ActiveFromDate) OVER (PARTITION BY JobID ORDER BY ActiveFromDate) AS ActiveToDate,
    RecID, TypeID
FROM table

answered Dec 4, 2020 at 11:09

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Naz Over a year ago

Many thanks. I didnt even know about this function but it does the job perfectly as you said

Collectives™ on Stack Overflow

How to use a window function to refer to a newer record?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related