2

I have a data set similar to the one below. What I'm trying (and failing) to do is calculate the number of days past due each payment is on any given day between the first payment and today.

But here's the catch, each amount paid must be deducted from the earliest amount due first then anything left over will be deducted from the next amount due.

So in the example below the earliest payment overdue would be row 1 until this is fully paid, which is on the 26/08/16 then row 4 would become the earliest payment overdue. This is because the cumulative amounts paid up to an including the 26/08/16 would be £2656.15 which would cover amounts due on rows 1,2 and 3.

RowNumber   AccountNumber   date        AmountDue   AmountPaid
1           1000            05/11/2015  1422.5      0
2           1000            26/11/2015  474.17      0
3           1000            26/12/2015  474.17      0
4           1000            26/01/2016  474.17      0
5           1000            26/02/2016  474.17      400.31
6           1000            26/03/2016  474.17      474.17
7           1000            26/04/2016  474.17      0
8           1000            26/05/2016  474.17      0
9           1000            26/06/2016  474.17      474.17
10          1000            26/07/2016  474.17      0
11          1000            26/08/2016  474.17      1307.5
12          1000            26/09/2016  474.17      0
13          1000            26/10/2016  474.17      0
14          1000            26/11/2016  474.17      434.17
15          1000            26/12/2016  474.17      434.17
16          1000            26/01/2017  474.17      0

Any help would be very much appreciated!

1

The way I solved this was assembling these pieces:

  • Using a cte with running totals for AmountDue and AmountPaid

  • Using outer apply() to get the earliest paid date and final days overdue for each account using the running totals

DaysOverDue is either the final amount of days overdue as of the day it was completley paid off, or if not yet paid off, as of @Date which is set as current date in the query below.

PaidDate is the first date for an account where the RunningAmountPaid >= RunningAmountDue


update from comment based on additional issue not in the original question:

Added an outer apply() in the cte to check if the next row for an account had a negative value for AmountPaid and if so, added that to the RunningAmountPaid.

This cleared up the issue of an amount being marked as paid when the next transaction was a reversal of the previous payment.


rextester link: http://rextester.com/YTHG73937

test setup:

create table t (
    RowNumber int
  , AccountNumber int
  , [date] date
  , AmountDue decimal(9,2)
  , AmountPaid decimal(9,2)
  );

insert into t values 
 (1,1000,'2015-11-05',474.17,474.17)
,(2,1000,'2015-11-26',474.17,474.17)
,(3,1000,'2015-12-26',474.17,474.17)
,(4,1000,'2015-12-27',0,-474.17)
,(5,1000,'2016-01-26',474.17,0)
,(6,1000,'2016-02-26',474.17,0)
,(7,1000,'2016-03-26',474.17,474.17)
,(8,1000,'2016-04-26',474.17,0)
,(9,1000,'2016-05-26',474.17,0)
,(10,1000,'2016-06-26',474.17,474.17)
,(11,1000,'2016-07-26',474.17,0)
,(12,1000,'2016-08-26',474.17,1307.5)
,(13,1000,'2016-09-26',474.17,0)
,(14,1000,'2016-10-26',474.17,0)
,(15,1000,'2016-11-26',474.17,434.17)
,(16,1000,'2016-12-26',474.17,-434.17)
,(17,1000,'2017-01-26',474.17,0);

query:

declare @Date date = convert(date,getdate());

with cte as (
    select 
        *
      , RunningAmountDue = sum(AmountDue)  over (order by date)
      , RunningAmountPaid= sum(AmountPaid) over (order by date)
                         + isnull(x.Reversal,0)
      , AccountBalance   = sum(AmountDue)  over (order by date)
                         - sum(AmountPaid) over (order by date)
    from t 
      outer apply (
          select top 1 
              Reversal = i.AmountPaid
            from t as i
              where i.AccountNumber = t.AccountNumber
                and i.RowNumber = t.RowNumber+1
                and i.AmountPaid < 0
        ) as x
    )

    select 
        o.RowNumber
      , o.AccountNumber
      , Date                = convert(varchar(10),o.Date,120)
      , AmountDue           = convert(varchar(30),o.AmountDue,1)
      , AmountPaid          = convert(varchar(30),o.AmountPaid,1)
      , RunningAmountDue    = convert(varchar(30),o.RunningAmountDue,1)
      , RunningAmountPaid   = convert(varchar(30),o.RunningAmountPaid,1)
      , AccountBalance      = convert(varchar(30),o.AccountBalance,1)
      , PaidDate            = convert(varchar(10),x.PaidDate,120)
      , DaysOverDue         = datediff(day,o.Date,isnull(x.PaidDate,@Date))
    from cte o
      outer apply (
          select top 1
              PaidDate = i.date
            from cte i
            where i.AccountNumber=o.AccountNumber
              and i.RunningAmountPaid>=o.RunningAmountDue
            order by i.date asc
      ) as x;

results:

