﻿ Window Functions - Running Total with Reset - ITCodar

# 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_rtfrom c2  cross apply(values(case when mn_cur < 0 then -mn_cur else 0 end)) as a1(replenish_rt);`
Cheers,
Itzik

## 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_outputfrom (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 by`s 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_outputfrom 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`.

``SELECT d.date, d.location, d.population, v.new_vaccinations, SUM(CAST(v.new_vaccinations AS int))    OVER (PARTITION BY d.location ORDER BY d.date ROWS UNBOUNDED PRECEDING) AS total_vaccinationsFROM CovidDeaths dJOIN CovidVaccinations vON d.date = v.date AND d.location = v.locationWHERE   d.continent IS NOT NULLORDER BY  d.location, d.date``

## 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,       T.Amount,       T.PayDate,       sum(T.Amount) over(partition by T.PersonID, T.ResetSum                           order by T.PayDate rows unbounded preceding) as SumAmountfrom (     select T1.PersonID,            T1.Amount,            T1.PayDate,            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.

``select  WDAYS.Date,  WDAYS.DayName,  WDAYS.WorkingDay,  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_monthfrom WDAYSwhere 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 Partselect  *,         result = 0,        processed = 0into    #resultsfrom    Table_1-- The Recursive Partwhile exists (select 0 from #results where processed = 0)    update  r    set     r.result = r.runSum,            r.processed =                 case                 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                end    from    (                select  *,                        runSum = sum(quantity) over(                            partition by item_id                             order by due_date                        )                from    #results                where   processed = 0            ) r;-- Final Outputselect  ITEM_ID,         DUE_DATE,         MIN_LOT_SIZE,         QUANTITY,         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

try:

``=INDEX(IF(A:A="",,MMULT(--TRANSPOSE(IF((TRANSPOSE(ROW(A:A))>=ROW(A:A))*( {0; ARRAY_CONSTRAIN(COUNTIFS(B:B, "TRADE PAIRED",  ROW(B:B), "<="&ROW(B:B)), ROWS(A:A)-1, 1)}=TRANSPOSE( {0; ARRAY_CONSTRAIN(COUNTIFS(B:B, "TRADE PAIRED",  ROW(B:B), "<="&ROW(B:B)), ROWS(A:A)-1, 1)})),A:A, 0)), ROW(A:A)^0)))`` ## 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.

Results:

``| 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           |``