Payment fully received

Question

We are raising bills to clients on various dates, and the payment is received in a irregular way. We need to calculate the payment delay days till full payments are received for a particular payment due. The data is sample data for only one client 0123

table Due (id, fil varchar(12), amount numeric(10, 2), date DATE)

table Received (id, fil varchar(12), amount numeric(10, 2), date DATE)

Table Due:

id     fil     amount        date
----------------------------------------
1.     0123   1000.         2019-jan-01
2.     0123   1500          2019-jan-15
3.     0123   1200.         2019-jan-25
4.     0123   1800.         2019-feb-10

Table Received:

id.    fil.    amount.       date
-----------------------------------------
1.     0123    1000.         2019-jan-10
2.     0123    500.          2019-jan-20
3.     0123    1300.         2019-jan-25
4.     0123     400.         2019-feb-08
5.     0123    1000.         2019-feb-20

The joined table should show:

fil.     due_date        due_amount.    received_amount date    delay
------------------------------------------------------------------------
0123    2019-jan-01     1000.          1000                     9
0123    2019-jan-15.    1500.          500
0123                                  1300.                    10(since payment completed on 25th jan)
0123    2019-jan-25.    1200.          400.
0123                                  1000.                    26
0123    2019-feb-10.    1800.                   

I have tried to be as accurate as possible in calculations......Please excuse if there is some in advertant error. I was just coming around to writing a script to do this, but maybe someone will be able to suggest a proper join.

Thanks for trying..

Answer 1

As @DavidHempy said, this is not possible without knowing for which invoice each payment is meant. You can calculate how many days it's been since the account was at 0, which might help:

with all_activity as (
  select due.date, 
       -1 * amount as amount 
  from due
  union all
  select received.date, 
       amount 
  from received), 
totals as (
 select date, 
        amount, 
        sum(amount) over (order by date), 
        case when sum(amount) over (order by date) >=0 
             then true 
             else false 
        end as nothing_owed
  from all_activity)
select date, 
       amount, 
       sum, 
       date - max(date) filter (where nothing_owed = true) OVER (order by date) 
       as days_since_positive
from totals order by 1
;
    date    |  amount  |   sum    | days_since_positive
------------+----------+----------+---------------------
 2019-01-01 | -1000.00 | -1000.00 |
 2019-01-10 |  1000.00 |     0.00 |                   0
 2019-01-15 | -1500.00 | -1500.00 |                   5
 2019-01-20 |   500.00 | -1000.00 |                  10
 2019-01-25 | -1200.00 |  -900.00 |                  15
 2019-01-25 |  1300.00 |  -900.00 |                  15
 2019-02-08 |   400.00 |  -500.00 |                  29
 2019-02-10 | -1800.00 | -2300.00 |                  31
 2019-02-20 |  1000.00 | -1300.00 |                  41
(9 rows)

You could extend this logic to figure out the last due date from which they were above 0.