'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 enter image description here

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 enter image description here

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 enter image description here

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 enter image description here

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



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source