# How you can validate if there is 12 consecutive obligations

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