As example :

I've this where we receive obligations, one payment per family, and register individuals obligations with it's amount within the DB.

The truth is that the family can move your finance from bank1 to bank2, only when they've 12 or even more consecutive obligations.

As example should they have registered a payment for

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

and feb did not received any payment, the count will begin again at march.

Colleagues are recommending that a great way is, in each and every payment registration count the entire obligations and register the entire consecutive obligations within an int column known as 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

Things I think, there has to be a strategy in which the sequential column is pointless, where if I wish to validate when the last order by Date DESC 12 rows are consecutive with only 1 month in difference.

any ideas?

Edited:

  • You will see million of rows within this table.

  • Also would rather only have the dates within the tables and use them at application level

Statistics!

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 should use the worthiness in NEXT_PAYMENT_MONTH to do whatever comparison you would like in the application level.


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

provides you with numerous several weeks - which was things i meanty using the value in the application level.

Which means 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

Provides you with numerous several weeks with successive obligations because the last skipped month.