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.
Depositis created with columns
Withdrawalis created with columns
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-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?