hsqldblibreoffice-base

Tracking finances owed using a database

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:

  1. Select all entries where Account Paid From = A
  2. Of those entries, take the total sum of Quantity * Recipient B Percentage / 100
  3. The result is how much B owes A
  4. Select all entries where Account Paid From = B
  5. Of those entries, take the total sum of Quantity * Recipient A Percentage / 100
  6. The result is how much A owes B
  7. Subtract A owes B from B owes A to 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.

Solution

  • For simplicity, I will use table name "T" and the following abbreviated column names:

    "Quantity" "Q", "Account Paid From" "APF", "Recipient A Percentage" "RAP", "Recipient B Percentage" "RBP"

    1. SELECT * FROM T WHERE "APF" = 'A'

    2. SELECT SUM("Q" * "RBP" / 100) AS "Sum RBP" FROM "T" WHERE "APF" = 'A'

    5. SELECT SUM("Q" * "RAP" / 100) AS "Sum RAP" FROM "T" WHERE "APF" = 'B'

    7. SELECT "Sum RBP" - "Sum RAP" FROM (SELECT SUM("Q" * "RBP" / 100) AS "Sum RBP" FROM "T" WHERE "APF" = 'A'), (SELECT SUM("Q" * "RAP" / 100) AS "Sum RAP" FROM "T" WHERE "APF" = 'B')