How to Calculate Days Past Due in SQL Server

sql serversql server 2014sql-server-2008-r2sql-server-2012

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!

Best Answer

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 |
+-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+

Related Solutions

Sql-server – SELECT every nth value by Date from large table

Technically to display n-th value you use the ranking functions like RANK, DENSE_RANK or ROW_NUMBER. Which one exactly depends, but what you describe matches best ROW_NUMBER:

with cte as (
 select row_number() over (order by Date) as rn, *
 from table)
where rn % 1000 = 0;

But you are saying that you want o do this for performance reasons, avoid going through millions of rows. Such a query had already done the damage, it read from disk all the rows and had to sort them so the performance price was already payed.

A better alternative to selecting a random sample of data is to use the TABLESAMPLE clause, see Limiting Result Sets by Using TABLESAMPLE:

select * 
from table tablesample (100 rows);

TABLESAMPLE will be way more efficient as it actually avoids reading all the data, it only samples some of the pages in the table and returns all rows in the sampled pages.

But consider that if you use TABLESAMPLE your WHERE clause are applied after the sampling. So it may be that the sample does not contain any row for the device/sensor you're interested in. This is specially true for sensor with small data.

SQL Server Running Total – How to Use Running Total Aggregates for Financial Accumulations

Interesting problem. Here is my attempt.

First we calculate a running total of the Allocation.InvoiceAmt over the various InvoiceId:

WITH inv AS
  ( SELECT 
      InvoiceId, Account, 
      InvAmt = InvoiceAmt,
      SumInvAmt = SUM(InvoiceAmt) OVER 
        (PARTITION BY InvoiceId 
         ORDER BY Account
         ROWS BETWEEN UNBOUNDED PRECEDING
                  AND CURRENT ROW)
    FROM Allocation 
  )

and the same for Payment.Amount:

, pay AS
  ( SELECT 
      PaymentId, InvoiceId, PaymentType, CheckNumber, 
      PayAmt = Amount,
      SumPayAmt = SUM(Amount) OVER 
        (PARTITION BY InvoiceId 
         ORDER BY PaymentId
         ROWS BETWEEN UNBOUNDED PRECEDING
                  AND CURRENT ROW)
    FROM Payment 
  )

Then we combine the previous two CTEs:

SELECT 
    inv.InvoiceId,
    pay.PaymentId,
    inv.Account,
    PaymentAllocated = 
      CASE WHEN SumPayAmt <= SumInvAmt - InvAmt
             OR SumInvAmt <= SumPayAmt - PayAmt
      THEN 0
      ELSE
          CASE WHEN SumPayAmt <= SumInvAmt THEN SumPayAmt      -- these would be
               ELSE SumInvAmt END                              -- simpler if there
        - CASE WHEN SumPayAmt-PayAmt <= SumInvAmt-InvAmt       -- was a LEAST() and 
               THEN SumInvAmt-InvAmt                           -- a GREATEST() function
               ELSE SumPayAmt-PayAmt END
      END
FROM inv JOIN pay
  ON inv.InvoiceId = pay.InvoiceId
ORDER BY 
    inv.InvoiceId,
    pay.PaymentId,
    inv.Account ;

Test at SQL-Fiddle

Best Answer

Related Solutions

Sql-server – SELECT every nth value by Date from large table

SQL Server Running Total – How to Use Running Total Aggregates for Financial Accumulations

Related Question