Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    29

    Unanswered: 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
    Dinesh

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Probably, Please read the hint link in the sticky at the top of the forum to help us with more information
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2002
    Posts
    29
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •