Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
(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}
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;
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;