How to get balance according date and ID for every customer_id and currency

I have a table named as transaction_table:

CREATE TABLE transaction_table
  (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT,
    debit REAL,
    credit REAL,
    curr_id INTEGER,
    cus_id INTEGER,
    FOREIGN KEY (curr_id) REFERENCES currencies(_id) ON DELETE CASCADE,
    FOREIGN KEY (cus_id) REFERENCES customers(_id) ON DELETE CASCADE
  )

And assume this data in it:

_id  date                       debit    credit   curr_id    cus_id
-------------------------------------------------------------------
1   2022-12-08T00:00:00.000     10.0       0.0         1         1
2   2022-12-07T00:00:00.000      0.0      20.0         1         1
3   2022-12-06T00:00:00.000      0.0      30.0         1         1
4   2022-12-07T00:00:00.000     40.0       0.0         1         1
5   2022-12-08T00:00:00.000    100.0       0.0         1         1

So I just make SELECT statement that will get balance according date then _id but the balance is wrong:

SELECT  t1._id,
    t1.date ,
    t1.description ,
    t1.debit ,
    t1.credit,
    SUM(t2.debit - t2.credit) as blnc,
    t1.curr_id,
    t1.cus_id
FROM transaction_table t1 INNER JOIN transaction_table t2
ON t2.curr_id = t1.curr_id AND t2.cus_id = t1.cus_id AND t2._id <= t1._id AND t2.date <= t1.date
GROUP BY t1._id
ORDER BY t1.date DESC, t1._id DESC;

the result:

 _id    date                        debit   credit   balance   curr_id   cus_id
  -----------------------------------------------------------------------------
   5    2022-12-08T00:00:00.000     100.0      0.0     100.0         1        1
   1    2022-12-08T00:00:00.000      10.0      0.0      10.0         1        1
   4    2022-12-07T00:00:00.000      40.0      0.0     -10.0         1        1
   2    2022-12-07T00:00:00.000       0.0     20.0     -20.0         1        1
   3    2022-12-06T00:00:00.000       0.0     30.0     -30.0         1        1

the result is wrong and it should be like this:

 _id    date                        debit   credit   balance   curr_id   cus_id
  -----------------------------------------------------------------------------
   5    2022-12-08T00:00:00.000     100.0      0.0     100.0         1        1
   1    2022-12-08T00:00:00.000      10.0      0.0       0.0         1        1
   4    2022-12-07T00:00:00.000      40.0      0.0     -10.0         1        1
   2    2022-12-07T00:00:00.000       0.0     20.0     -50.0         1        1
   3    2022-12-06T00:00:00.000       0.0     30.0     -30.0         1        1

which I can achieve it by this code:

SELECT _id,date, description, debit, credit,
   SUM(debit - credit) OVER (PARTITION BY curr_id, cus_id ORDER BY date, _id) blnc,
   curr_id, cus_id 
FROM transaction_table
order by date desc, _id desc;

but I don't need it cause its not working in old android. I just need to use INNER JOIN.

CodePudding user response：

The conditions about the dates and the ids in the ON clause need correction:

SELECT t1._id,
       t1.date,
       t1.description,
       t1.debit,
       t1.credit,
       SUM(t2.debit - t2.credit) AS blnc,
       t1.curr_id,
       t1.cus_id
FROM transaction_table t1 INNER JOIN transaction_table t2
ON t2.curr_id = t1.curr_id AND t2.cus_id = t1.cus_id 
AND (t2.date < t1.date OR (t2.date = t1.date AND t2._id <= t1._id))
GROUP BY t1._id
ORDER BY t1.date DESC, t1._id DESC;

See the demo.

CodePudding user response：

You may substitute the SUM() analytic function call with a correlated subquery:

SELECT _id, date, description, debit, credit,
       (SELECT SUM(t2.debit - t2.credit)
        FROM transaction_table t2
        WHERE t2.curr_id = t1.curr_id AND
              t2.cus_id = t1.cus_id AND
              (t2.date <= t1.date OR
               t2.date = t1.date AND t2._id <= t1._id)) AS blnc,
       curr_id, cus_id 
FROM transaction_table t1
ORDER BY date DESC, _id DESC;