For years, I have used the humble spreadsheet as a way of keeping track of finances, but as this spreadsheet grew in size, and as I required more types of data analyses, it eventually became clear that a spreadsheet was no longer going to cut it. Problem is, going from Calc to Base has been a slow learning process in understanding SQL (HSQLDB, specifically), particularly its syntax.
Below is an example table with some arbitrary values. We'll call it Table A. There are a lot of other columns in my original table, but are either irrelevant, or I have already figured out what to do with them.
| Quantity | Account Paid From | Recipient A Percentage | Recipient B Percentage |
|---|---|---|---|
| 100 | A | 100 | 0 |
| 200 | B | 0 | 100 |
| 500 | A | 0 | 100 |
| 50 | B | 100 | 0 |
| 10 | A | 40 | 60 |
The idea here is that in row 1, Person A paid for something intended solely for person A. Therefore, this transaction does not result in anyone owing anything to another person. Same with row 2, where person B paid for something intended solely for person B
In row 3, person A paid $500 on behalf of person B. Person B now owes Person A $500.
In row 4, B Paid $50 for A. Subtract 50 from 500, and B now only owes A $450
In row 5, A paid for something that is 40% theirs, and 60% for B. In other words, A paid $6 out of the $10 for B. B now owes A 500 - 50 + 6 $456
I'm looking for something along the lines of the following:
- Select all entries where
Account Paid From= A - Of those entries, take the total sum of
Quantity*Recipient B Percentage/ 100 - The result is how much B owes A
- Select all entries where
Account Paid From= B - Of those entries, take the total sum of
Quantity*Recipient A Percentage/ 100 - The result is how much A owes B
- Subtract
A owes BfromB owes Ato find out who is in debt to the other (if the value is + or -), and by how much.
I guess something along the lines of:
SELECT SUM("Account Paid From" * "Recipient B Percentage / 100)
WHERE "Account Paid From" = "A" - SUM("Account Paid From" * "Recipient B Percentage / 100)
WHERE "Account Paid From" = "B" AS "Owed"
FROM "Table A"
But...you know, without syntax errors screaming at me.
