Running Total with a reset

[mdProgrammer]

I have a query that I'm working on which has the requirement that it calculates a netloss carryover (it's an accounting app, so accounting algorithms aren't my strong suit). The current netloss carryover field should add the income and netloss from the previous month. I found a running total example online using OVER PARTITION. This works fine for a running total, but there's a twist that I'm trying to figure out how to do - (Note - I'm using SQL Server 2016)

1 - When the "Net Loss Carryover" is >= 0, then show 0 and it resets the total.
2 - Sometimes, another condition may be met that resets the value to 0 (not included in the query, but let's assume it's some field flag for that particular row. I've seen reset code for this elsewhere).

Below, I have the output, and what I would like it to show.

[pre]
CREATE TABLE #Purchases
(
GroupID int,
TransactionDate date,
Price int
)
INSERT INTO #Purchases VALUES (1,'1/1/2017',80)
INSERT INTO #Purchases VALUES (1,'2/1/2017',20)
INSERT INTO #Purchases VALUES (1,'3/1/2017',-50)
INSERT INTO #Purchases VALUES (1,'4/1/2017',30)
INSERT INTO #Purchases VALUES (1,'5/1/2017',40)
INSERT INTO #Purchases VALUES (1,'6/1/2017',-100)
INSERT INTO #Purchases VALUES (1,'7/1/2017',-80)
INSERT INTO #Purchases VALUES (1,'8/1/2017',-20)
INSERT INTO #Purchases VALUES (1,'9/1/2017',10)
INSERT INTO #Purchases VALUES (1,'10/1/2017',25)
INSERT INTO #Purchases VALUES (1,'11/1/2017',15)
INSERT INTO #Purchases VALUES (1,'12/1/2017',50)
INSERT INTO #Purchases VALUES (1,'1/1/2018',30)

SELECT
GroupID,
TransactionDate,
Price,
SUM(Price) OVER (PARTITION BY GroupID ORDER BY TransactionDate) AS RunningTotal
FROM
#Purchases


DROP TABLE #Purchases
[/pre]


[pre]
GroupID TransactionDate Income RunningTotal Should Show (for Net Loss Carryover)
1 1/1/2017 80 80 0
1 2/1/2017 20 100 0
1 3/1/2017 -50 50 0
1 4/1/2017 30 80 -50
1 5/1/2017 40 120 -20
1 6/1/2017 -100 20 0
1 7/1/2017 -80 -60 -100
1 8/1/2017 -20 -80 -180
1 9/1/2017 65 -70 -200
1 10/1/2017 25 -45 -135
1 11/1/2017 30 -30 -110
1 12/1/2017 80 20 -80
1 1/1/2018 130 50 0

[/pre]

 

[mdProgrammer]

Ok, I found something close, but it's for Oracle. So, just for reference -

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9531712000346124348

Seems to be almost what I want. For example, row 2 should say 0 for the total, and row 3 should say -300, so offset by the previous month (since my goal is to show the net loss carryover from the previous month).

[pre]
DECLARE @Threshold integer = 50
create table #t
(
Flight_Date date,
Destination varchar(6),
DateDiffMin int
)

--alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
insert into #t values ('1/1/2017', 'Berlin', 0);
insert into #t values ('2/1/2017', 'Berlin', -300);
insert into #t values ('3/1/2017', 'Berlin', 30);
insert into #t values ('4/1/2017', 'Berlin', 10);
insert into #t values ('5/1/2017', 'Berlin', 50);
insert into #t values ('6/1/2017', 'Berlin', 20);
insert into #t values ('7/1/2017', 'Berlin', 60);
insert into #t values ('8/1/2017', 'Berlin', 60);
insert into #t values ('9/1/2017', 'Berlin', 5);
insert into #t values ('10/1/2017', 'Berlin', 55);
insert into #t values ('1/1/2017', 'Rome', 0);
insert into #t values ('2/1/2017', 'Rome', 90);
insert into #t values ('3/1/2017', 'Rome', 30);
insert into #t values ('4/1/2017', 'Rome', 1320);

with rws as (
select #t.*,
row_number() over (partition by destination order by Destination, flight_date) rn
from #t
), tots (rn, flight_date, dest, DateDiffMin, tot) as (
select rn, flight_date, destination, DateDiffMin, 0 from rws
where rn = 1
union all
select r.rn, r.flight_date, r.destination, r.DateDiffMin,
case when t.tot + r.DateDiffMin >= @Threshold then
0 else t.tot + r.DateDiffMin
end
from tots t
join rws r
on r.rn = t.rn + 1
and t.dest = r.destination
)
select * from tots
order by dest, flight_date;

/*
Data Output
rn flight_date dest DateDiffMin tot
1 2017-01-01 Berlin 0 0
2 2017-02-01 Berlin -300 -300
3 2017-03-01 Berlin 30 -270
4 2017-04-01 Berlin 10 -260
5 2017-05-01 Berlin 50 -210
6 2017-06-01 Berlin 20 -190
7 2017-07-01 Berlin 60 -130
8 2017-08-01 Berlin 60 -70
9 2017-09-01 Berlin 5 -65
10 2017-10-01 Berlin 55 -10
1 2017-01-01 Rome 0 0
2 2017-02-01 Rome 90 0
3 2017-03-01 Rome 30 30
4 2017-04-01 Rome 1320 0
*/

DROP TABLE #t
[/pre]