Weird Query

1. Junior Member
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

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. 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

#### Posting Permissions

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