Running total in two tables-CodePudding

Consider two tables: invoices and payments. The invoices table contains records of invoices raised, and the payments table contains records of payments received.

invoices

id |    date    | cname  | amount
---- ------------ -------- --------
1  | 2021-12-12 | cname1 |  10000
2  | 2021-12-13 | cname2 |   5000
3  | 2022-01-15 | cname1 |   7000
4  | 2022-01-16 | cname2 |   1000

payments

id |    date    | cname  | amount
---- ------------ -------- --------
1  | 2022-01-05 | cname1 |   5000
2  | 2022-01-07 | cname2 |   5000
3  | 2022-02-05 | cname1 |  10000
4  | 2022-02-06 | cname2 |   1000

CALCULATE RUNNING BALANCE

Q) Extend the SQL query to do invoice / payment matching as follows (as of 28/2/2022)

matching

date                | document_id | cname  | amount |  due
--------------------- ------------- -------- -------- -------
2021-12-12 00:00:00 |           1 | cname1 |  10000 | 10000
2022-01-05 00:00:00 |           1 | cname1 |  -5000 |  5000
2022-01-15 00:00:00 |           3 | cname1 |   7000 | 12000
2022-02-05 00:00:00 |           3 | cname1 | -10000 |  2000
2021-12-13 00:00:00 |           2 | cname2 |   5000 |  5000
2022-01-07 00:00:00 |           2 | cname2 |  -5000 |     0
2022-01-16 00:00:00 |           4 | cname2 |   1000 |  1000
2022-02-06 00:00:00 |           4 | cname2 |  -1000 |     0

CodePudding user response：

You can union both tables considering the second one with negative amount, and then a simple running total will produce the result you want. For example:

select
  date,
  id as document_id,
  cname,
  amount,
  sum(amount) over(partition by id order by date) as due  
from (
  select * from invoices
  union all select id, date cname, -amount from payments
) x
order by cname, date

CodePudding user response：

SELECT `date`,
documentId,
cname,amount,
due FROM (SELECT `date`,
documentId,
cname, 
amount,
(CASE WHEN @running_customer=''  THEN @running_balance:=amount
 WHEN @running_customer=cname THEN @running_balance:=@running_balance amount ELSE @running_balance:=amount END) due,
 @running_customer:=cname
 FROM (SELECT `date`, id AS documentId,cname, amount FROM `invoices`i
UNION ALL 
SELECT `date`, id AS documentId,cname, amount*-1 AS actionType FROM `payments` p) final
   JOIN (SELECT @running_customer:='') rc   
JOIN (SELECT @running_balance:=0) rb
ORDER BY cname, `date`) finalResult

You need to be using assignment operator for these kind of problems.