SQL Window function to find days past due

Question

I want calculate DPD (days past due) from loan list sorted by date. Every past Due date should re-counted. See example in attach . How can I calculate "Days past due" column ?

Answer 1

This should do it.

SELECT
t1.LoanNumber, t1.daydate, t1.Status, case 
when status = 'Past Due'
  then datediff(day, 
             (select max(daydate) from table1 t2 
                where t2.loanNumber =t1.loanNumber 
                and t2.daydate<t1.daydate and t2.status<> 'Past Due'
             ),
             daydate)
   else 0 end as [Days Past Due]
from table1 t1

You can try it on sqlfiddle