Weird Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Weird Query

  1. #1
    Join Date
    Nov 2000
    Posts
    39
    Hello,

    I was wondering if someone had an easy answer to this question:

    Here is the table:

    FIRSTNAME LASTNAME PLATE YEAR DATE MILEAGE

    Bob Smith SF234 1997 5/13/01 3200
    Bob Smith SF234 1997 8/12/01 9500
    MARY JOHNSON BN987 1992 3/15/01 28000
    MARY JOHNSON BN987 1992 5/12/01 33000
    MARY JOHNSON BN987 1992 8/12/01 38000
    PAT JACKSON PO234 1999 4/12/01 2100
    PAT JACKSON PO234 1999 6/12/01 2700
    PAT JACKSON PO234 1999 8/13/01 35000

    The task is to:
    1. Calculate the average number of days between transactions for all customers
    2. Calculate the average mileage between transactions for all customers.

    There are multiple transactions for every customer. There are about 25 000 customers and about 75 000 transactions.

    I'm having a serious brain cramp this morning...

    Does anyone know an easy way to solve this one....

    Any help is much appreciated.


  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Try this:

    Code:
    SELECT
       Plate,
       AVG ( DaysBetween ),
       AVG ( MilesBetween)
    FROM
       (
          SELECT
             Date   -
             LAG (
                Date, 1
                )
             OVER
                (
                   PARTITION BY
                      Plate
                   ORDER BY
                      Date
                )
                AS   DaysBetween,
             Mileage   -
             LAG (
                Mileage, 1
                )
             OVER
                (
                   PARTITION BY
                      Plate
                   ORDER BY
                      Date
                )
                AS   MilesBetween
          FROM
             Table1
       )
    GROUP BY
       Plate
    HTH,

    - Chris

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

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


Click Here to Expand Forum to Full Width