Average interpurchase times

T

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
 
J

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/
 
S

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

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
 
T

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


Jerry Whittle said:
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.


thomasDrew said:
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
 
V

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



thomasDrew said:
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


Jerry Whittle said:
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.


thomasDrew said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top