Query to get repeated customers between start and end date

Question

I am having a table with following attributes :

Date | Inv_Num | Cust_Name

Now, Given a start and end date I want all the Cust_Name that have multiple invoices with Date laying in the period?

Customer is not considered repeat if the difference between two invoice dates is less than 30 days.

Example :

01/19/2013  201237  A
01/11/2013  201235  A
01/05/2013  201276  B
03/05/2013  201256  B
12/12/2000  201010  C

If here StartDate is say 01/01/2013 and EndDate is 06/06/2013 then here A is not repeated customer while B is repeated customer and C is not in range mentioned. How to handle this query please help. Main problem am facing is how to compare the records after grouping them according to the Cust_Name.

Note that dates in MySQL adhere to a specific format. Fix that, then get back to us. — Strawberry

Rahul Rahul · Accepted Answer · 2015-06-01T17:49:32

You can try like below [Not Tested]

select a.cust_name
from invoice a join invoice b 
on a.cust_name = b.cust_name 
and a.`date` < b.`date`
where datediff(b.`date`, a.`date`) > 30

Query to get repeated customers between start and end date

1 Answers