# Calculate balance for every row by sequentially subtracting each row's amount from an initial sum

I'll explain the issue with an example.

A query that will select and show the Withdrawal Process I made and show the status of my deposit balance.

A table `Deposit` is created with columns `TotalAmount`, `DepositDate`.

Another table `Withdrawal` is created with columns `WithdrawAmount`, `WithdrawDate`.

So I use a SELECT query to select from both tables with formulae:

``````SELECT WithdrawAmount,
CASE WHEN ( TotalAmount - WithdrawAmount) = 0 THEN 'ZeroBalanceOops'
ELSE 'StillAvailableYAY' as 'Status'
FROM Deposit Inner Join Withdraw WHERE [WithdrawDate] between this month beginning and ending
``````

So for this query, let's assume I have a deposit of 500 and will only deposit once. It works well if I only withdraw once this month and withdraw the deposit completely. The result will be showing like this:

``````| WithdrawAmount | Status          |
| 500            | ZeroBalanceOops |
``````

However, it doesn't work if I withdraw more than once in a month and these withdrawals would make the balance = 0. Assuming the deposit is 500 again, the result would show like this :

``````| WithdrawAmount  | Status            |
| 250             | StillAvailableYAY |
| 250             | StillAvailableYAY |
``````

The expected result is to have 'StillAvailableYAY' at first withdrawal then 'ZeroBalanceOops' at second withdrawal status but as it only compares `500-250` not `500-250-250` thus it will be always the 'StillAvailableYAY' status. Using `SUM(WithdrawalAmount)` does not give the result I want as it would cause the two withdrawals to have status 'ZeroBalanceOops'.

Any idea on how can I retrieve the previously selected query and include it into calculation? Or is there any better way to do so?

This is a "running total" type of problem: each row's total is calculated based on that row's value added to, or subtracted from, the previous row's total.

Since you are using SQL Server 2014, Transact-SQL has built-in syntax available to you to help you with getting the results.

Using the simplified model of "One deposit per month, many withdrawals per month", the SQL statement could go like this:

``````SELECT
d.TotalAmount,
w.WithdrawAmount,
Balance = d.TotalAmount - SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC)
FROM
dbo.Deposit AS d,
dbo.Withdrawal AS w
WHERE
d.DepositDate  >= start_of_this_month AND d.DepositDate  < start_of_next_month
AND
w.WithdrawDate >= start_of_this_month AND w.WithdrawDate < start_of_next_month
;``````

The WHERE clause is supposed to filter the tables down to one deposit row from `Deposit` and the however many corresponding withdrawals from `Withdrawal`. If the tables support multiple accounts and the amounts need to be further related by account, you may want to replace the FROM clause with something like this:

``````FROM
dbo.Deposit AS d
INNER JOIN dbo.Withdrawal AS w ON d.AccountNumber = w.AccountNumber
``````

The `SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC)` expression calculates a running `WithdrawAmount` total for each row. So, the total increases with each row (sorted in the ascending order of `WithdrawDate`) – thus with each row an ever increasing amount is subtracted from `TotalAmount`, drawing `Balance` nearer to 0.

The above query will give you each row's balance but not the status. To get the status, you will need to reference the `Balance` value to compare it 0 and select a corresponding status string to return. `Balance` is a calculated column and in order to be able to reference it you need to nest the above query and reference `Balance` at the outer level. Nesting could be done with either a derived table or a common table expression (CTE). This query uses a CTE:

``````WITH balances AS
(
SELECT
d.TotalAmount,
w.WithdrawAmount,
Balance = d.TotalAmount - SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC)
FROM
dbo.Deposit    AS d,
dbo.Withdrawal AS w
WHERE
d.DepositDate  >= start_of_this_month AND d.DepositDate  < start_of_next_month
AND
w.WithdrawDate >= start_of_this_month AND w.WithdrawDate < start_of_next_month
)
SELECT
TotalAmount,
WithdrawAmount,
Balance,
Status = CASE WHEN Balance > 0 THEN 'StillAvailableYAY' ELSE 'ZeroBalanceOops' END
FROM
balances
;``````

Of course, if you do not really need to return the balance – only to compare it to 0, then nesting is not needed and you can put the `d.TotalAmount - SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC)` expression directly into the CASE, replacing `Balance`.

• what if I have other table like BillPayment with associate table BillPayment_Knockoff_Deposit, Transfer with Transfer_Knockoff_Deposit. Then I need to perform select at all these tables and UNION all of them. How do I get the balance across the different select table i made? Aug 24, 2016 at 1:33
• I have edited to add in the question i mentioned in the EXTRA. Aug 24, 2016 at 2:28
• That's an interesting problem as well but it's different from the one you asked about originally. You essentially changed the question. It would probably be better to rollback your last change and submit a new question. If/when you do, you can include a link to this question to make it clear that the two are related (and not duplicates of each other). Aug 24, 2016 at 9:53
• I have solved the question and yeah you are right its different from this question as it took two totally different approach to handle this and the extra question I mentioned. I have edited out the extra question. Thanks a lot for your help. Definitely helped me a lot! Aug 25, 2016 at 1:14