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