Cumulative balance across two tables

I want to get data from 2 tables ordering them by date: to get the cumulative balance for the customer

The 2 tables that I want to get data from are my tables: transfers & trans_payments

transfers:

ID        int   
cust_id   int   
tfrom     nvarchar(200) 
tto       nvarchar(200)
price     decimal(18, 2)
tax       decimal(18, 2)
final     Calculated 
tnumber   nvarchar(30)
note      nvarchar(MAX)
date      date

trans_payments:

ID       int
cust_id  int
value    decimal(18, 2)
method   nvarchar(30)
note     nvarchar(MAX)
date     date

Let’s assume I have this data:

transfers:

ID    cust_id   final       date    
1      5        3000     22-09-2020  
2      5        1500     25-09-2020 
3      10       4000     28-09-2020

trans_payments:

ID    cust_id   value       date  
1      5        1000     22-09-2020
2      5        1500     23-09-2020
3      5        1000     01-10-2020
4      10       1000     28-09-2020
5      10       2000     01-10-2020

I want to create a view to show customers action on Purchases and Payments like this:

Customer 5:

cust_id   final    value    Balance    date      
5        3000        0       3000     22-09-2020 --- > Purchases 
5         0        1000      2000     22-09-2020 --- > payment
5         0        1500      500      23-09-2020 --- > payment
5        1500        0       2000     25-09-2020 --- > P
5         0        1000      1000     23-09-2020 --- > payment purchases  

Last row is the final balance for the customer

I tried this:

CREATE VIEW customer_account_summary as 
WITH Tb0 as
( SELECT ID,date,cust_id,final,0'value' from transfers
        UNION
        SELECT ID,date,cust_id,0'final',value from trans_payments
)

, Tb1 as
( 
    SELECT ID,date,cust_id,final,value,Row_Number() over (order by date asc) as [OrderId]
        FROM    
            Tb0
)

SELECT TOP 1000000 T1.ID,T1.cust_id,T1.date,T1.final,T1.value,(Sum(T2.final) - Sum(T2.value)) as Balance FROM Tb1 as T1
            INNER JOIN
                Tb1 as T2
                ON T1.[OrderId] <= T2.[OrderId]
                Group By T1.ID,T1.cust_id,T1.date,T1.final,T1.value
                Order by [date] 

But I have a problem that if 2 customers paid on the same date the payments will calculate for 2 customers that paid on the same date, I tried to add the ID for the 2 to prevent the query from grouping data But I’m still facing mistakes in the balance.

How can I do this in a proper way?

Answer

I think that’s union all and a window sum():

select cust_id, final, value, date,
    sum(balance) over(partition by cust_id order by date, seq) balance
from (
        select cust_id, final, 0, final balance, date, 0 from transfers
        union all
        select cust_id, 0, value, -value, date, 1 from trans_payments
) t (cust_id, final, value, balance, date, seq)
order by cust_id, date, seq

When there are rows in both tables for a given date, this puts the transfer(s) first.

Demo on DB Fiddle:

cust_id | final | value | date       | balance
------: | ----: | ----: | :--------- | ------:
      5 |  3000 |     0 | 2020-09-22 |    3000
      5 |     0 |  1000 | 2020-09-22 |    2000
      5 |     0 |  1500 | 2020-09-23 |     500
      5 |  1500 |     0 | 2020-09-25 |    2000
      5 |     0 |  1000 | 2020-10-01 |    1000
     10 |     0 |  2000 | 2020-01-01 |   -2000
     10 |  4000 |     0 | 2020-09-28 |    2000
     10 |     0 |  1000 | 2020-09-28 |    1000

You can use a where clause to filter on a given customer.