CODEWITHSUNDEEP
sqlsql-servert-sql
FAHRB
FAHRB

Reputation: 39

Conditional counting of late payments monthly, including reset to zero

I have a table with the following structure:

   EOM        ID     Principal       Pay_plan  cum_Payments
2019-12-31  AY4525  25000.000000    796.000000  936.000000
2020-01-31  AY4525  25000.000000    1592.000000 936.000000
2020-02-29  AY4525  25000.000000    2388.000000 936.000000
2020-03-31  AY4525  25000.000000    3184.000000 3184.00000
2020-04-30  AY4525  25000.000000    3980.000000 3980.00000
2020-05-31  AY4525  25000.000000    4776.000000 3980.00000
2020-06-30  AY4525  25000.000000    5572.000000 3980.00000
2020-04-30  KD4525  35000.000000    500.000000  500.000000
2020-05-31  KD4525  35000.000000    1000.000000 1000.00000
2020-06-30  KD4525  35000.000000    1500.000000 1000.00000
2020-07-31  KD4525  35000.000000    2000.000000 2500.00000

So I have a cumulative payment plan and cumulative payments for unique client IDs per month. Now I want to add a column which starts counting months that the client is late with payments, hence when pay_plan > cum_payments:

   EOM        ID     Principal       Pay_plan  cum_Payments  months_Late
2019-12-31  AY4525  25000.000000    796.000000  936.000000       0
2020-01-31  AY4525  25000.000000    1592.000000 936.000000       1
2020-02-29  AY4525  25000.000000    2388.000000 936.000000       2
2020-03-31  AY4525  25000.000000    3184.000000 3184.00000       0
2020-04-30  AY4525  25000.000000    3980.000000 3980.00000       0
2020-05-31  AY4525  25000.000000    4776.000000 3980.00000       1
2020-06-30  AY4525  25000.000000    5572.000000 3980.00000       2
2020-04-30  KD4525  35000.000000    500.000000  500.000000       0
2020-05-31  KD4525  35000.000000    1000.000000 1000.00000       0
2020-06-30  KD4525  35000.000000    1500.000000 1000.00000       1
2020-07-31  KD4525  35000.000000    2000.000000 2500.00000       0

The counter must be reset when pay_plan = cum_payments again. I have tried numerous ways of doing this with OVER(), but not found a solid solution. Anyone got an idea how to solve this?

Upvotes: 1

Views: 200

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is a gaps-and-islands problem. The islands are when the cumulative amount is less than the planned amount. So, you can use a cumulative sum to define the islands and then row_number():

select t.*,
       (case when cum_payments >= pay_plan then 0
             else row_number() over (partition by id, grp order by eom) - 1
        end) as months_late
from (select t.*,
             sum(case when cum_payments >= pay_plan then 1 else 0 end) over (partition by id order by eom) as grp
      from t
     ) t;

Here is a db<>fiddle.

You can handle the situation where the first payment is late by using:

select t.*,
       (case when cum_payments >= pay_plan then 0
             else row_number() over (partition by id, grp order by eom) - 1 +
                  (case when min(eom) over (partition by id) = min(eom) over (partition by id, grp) and
                             first_value(cum_payments) over (partition by id, grp order by eom) < first_value(pay_plan) over (partition by id, grp order by eom)
                        then 1 else 0
                   end)
        end) as months_late
from (select t.*,
             SUM(case when cum_payments >= pay_plan then 1 else 0 end) over (partition by id order by eom) as grp
      from t
     ) t

I actually left this logic out of the above, because it seems inelegant. There might be a better solution, but it does not readily occur to me. Here is the revised db<>fiddle.

Upvotes: 2

mkRabbani
mkRabbani

Reputation: 16908

If I understand your logic correct, Months_late value for your last row should be 0 and if it is correct, you can use this below logic to achieve your requirement-

Demo Here

Let cum_Payments will always increase or same per day for a specific ID

SELECT *,
(
    SELECT COUNT(*) 
    FROM your_table B 
    WHERE B.cum_Payments = A.cum_Payments 
    AND B.EOM < A.EOM
    AND B.ID = A.ID
) Months_late 
FROM your_table A
ORDER BY ID,EOM

This following query will return the exact result as you are looking for. It is true that the query is bit heavy for a table with huge data but it is acceptable if you have one time use of this query. In case or more use, you can think about creating a view for improving query execution performance.

Demo2 Here

SELECT *,
(
    SELECT count(1) FROM your_table b
    WHERE b.id = a.id AND b.eom <= a.eom
    AND b.eom >
    (
        ISNULL
        (
            (
                SELECT MAX(eom) FROM your_table c
                WHERE c.id = a.id AND (c.pay_plan - c.cum_payments) = 0
                AND c.eom <= a.eom
            )
            ,        
            (
                SELECT MIN(eom) FROM your_table d
                WHERE d.id = a.id
            ) 
        ) 
    )
)
FROM your_table a
ORDER BY id, eom

Upvotes: 2

Related Questions