'Cumulative DateDiff with dependencies
I'm trying to calculate a cumulative datediff field, but with some dependencies. I want to have a cumulative datediff for each row in my table for each client, but when the DateDiff Previous ID > 31, i have to start at 0. My table is set up in the following way

I've tried a couple of things myself, but didn't give me the expected result. Lag function datediff previous row
WHEN [EndDate Previous] is null THEN [DateDiff Current]
WHEN [DateDiff Previous ID] < 32 THEN [DateDiff Current] + LAG([DateDiff Current]) OVER(PARTITION BY ClientID ORDER BY [Startdate]
ELSE [DateDiff Current]
END
The result, it only sums the current and previous DateDiff

Lag Function with DateDiff current + Lag(Running DateDiff)
WHEN [EndDate Previous] is null THEN [DateDiff Current]
WHEN [DateDiff Previous ID] < 32 THEN [DateDiff Current] + LAG([Running DateDiff]) OVER(PARTITION BY ClientID ORDER BY [Startdate]
ELSE [DateDiff Current]
END
Unfortunately it gives back null values

Last thing I tried was with a Sum function
WHEN [EndDate Previous] is null THEN [DateDiff Current]
WHEN [DateDiff Previous ID] < 32 THEN SUM([DateDiff Current]) OVER(PARTITION BY ClientID ORDER BY [Startdate]
ELSE [DateDiff Current]
END
The result I get is a totale SUM for this client

The final result I want shown below, but just can't get the right query for this, you guys got any ideas

Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
