Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Unhappy Unanswered: finding consecutive dates in table

    Hi

    I wondered whether someone could help me out.

    I have a table called transactions, which contains a membership no, servicedate, product, supplierofservice.

    I'm trying to identify where the Membership No has consecutive servicedates from a supplierofservice.

    I have no idea whether this should be a function or a query/subquery or how to write it.

    Supidgirl99

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Testing several records and having to remember a setting from the previous record is a prime candidate for a recordset loop in vba.

    dim PrevDate as date, prevsupplierofservice as string, rs as recordset
    set rs = currentdb().OpenRecordset("Select * from Transactions order by supplierofservice, servicedate")

    while not rs.eof
    if prevsupplierofservice <> rs!supplierofservice then
    prevsupplierofservice = rs!supplierofservice
    else
    if rs!servicedate = dateadd("d",1,prevdate) then
    debug.print rs!supplierofservice & " " & rs!servicedate
    end if
    prevdate = rs!servicedate
    end if
    rs.movenext
    wend

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Re: finding consecutive dates in table

    I have a table called transactions, which contains a membership no, servicedate, product, supplierofservice.
    I'm trying to identify where the Membership No has consecutive servicedates from a supplierofservice.
    You can't do this with a single query. You can do it by scanning the result of a single query with Visual Basic code.

    First, you query the table ORDER BY member#, supplier, servicedate. This puts all the records so that the record for each member, and for each supplier and member, and for each supplier and member and date, will be adjacent.

    So now you scan the table from beginning to end, remembering what the previous member#, supplier, and service-date were. The first record is a special case: that's where you pick up initial values but do nothing else. For the second and subsequent records you compare the values, process them if they've changed from the previous record in the way that you want, and then (in any case) remember them in preparation for the next record.

    Observe that this algorithm needs only a "one-record memory." Because of the sorting, it need only compare a record "to the previous one."

    The specific case you're looking for is: "the member# and supplier are the same as before, and the date is one day greater ("DateAdd...") than the one before."

    When all those magnetic tapes were whirling about in all those science-fiction movies, "that's what they were doing." They were either sorting data to put onto a tape, or they were processing sorted tapes.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you can do it in one query, just join the table to itself

    it won't be very fast, but it will be one query

    furthermore, you won't have to write any application code! (a consummation, as the saying goes, devoutly to be wished)

    yes, i remember mag tapes, i started on an IBM 360 model 40



    Code:
    select A.membershipno
         , A.servicedate
         , A.product
         , A.supplierofservice
      from transactions A
    inner
      join transactions B    
        on A.membershipno       
         = B.membershipno
       and A.supplierofservice  
         = B.supplierofservice
       and A.servicedate   
         = dateadd("d",1,B.servicedate)
    this query returns the 2nd (and 3rd and 4th, etc., if there are runs) transacation for the same supplier on consecutive days
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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