Average interpurchase times

[thomasDrew]

Table is organized like this:

Date Purchase Quantity
01/02/2010 12
01/23/2010 45
05/04/2010 10
07/06/2010 5
....

How do I calculate the average time between purchase?

Thank you for any help you can provide.

Drew Yallop

 

[Jerry Whittle]

SELECT Avg([Date]-DMin("[Date]","Yallop")) AS AvgDaysBetweenPurchases
FROM Yallop;

BTW: Date is a very bad name for a field or table. It's a reserved word and
can cause problems if you forget to put the [ ] around it. Read more about
reserved words at:

http://support.microsoft.com/kb/286335/

 

[Stefan Hoffmann]

hi Thomas,

Table is organized like this:

Date Purchase Quantity
01/02/2010 12
01/23/2010 45
05/04/2010 10
07/06/2010 5
...

How do I calculate the average time between purchase?

Use this helper query to calculate the previous date:

SELECT
O.[Date],
(SELECT TOP 1 Max(I.[Date])
FROM yourTable I
WHERE I.[Date] < O.[Date]
ORDER BY Max(I.[Date])
) AS PreviousDate
FROM yourTable O
ORDER BY O.[Date];

You should consider renaming your [Date] column.


mfG
--> stefan <--

 

[KenSheridan via AccessMonster.com]

Try this:

SELECT AVG(Interval)
AS AverageInterval
FROM
(SELECT P1.Date, P1.Date -
(SELECT MAX(P2.Date)
FROM Purchases as P2
WHERE P2.Date < P1.Date) AS Interval
FROM Purchases AS P1);

where Purchases is the table name.

Note the caveats the others have expressed regarding the use of Date as a
column name. PurchaseDate would be better.

Ken Sheridan
Stafford, England

 

[thomasDrew]

Thank you for your quick reply Unfortunately my question was ill-formed.

The table has an additional field - ID. So the table looks like this:

ID NewDate Purchase Quantity
1 01/02/2010 12
1 01/23/2010 45
1 05/04/2010 10
1 07/06/2010 5
2 03/020/2010 6
2 05/01/2010 8

I want to calculate iaverage nterpurchase times for each ID.

Best,
Drew Yallop

1
--
Drew Yallop

SELECT Avg([Date]-DMin("[Date]","Yallop")) AS AvgDaysBetweenPurchases
FROM Yallop;

BTW: Date is a very bad name for a field or table. It's a reserved word and
can cause problems if you forget to put the [ ] around it. Read more about
reserved words at:

http://support.microsoft.com/kb/286335/
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Table is organized like this:

Date Purchase Quantity
01/02/2010 12
01/23/2010 45
05/04/2010 10
07/06/2010 5
...

How do I calculate the average time between purchase?

Thank you for any help you can provide.

Drew Yallop

 

[vanderghast]

SELECT ( MAX(newDate)-MIN(newDate) ) / (COUNT(*) - 1) , id
FROM table
GROUP BY id


Indeed, the number of date between purchases would be like: (a_2 - a_1)
+ (a_3 - a_2) + ... + (a_n - a_n-1) / ( n-1 )
which become, after simplification: ( a_n - a_1 ) / (n-1)


Vanderghast, Access MVP

Thank you for your quick reply Unfortunately my question was ill-formed.

The table has an additional field - ID. So the table looks like this:

ID NewDate Purchase Quantity
1 01/02/2010 12
1 01/23/2010 45
1 05/04/2010 10
1 07/06/2010 5
2 03/020/2010 6
2 05/01/2010 8

I want to calculate iaverage nterpurchase times for each ID.

Best,
Drew Yallop

1
--
Drew Yallop

SELECT Avg([Date]-DMin("[Date]","Yallop")) AS AvgDaysBetweenPurchases
FROM Yallop;

BTW: Date is a very bad name for a field or table. It's a reserved word
and
can cause problems if you forget to put the [ ] around it. Read more
about
reserved words at:

http://support.microsoft.com/kb/286335/
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Table is organized like this:

Date Purchase Quantity
01/02/2010 12
01/23/2010 45
05/04/2010 10
07/06/2010 5
...

How do I calculate the average time between purchase?

Thank you for any help you can provide.

Drew Yallop