Search code examples
sqlfunctiont-sqlsumlag

Sum multiple columns with lag function SQL

I'm trying to sum 2 fields using SQL. My first field is in the current row (leadTime days), while second field is located on previous row (LeadTime Cumulative).

Expected outcome

What I've done so far to calculate the filed LeadTime Cumulative

CASE
WHEN [ClientNo] <> LAG([ClientNo], 1) OVER(ORDER BY [ClientNo], [Startdate])
THEN [LeadTime days]

WHEN [ClientNo] = LAG([ClientNo], 1) OVER(ORDER BY [ClientNo], [Startdate])
AND DATEDIFF(DD, [Enddate Previous], [Startdate]) > 10
THEN [LeadTime days]

WHEN [ClientNor] = LAG([ClientNo], 1) OVER(ORDER BY [ClientNo], [Startdate])
AND DATEDIFF(DD, [Enddate Previous], [Startdate]) < 10
THEN [LeadTime days] + LAG([LeadTime Cumulative], 1) OVER(ORDER BY [ClientNo], 
[Startdate])

ELSE [LeadTime days]
END

The thing it goes wrong, it does't ad up the field leadtime days + lag(leadtime cumulative, 1). It gives me null values:

Current output

Extra: If datediff start date en enddate previous record within same client > 10, you have to start counting over Any idea on how to fix this?

Solution

  • I think you want a cumulative sum:

    select t.*,
       sum(lead_time_days) over (partition by clientno
                                 order by startdate
                                ) as cumulative_lead_time_days
from t