How to check if there were 12 consecutive payments

Question

As an example:

I have such a scenario when we receive payments, a one-time payment for a family and register these payments with its amount in DB .

The fact is that a family can transfer its loan from bank1 to bank2 only if they have 12 or more consecutive payments.

As an example, if they registered payment for

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

and feb didn’t receive any payment, the counter will start with march .

Employees suggest that the best approach is to have common payments taken into account in each payment registration and to record full consecutive payments in an int column named 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 should be the approach when the sequential column is useless, where if I want to check if the last order by Date DESC 12 rows are consecutive, only with a difference of 1 .

any ideas?

Edited by:

• There will be a million rows in this table .

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

Answer 1

To check if one family has 12 consecutive payments in the last twelve months, regardless of the 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 during this period, and paid_months indicates the number of individual months in which payments were made.

If you want to check if they have already transferred to the bank in the selected period, add the group by bank offer to the above request.

To list all families with 12 monthly payments for a 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 limit the results to families that have not yet switched the bank in the selected period, add the condition and count(distinct bank) = 1 to the having sentence of the above request.

I suggest making sure that the payment table has an index for the family and pay_date.

Answer 2

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 any comparison you want at the application level.

---

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

gives you a few months - this is what I meant using value at the application level.

So this is:

 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 several months with consecutive payments since the last month missed.

Answer 3

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

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

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

Answer 4

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 request will inform you of each payment if it is consistent (i.e. if a payment was made a month before)

You can then run a query to determine if there are 12 consecutive payments for a specific month or for a specific family.

Suppose you want to display all families that have at least 12 consecutive 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 5

This can be done as others have pointed out.

However, this is not the case when you have relational data, but you do it consistently, which is bad.

This is the case when a business rule is consistent; in such cases, the presence of a sequential auxiliary field may

• simplify your requests
• increase productivity (if you are talking about 100 megapixel recordings, this sudenlly becomes almost the highest rating and various spring denormalization ideas).
• make sense for other business rules (allow more functionality and flexibility)

Last: I think that the most complete solution will require a revision of the business rules - you will probably find that users will talk about “missed payments” that other tables offer, such as “payment plan / schedule” and related to other processes, this can be a really good place to have either a missing payment column or a consistent cost ... This structure will also support flexibility during periods of benefits, prepayments, etc.