Retrieving date from next from to fill in column on row above

Question

Retrieving date from next from to fill in column on row above

icebird28

Valued Member

Points: 62
More actions
June 30, 2022 at 6:33 pm

#4057214

Hi all,
I have a very short query that contains an effective date but not expiration date. I would like to populate a expiration date set to 1 day before the next row's effective date. Here is query and results. For example, on part 100-121-0000, Revision A2; I'd like the Expiration Date to be 08-09-2009 based on one day before the Effective Date of Revision B, whose Effective Date is 08/10/2009. Hope that makes sense. Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

You will probably want to use LEAD windowing function. -- e.g.,

DATEADD(day,-1,LEAD(EffectiveDate ) OVER (PARTITION BY PartNum ORDER BY EffectiveDate))

-- Assumes RevisionNum is correctly determined in order based on EffectiveDate, so we can order only by EffectiveDate

pietlinden SSC Guru Points: 63503 More actions · Answer 2

you need LEAD() to get the next row. See here

You'd put the PartNum in the OVER clause and then ORDER BY EffectiveDate

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

If you'd like a tested coded answer, please read and heed the first link in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)