# Calculating Days Past Due

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!

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.

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? Jan 26, 2017 at 18:41
• whoops! Because I forgot to change it in my `convert()` for formatting. I'll fix that. Jan 26, 2017 at 18:43
• I've come across another issues when there are negative amounts paid, see code link Jan 26, 2017 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? Jan 26, 2017 at 21:29
• This is sample data but it could in therory happen. Reversals of transactions, amounts paid could happen days or weeks after the initial collection attempt. Jan 26, 2017 at 21:37

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