How to get end date based on previous row start date in SAP Datasphere?

[jelenakavcic]

Hi all,

I've tried with LAED and LAG sql function, but it didn't work:

Do you have any suggestion how to get previous value as results and how to calculate End Date, I would you like to get something like this:

For example, if Start Date is 01.01.2012 then to create End date based on previous row and minus one day.

Thank you in advance,

Jelena

[XaviPolo]

Hello,

LAG window function should work for you.

This works for me (SQL View with languge = table function)

return 
    select 
        "StartDate", 
        add_days( LAG("StartDate") OVER (ORDER BY "StartDate"), -1) AS "EndDate"
    from "DT_TRIPS"
    order by "StartDate"
;

Just check that StarDate has no nulls, or process them accordingly.Regards,

[JulianJuraske]

Was also my first approach

[berat_s]

Hi jelenakavcic,

Another possibility to the one from andreas.forster would be that you change the view to SQLScript and work with Table Functions. Here we have made good experience with Row_Number.

Table1 = select 
*,
Row_Number via Keys & order by Date ascending 
from Source;

Result = select 
....,
coalesce(add_days(to_date(start_date),-1),To_date('31/12/9999')) as End_date
from table as t1 Left outer Join Table1 as t2 on t1.Keys = t2.Keys and t1.Row_Number = (t2.Row_Number + 1);

Result = select ....,

coalesce(add_days(to_date(start_date),-1),To_date('31/12/9999')) as End_date

from table as t1 Left outer Join Table1 as t2 on t1.Keys = t2.Keys and t1.Row_Number = (t2.Row_Number + 1)
If it finds the next row, it would use its start date and set the previous day as the end date. If it is the current valid record and has no next row and therefore no end date, it will set this to 31/12/9999.

From the performance the solution proposed by me should be better, but of course I can not guarantee. It should also only serve as an idea. The try and the adjustments is up to you. :)

BR,

Berat

[AndreasForster]

Hi jelenakavcic , I had a similar requirement some time ago when calculating Net Promoter Scores in a Marketing context. A customer had multiple activities on different dates, each activity changes the NPS and an additional column was needed, that shows how long that NPS is valid for (so showing the date of the next activity in the same row).

A nested SELECT worked for me. Not sure if this fits your requirements, ideally I would try to avoid nested SELECTs to reduce workload and calculation time. You would probably still need to substract 1 day from the calculated column.

SELECT
A."NAME" AS THENAME,
A.NPS AS THENPS,
A."ACTIVITYDATE" AS ACTIVITYDATEFROM,
(SELECT MIN(ACTIVITYDATE) AS MAXDATE FROM TESTDATA B WHERE A.NAME = B.NAME AND A.ACTIVITYDATE < B."ACTIVITYDATE") AS ACTIVITYDATETO
FROM "TESTDATA" A
ORDER BY A.NAME, A."ACTIVITYDATE"