Fundamentally, this is a running total problem: for each row you are getting the running total of qty and subtracting a fixed value from it. More specifically, this is about how you want to use the running total. In this case you want to display a new qty value based on the result of the previous calculation. And there are essentially three cases here:
- The result of the subtraction is negative.
- The result of the subtraction exceeds the current row's qty.
- The result is a non-negative value not exceeding qty.
The first case means that the supplied fixed value is more than necessary to exhaust the current running total – in other words, it exhausts all qty values from the beginning till the current row (including the current row). Therefore, we substitute a 0 for qty.
In the second case we just keep the qty, because the running total now exceeds the fixed value so much that the difference is greater than the current qty – so the latter stays intact.
The last case is when the fixed value either exactly covers all the qty values so far or is slightly less than the total but not much less: up to the amount of the current qty. In this case we show the difference between the running total and the fixed value.
The SQL for the above is this:
WHEN balance < 0 THEN 0
WHEN balance > qty THEN qty
END AS qty
SUM(qty) OVER (ORDER BY id ASC) - @fixedSum AS balance
) AS derived
where @fixedSum is the fixed value you want to subtract from the running total.
The central part of the solution is
SUM(qty) OVER (ORDER BY id ASC) – the window aggregate function to get the running total of qty.
I am calculating the running totals here in a nested SELECT because the result of the subtraction needs to be referenced several times in a CASE expression, and you cannot reference a calculated column on the same level, you need to use nesting for that. The nesting can be in the form of either a derived table or a common table expression (CTE). The above solution uses a derived table.
There is a way to avoid nesting, though, and do all the calculations in a single SELECT using the functions LEAST and GREATEST:
SUM(qty) OVER (ORDER BY id ASC) - @fixedSum,
) AS balance
This method is much less straightforward than the one using CASE, though. Performance-wise, it would likely work the same.
The method works like this. First, you choose the greater value between the subtraction result and 0 using the GREATEST function. So, in terms of the cases listed above, if we have Case 1, GREATEST will yield 0. A non-zero result would mean we have one of the other cases.
The obtained value is then compared to the current qty and this time the lesser one of the two is chosen using the LEAST function. If we obtained 0 previously, LEAST will keep it. If we had the subtraction result and it is now found to be greater than qty, the LEAST function will give us qty (meaning this is Case 3). And if the difference happens to be less than qty, that is what LEAST will yield (Case 2).