How to find the time elapsed between 2 events

[hcapper]

Maybe someone here can help me. I have a database of customers with
information on their purchases. I need to get the time elapsed between
purchases and then get an average of time (in days) between purchases.

For example I want to sample 10% of my customers with at least X number of
purchases, to find the average time between their 2nd and 3rd purchases. (or
1st & 2nd, 3rd & 4th). I'm having a hard time wrapping my head around how to
make sure that I am looking at the right purchases. How do I know that I'm
working with exactly the 2nd and 3rd purchase?

I hope to be able to do this with a query or series of queries, since I'm
only mediocre at coding.

Thanks for your help.


Rob

 

[Ken Sheridan]

Rob:

The following query should give you the days between the first and second
purchases per customer where 2 is entered at the [Enter last purchase
number:] parameter prompt and the customer has made at least as many
purchases as the number entered at the [Enter number of purchases:] parameter
prompt:

SELECT CustomerID, SaleDate -
(SELECT MAX(SaleDate)
FROM Sales AS S2
WHERE S2.CustomerID = S1.CustomerID
AND S2.SaleDate < S1.SaleDate)
AS DaysBetween
FROM Sales AS S1
WHERE
(SELECT COUNT(*)
FROM Sales AS S2
WHERE S2.CustomerID = S1.CustomerID
AND S2.SaleDate <= S1.SaleDate)
= [Enter last purchase number:]
AND
(SELECT COUNT(*)
FROM Sales As S2
WHERE S2.CustomerID = S1.CustomerID)

= [Enter number of purchases:];

This assumes no customer makes more than one purchase on the same day. For
the number of days between the second and third purchase enter 3 at the
[Enter last purchase number:] parameter prompt.

You can then base another query on this to restrict it to the sample 10% and
then AVG the DaysBetween column from the restricted result set.

Ken Sheridan
Stafford, England