+-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+
| RowNumber | AccountNumber |    Date    | AmountDue | AmountPaid | RunningAmountDue | RunningAmountPaid | AccountBalance |  PaidDate  | DaysOverDue |
+-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+
|         1 |          1000 | 2015-11-05 | 474.17    | 474.17     | 474.17           | 474.17            | 0.00           | 2015-11-05 |           0 |
|         2 |          1000 | 2015-11-26 | 474.17    | 474.17     | 948.34           | 948.34            | 0.00           | 2015-11-26 |           0 |
|         3 |          1000 | 2015-12-26 | 474.17    | 474.17     | 1422.51          | 948.34            | 0.00           | 2016-03-26 |          91 |
|         4 |          1000 | 2015-12-27 | 0.00      | -474.17    | 1422.51          | 948.34            | 474.17         | 2016-03-26 |          90 |
|         5 |          1000 | 2016-01-26 | 474.17    | 0.00       | 1896.68          | 948.34            | 948.34         | 2016-06-26 |         152 |
|         6 |          1000 | 2016-02-26 | 474.17    | 0.00       | 2370.85          | 948.34            | 1422.51        | 2016-08-26 |         182 |
|         7 |          1000 | 2016-03-26 | 474.17    | 474.17     | 2845.02          | 1422.51           | 1422.51        | 2016-08-26 |         153 |
|         8 |          1000 | 2016-04-26 | 474.17    | 0.00       | 3319.19          | 1422.51           | 1896.68        | NULL       |         275 |
|         9 |          1000 | 2016-05-26 | 474.17    | 0.00       | 3793.36          | 1422.51           | 2370.85        | NULL       |         245 |
|        10 |          1000 | 2016-06-26 | 474.17    | 474.17     | 4267.53          | 1896.68           | 2370.85        | NULL       |         214 |
|        11 |          1000 | 2016-07-26 | 474.17    | 0.00       | 4741.70          | 1896.68           | 2845.02        | NULL       |         184 |
|        12 |          1000 | 2016-08-26 | 474.17    | 1307.50    | 5215.87          | 3204.18           | 2011.69        | NULL       |         153 |
|        13 |          1000 | 2016-09-26 | 474.17    | 0.00       | 5690.04          | 3204.18           | 2485.86        | NULL       |         122 |
|        14 |          1000 | 2016-10-26 | 474.17    | 0.00       | 6164.21          | 3204.18           | 2960.03        | NULL       |          92 |
|        15 |          1000 | 2016-11-26 | 474.17    | 434.17     | 6638.38          | 3204.18           | 3000.03        | NULL       |          61 |
|        16 |          1000 | 2016-12-26 | 474.17    | -434.17    | 7112.55          | 3204.18           | 3908.37        | NULL       |          31 |
|        17 |          1000 | 2017-01-26 | 474.17    | 0.00       | 7586.72          | 3204.18           | 4382.54        | NULL       |           0 |
+-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+
  • I think that's it, thank you so much. One question, why are the values in the RunningAmountDue and RunningAmountPaid exactly the same? – Data Monkey Jan 26 '17 at 18:41
  • whoops! Because I forgot to change it in my convert() for formatting. I'll fix that. – SqlZim Jan 26 '17 at 18:43
  • Ah yes, I can see now. Great stuff, thanks! – Data Monkey Jan 26 '17 at 18:46
  • I've come across another issues when there are negative amounts paid, see code link – Data Monkey Jan 26 '17 at 20:45
  • The next day 0 amount due, -amount paid I understand, what is going on with the account where the -amount paid is a month after? – SqlZim Jan 26 '17 at 21:29
0

I solved it as an account balance, but aligning first debt with first payment.

WITH Acc AS
(
    select Due.RowNumber, Due.AccountNumber, Due.[Date], Due.AmountDue, Paid.AmountPaid
    from account AS Due
    left join (select Row_Number() OVER (ORDER BY acc.[Date]) RowNumber,
                      acc.AccountNumber, acc.[Date], acc.AmountPaid
               from account acc
               where AmountPaid > 0) Paid
               ON Paid.RowNumber = Due.RowNumber
)
select Acc.AccountNumber, Acc.[Date], Acc.AmountDue, IsNull(Acc.AmountPaid,0) AmountPaid,
     SUM(Acc.AmountDue - Acc.AmountPaid) OVER(ORDER BY Acc.[Date] 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Balance
from Acc;

First calculates a new row number for all payments > 0, and join with debts by using the new row number. This aligns first debt with first payment.

select Row_Number() OVER (ORDER BY acc.[Date]) RowNumber,
       acc.AccountNumber, acc.[Date], acc.AmountPaid
from account acc
where AmountPaid > 0) Paid

And finally caculates cumulative sum of (AmountDue - AmountPaid) for every row.

This is the result:

+---------------+---------------------+-----------+------------+-----------+
| AccountNumber | Date                | AmountDue | AmountPaid | Balance   |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 05.11.2015 00:00:00 | 1422,5000 | 400,3100   | 1022,1900 |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.11.2015 00:00:00 | 474,1700  | 474,1700   | 1022,1900 |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.12.2015 00:00:00 | 474,1700  | 474,1700   | 1022,1900 |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.01.2016 00:00:00 | 474,1700  | 1307,5000  | 188,8600  |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.02.2016 00:00:00 | 474,1700  | 434,1700   | 228,8600  |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.03.2016 00:00:00 | 474,1700  | 434,1700   | 268,8600  |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.04.2016 00:00:00 | 474,1700  | 0,00       | 268,8600  |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.05.2016 00:00:00 | 474,1700  | 0,00       | 268,8600  |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.06.2016 00:00:00 | 474,1700  | 0,00       | 268,8600  |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.07.2016 00:00:00 | 474,1700  | 0,00       | 268,8600  |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.08.2016 00:00:00 | 474,1700  | 0,00       | 268,8600  |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.09.2016 00:00:00 | 474,1700  | 0,00       | 268,8600  |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.10.2016 00:00:00 | 474,1700  | 0,00       | 268,8600  |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.11.2016 00:00:00 | 474,1700  | 0,00       | 268,8600  |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.12.2016 00:00:00 | 474,1700  | 0,00       | 268,8600  |
+---------------+---------------------+-----------+------------+-----------+
| 1000          | 26.01.2017 00:00:00 | 474,1700  | 0,00       | 268,8600  |
+---------------+---------------------+-----------+------------+-----------+

Check it here: http://rextester.com/HUXR2415

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.