Sunday, February 26, 2012

Find Consecutive Occurrences

Hi,
I am in need of a query which would find the same customer coming in for three or more consecutive dates. To elaborate

I have a details table where I capture the following details

CustID, DateofPurchase, PurchaseDetails

I need a query to find how many customers have come in everyday consecutive day and count of the same for the a given period, say a month. Can anyone help me with a query for the same.

Thanks for your help in advance.
Regards
DineshProbably, Please read the hint link in the sticky at the top of the forum to help us with more information|||Brett,

In my table I have details as below

CustID DateofPurchase PurchaseDetails
------------------
1 01/05/2006 Item1, Item2, Item3
2 01/06/2006 Item2, Item5, Item1, Item7
1 01/06/2006 Item4, Item5, Item6
3 01/06/2006 Item4, Item5, Item6
1 01/07/2006 Item3, Item4, Item5
3 01/07/2006 Item1, Item2, Item3
1 01/08/2006 Item1, Item2, Item3
2 01/08/2006 Item4, Item5, Item6
3 01/08/2006 Item4, Item5, Item6
2 01/09/2006 Item2, Item5, Item1, Item7
2 01/10/2006 Item1, Item2, Item3, Item4
1 01/10/2006 Item1, Item2, Item3, Item4
1 01/11/2006 Item4, Item5, Item6

I need the query which will return say Cust ID 1 has come 4 days in a row, or Cust ID 2 has come 3 days in a Row.
I want the below output

Cust ID No of Consecutive Days
1 4
2 3
3 3
2 2
1 2

Would this help. Please let me know.

No comments:

Post a Comment