How to validate if there was 12 sequential payments

Question

As example :

I have this scenario where we receive payments, a singular payment per family, and register those payments with it's amount in the DB.

The thing is that a family can move their loan from bank1 to bank2, only if they have 12 or more sequential payments.

As example if they have registered a payment for

oct, nov, dec, jan, feb, mar, apr, may, jun, jul, ago, and sept.

and feb didn't received any payment, the count will start over at march.

Coworkers are suggesting that the best approach is, in every payment registration count the total payments and register the total sequential payments in an int column called sequential.

as:

Payment     Family      Bank     Date          Sequential
---------------------------------------------------------
1200        2           1        10-22-2009    1
1200        2           1        11-22-2009    2
.
.
.
1200        2          1        08-22-2010     11
1200        2          1        09-22-2010     12

What I think, there must be an approach where the sequential column is needless, where if I want to validate if the last order by Date DESC 12 rows are sequential with only 1 month in difference.

any ideas?

Edited:

• There will be million of rows in this table.

• Also prefer to have only the dates in the tables and work with them at application level

Answer 1

Hi, I think a simple query will help, check this:

SELECT COUNT(*)
FROM payments p
WHERE p.Family = 2 AND p.Date between '01-01-2009' and '12-01-2009'

this way, you'll get the number of payments between any date with your current table structure.

Answer 2

How about this:

SELECT  PT.Payment
      , PT.Family
      , PT.Bank
      , PT.Date
      , (SELECT COUNT(*) FROM PaymentTable T
         WHERE DATEDIFF (d, T.Date, PT.Date) < 31) as IsSequential
  FROM PaymentsTable PT

The above query will tell you for each payment if it's sequential (i.e. if there was a payment made the month before it)

Then you could run a query to determine if there are 12 sequential payments made for a specific month, or for a specific family.

Let's say you want to display all families that have at least 12 sequential payments:

SELECT  ST.Family
     , COUNT(ST.IsSequential) as NumberOfSequentialPayments
 FROM
  (SELECT   PT.Payment
          , PT.Family
          , PT.Bank
          , PT.Date
          , (SELECT COUNT(*) FROM PaymentTable T
          WHERE DATEDIFF (d, T.Date, PT.Date) < 31) as IsSequential
     FROM PaymentsTable PT
   ) AS ST
  WHERE NumberOfSequentialPayments >= 12
 GROUP BY ST.Family

Answer 3

Analytics!

Data:

create table payments
(amount       number,
 family       number,
 bank         number,
 payment_date date
);

insert into payments values (1200, 2, 1, date '2010-01-01');
insert into payments values (1200, 2, 1, date '2010-02-02');
insert into payments values (1200, 2, 1, date '2010-03-03');
insert into payments values (1200, 2, 1, date '2010-04-04');
insert into payments values (1200, 2, 1, date '2010-05-05');
insert into payments values (1200, 2, 1, date '2010-06-07');
insert into payments values (1200, 2, 1, date '2010-07-07');
--skip august
--insert into payments values (1200, 2, 1, date '2010-08-08');
insert into payments values (1200, 2, 1, date '2010-09-09');
insert into payments values (1200, 2, 1, date '2010-10-10');
insert into payments values (1200, 2, 1, date '2010-11-11');
--double pay november
insert into payments values (1200, 2, 1, date '2010-11-30');
insert into payments values (1200, 2, 1, date '2010-12-12');

Query:

select * 
  from (select family, bank, 
               trunc(payment_date, 'mon') as payment_month,
               lead ( trunc(payment_date, 'mon')) 
                 over ( partition by family
                        order by payment_date) 
                 as next_payment_month
          from payments 
         order by payment_date desc 
       )
       -- eliminate multiple payments in month
 where payment_month <> next_payment_month
       -- find a gap
   and add_months(payment_month, 1) <> (next_payment_month)
       -- stop at the first gap
   and rownum = 1

Results:

    FAMILY       BANK PAYMENT_M NEXT_PAYM
---------- ---------- --------- ---------
         2          1 01-JUL-10 01-SEP-10

You can use the value in NEXT_PAYMENT_MONTH to perform whatever comparison you want at the application level.

---

SELECT trunc(MONTHS_BETWEEN(SYSDATE, DATE '2010-01-01')) FROM DUAL

gives you a number of months - that was what I meanty by using the value at the application level.

So this:

select trunc(
       months_between(sysdate, 
         (select next_payment_date
            from (select family, bank, 
                         trunc(payment_date, 'mon') as payment_month,
                    lead ( trunc(payment_date, 'mon')) 
                    over ( partition by family
                           order by payment_date) 
                      as next_payment_month
                    from payments 
                   where family = :family
                   order by payment_date desc 
                 )
           where payment_month <> next_payment_month
             and add_months(payment_month, 1) <> (next_payment_month)
             and rownum = 1
          ) 
       )
  from dual

Gives you a number of months with successive payments since the last missed month.

Answer 4

It is possible to do it as other have pointed out.

However, this is not a case when you have relational data, but you do things sequentially, which is a bad thing.

This is a case when a business rule is sequential in nature; in such cases having a sequential helper field might

• simplify your queries
• improve performance (if you talk about 100M records this sudenlly becomes almost highest rated factor and various denormalization ideas spring to mind)
• make sense for other business rules (allow more functionality and flexibility)

Re last point: I think the most complete solution would require re-examining the business rules - you would probably discover that users would talk about 'missed payments', which suggest other tables, such as 'payment plan/schedule' and tied with other processes this might be really the right place to have either missed payment column or sequential value... This structure would also support flexibility in grace periods, prepaying, etc...

Answer 5

To validate whether a single family have 12 sequential payments over the past twelve months, regardless of bank, use:

select sum(payment)                          total_paid, 
       count(*)                              total_payments, 
       count(distinct trunc(pay_date,'mon')) paid_months
from   payment_table
where  family = :family and pay_date between :start_date and :end_date;

total_payments indicates the number of payments made in the period, while paid_months indicates the number of separate months in which payments were made.

If you want to check whether they have already switched bank in the selected period, add a group by bank clause to the above query.

To list all families with 12 distinct months of payments within the period, use:

select family,
       sum(payment)                          total_paid, 
       count(*)                              total_payments, 
       count(distinct trunc(pay_date,'mon')) paid_months
from   payment_table
where  pay_date between :start_date and :end_date
group by family
having count(distinct trunc(pay_date,'mon')) = 12;

If you want to restrict the results to families that have not already switched bank in the selected period, add a and count(distinct bank) = 1 condition to the having clause of the above query.

I suggest ensuring that the payment table has an index on family and pay_date.