New to SQL and been doing beginner queries successfully. I am now trying to apply it to real life situations but am stuck on this particular problem:
I have a database for our family budget where myself and my wife have several accounts for different uses i.e. food, school expenses etc. From this, I am trying to extract what our balances for them at any given time (while it is easiest to just log in to our bank accounts, I wanted to see how I can do this in SQL as a learning exercise).
Our database looks like this:
| Buyer | Date of Transaction | Bank Account | Transaction Amount | Bank Account Balance After Transaction |
|---|---|---|---|---|
| Mum | 05/06/24 | 1 | $3 | $32 |
| Dad | 05/06/24 | 1 | $2 | $30 |
| Dad | 05/06/24 | 2 | $10 | $10 |
| Mum | 03/06/24 | 1 | $5 | $35 |
| Mum | 02/06/24 | 1 | $10 | $40 |
This would mean that before 2nd of June 2024, we started off with $50 in our Bank Account 1. Bought $10 worth from it, and ended with a balance of $40 after that transaction. Then after more transactions, we end up with a balance of $30 on 5th of June 2024.
Unfortunately, the dates do not have timestamps and any transaction on that date will not necessarily be in order of occurrence in the database (as above, considering the balance of Bank Account 1 the $2 transaction should be the last one to have occurred as it is the one with the lowest balance)
This is what I want the results to look like:
| Bank Account | Current Balance |
|---|---|
| 1 | $30 |
| 2 | $10 |
To me it seems like I need to take the MAX Date and find the MIN value that occurred that date (I have not taken in account deposits yet, but that is a problem I will tackle later). However, in trying this I can’t seem to group them together and get a bigger list than 2 results. I also then get errors when I play around with MIN and MAX’s.
Any assistance for a noob in learning is appreciated!