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

Question

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?

Answer 1

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.