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 &gt;= 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 &lt; 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&gt;=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 |
+-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+