Trouble in calculating End_dt based on Start_dt using peek() ..

[#1]

Hi all,

i need to calculate the end date based on the start date of next record and for last record end date should be today's date .

(the records should be ordered by start date)

I am facing an issue with following data

using peek function, the result is:

id        start_dt         end_dt

19396 1979-07-01    1979-06-30

19397 1979-07-01    1979-05-31

19398 1980-09-01    1980-08-31

And expected output like below:

id        start_dt         end_dt

19396 1979-07-01    1979-06-30

19397 1979-07-01    1980-08-31

19398 1980-09-01    end date based on start date of next record

{PFA}

[Gysbert_Wassenaar · Accepted solution]

Try ordering by start date in descending order:

[Carrier Decode]:

LOAD

     ....

     [Start Date],

     Date(alt(Peek('Start Date')-0.00000001,Today()),'YYYY-MM-DD') as [End Date]

Resident [Temp_Carrier Decode]

Order by [Start Date] desc;