## 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:

Cheers,

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);

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_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 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_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.**

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

FROM

CovidDeaths d

JOIN

CovidVaccinations v

ON

d.date = v.date

AND

d.location = v.location

WHERE

d.continent IS NOT NULL

ORDER 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 SumAmount

from (

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_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 =

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 Output

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

### Related Topics

Django Orm - Get Latest Record for Group

Example of Three Valued Logic in SQL Server

Access SQL Using Top 5 Returning More Than 5 Results

SQL Server 2005, Turn Columns into Rows

SQL Server Inserting Date as 1/1/1900

SQL to Transpose Row Pairs to Columns in Ms Access Database

Select Distinct Values from 1 Column

SQL Select Elements Where Sum of Field Is Less Than N

Postgresql: Top N Entries Per Item in Same Table

How to Prompt for Input from an Ssis Package

Db2- How to Check If Varchar Field Value Has Integers