Window Functions - Running Total with Reset

Window Functions - Running Total with reset

This can be done using a set-based solution:

1.Compute the normal running total (call it RT)

2.Compute the running minimum of RT (call it MN)

When MN is negative, -MN is the total quantity you had to replenish so far. Let replenish_rt be -MN when MN is negative. So, the new running total (call it new_rt) is rt + replenish_rt. And if you need to return the current replenish quantity needed, subtract the pervious replenish_rt (using LAG) from the current.

Here's the complete solution query:

with c1 as
select *,
sum(qty) over(order by tdate rows unbounded preceding) as rt
from tx
c2 as
select *,
-- when negative, mn is the total qty that had to be
-- replenished until now, inclusive
min(rt) over(order by tdate rows unbounded preceding) as mn_cur
from c1
select tdate, qty, rt,
replenish_rt - lag(replenish_rt, 1, 0) over(order by tdate) as replenish,
rt + replenish_rt as new_rt
from c2
cross apply(values(case when mn_cur < 0 then -mn_cur else 0 end)) as a1(replenish_rt);

Running total with monthly reset

I would strongly recommend doing this using window functions -- for performance reasons:

select id, somedate, somevalue, run_tot, m_run_tot,
coalesce(max(prev_m_run_tot) over (partition by year(somedate), month(somedate)),
first_value(m_run_tot) over (order by somedate)
) as required_output
from (select id, somedate, somevalue,
sum(somevalue) over (order by somedate) as run_tot ,
sum(somevalue) over (partition by year(somedate), month(somedate) order by somedate) as m_run_tot,
(case when row_number() over (partition by year(somedate), month(somedate) order by somedate) = 1
then sum(somevalue) over (order by somedate rows between unbounded preceding and 1 preceding)
end) as prev_m_run_tot
from TestTable
) t;

Here is a db<>fiddle.

The logic is to calculate the running total on the first date in each month and then spread that through the month.

Note that this also fixes the partition bys so they include the year as well as the month.

You could actually do this without subqueries by subtracting two cumulative sums:

select id, somedate, somevalue,
sum(somevalue) over (order by somedate) as run_tot ,
sum(somevalue) over (partition by year(somedate), month(somedate) order by somedate) as m_run_tot,
(case when rank() over (order by year(somedate), month(somedate)) = 1
then first_value(somevalue) over (order by somedate)
else sum(somevalue) over (order by somedate) - sum(somevalue) over (partition by year(somedate), month(somedate) order by somedate)
end) as required_output
from TestTable;

Here is the db<>fiddle for this version.

Window function not giving running total

To get a running cumulative total, either RANGE or ROWS window must be specified or implied in the OVER, and therefore you also need to add an ORDER BY. Otherwise the window is the full partition.

Once you specify ORDER BY, the default implied window is RANGE UNBOUNDED PRECEDING, which gives different results for non-unique orderings from ROWS.

Given that date is probably not unique, you also want ROWS UNBOUNDED PRECEDING.

SUM(CAST(v.new_vaccinations AS int))
CovidDeaths d
CovidVaccinations v
ON =
d.location = v.location
d.continent IS NOT NULL

reset a running total in same partition based on a condition

Do a running total on ResetSum in a derived table and use that as a partition column in the running total on Amount.

select T.PersonID,
sum(T.Amount) over(partition by T.PersonID, T.ResetSum
order by T.PayDate rows unbounded preceding) as SumAmount
from (
select T1.PersonID,
sum(case T1.ResetSum
when 1 then 1
else 0
end) over(partition by T1.PersonID
order by T1.PayDate rows unbounded preceding) as ResetSum
from dbo.Table_1 as T1
) as T;

SQL Fiddle

SQL Server, running total, reset for each month and sum again

You can try to use PARTITION by with EOMONTH function which might get the same result but better performance, then you might only need to order by Date instead of using the function with the date.

sum(WDAYS.WorkingDay) OVER(PARTITION by EOMONTH(WDAYS.Date) order by Date) as 'current_working_day',
sum(WDAYS.WorkingDay) OVER(PARTITION by EOMONTH(WDAYS.Date) ) total_working_days_per_month
from WDAYS
where YEAR(WDAYS.Date) = 2022

SQL Server 2012 - Reset Running Total Based on the Value of Another Column

With a problem like this, you have to get really meta about the running result (the running result for a row depends not on the previous value but rather on the previous running result). I can't think of a way to do this inside a CTE when SQL Server handles window functions in a way that differs and is less intuitive compared to other implementations such as PostgreSql. It may even be considered buggy if there is a standard that it is violating.

The strategy I give below uses recursion with a while loop. But it's not RBAR so I don't think performance will be horrible compared to what you might fear with such a loop. In fact, I structured it to be analogous to the syntax of a recursive CTE, if windowed functions worked inside the recursive part in SQL Server. Incidentally, I actually implemented it as a recursive CTE in postgreSQL with success.

One thing to keep in mind for the loop is that I make your 'flag' a little more complex. I call my flag 'processed', and it can take on three values: 1, -1, and 0. 0 means 'not processed', 1 means 'processed', and -1 is also processed but a special marker that pretty much serves as your 'flag'.

-- The Anchor Part
select *,
result = 0,
processed = 0
into #results
from Table_1

-- The Recursive Part
while exists (select 0 from #results where processed = 0)

update r
set r.result = r.runSum,
r.processed =
when r.runSum <= r.min_lot_size then 1
-- first entry that is greater than min_lot_size
when r.runSum - quantity < r.min_lot_size then -1
else 0
from (
select *,
runSum = sum(quantity) over(
partition by item_id
order by due_date
from #results
where processed = 0
) r;

-- Final Output
select ITEM_ID,
NEW_QUANTITY = iif(processed = -1, result, 0)
from #results;
  • Here it is with runnable results in SQL Server: sql server.
  • Here it is as a true recursive CTE as I discuss above: postgre.

Google Sheets - Running Total with reset


ROW(B:B), "<="&ROW(B:B)), ROWS(A:A)-1, 1)}=TRANSPOSE(
ROW(B:B), "<="&ROW(B:B)), ROWS(A:A)-1, 1)})),A:A, 0)), ROW(A:A)^0)))

Sample Image

Reset my running total on specfic column value in sqlite

With window functions MAX() and SUM():

select RowNum, ProductName, InOutType, quantity,
sum(case when InOutType = 2 then -1 else 1 end * quantity)
over (partition by ProductName, start order by RowNum) runningTotal
from (
select *,
max(case when InoutType = 3 then RowNum end) over (partition by ProductName order by RowNum) start
from tablename
order by RowNum

See the demo.


| RowNum | ProductName | InOutType | quantity | runningTotal |
| ------ | ----------- | --------- | -------- | ------------ |
| 1 | Apple | 1 | 10 | 10 |
| 2 | Orange | 1 | 20 | 20 |
| 3 | Apple | 2 | 5 | 5 |
| 4 | Apple | 1 | 2 | 7 |
| 5 | Orange | 2 | 8 | 12 |
| 6 | Orange | 3 | 5 | 5 |
| 7 | Apple | 3 | 2 | 2 |
| 8 | Orange | 1 | 20 | 25 |
| 9 | Apple | 1 | 10 | 12 |
| 10 | Apple | 2 | 2 | 10 |
| 11 | Orange | 2 | 8 | 17 |

Related Topics

Leave a reply