Calculate Days between dates of a sale for a particular product

  • Thread starter Thread starter DawnTreader
  • Start date Start date
D

DawnTreader

Office Version:Office 2007
Operating System:Windows XP
Business Application:Access

Hello All

i have a situation where i am trying to find out the speed at which i
sell particular products. i know how to get the total amount sold in a
period of time, but what i want to know is the amount of time between
each sale of a product and then calculate the average time to sale of
that product.

so say i have part number 306657 that sells like so:

CUST_ORDER_ID PART_ID MaxOfORDER_DATE Orders
90641 306657
2010/01/07 1
90650 306657
2010/01/07 1
90691 306657
2010/01/21 1
90714 306657
2010/01/27 1

how do i calculate the amount of time between each order? once i get
that figured out it wouldnt be a problem to then average that and come
up with a rating of how frequently the part has sold. at this point
all i have is the SQL to do the above table.

SELECT dbo_CUST_ORDER_LINE.CUST_ORDER_ID, dbo_CUST_ORDER_LINE.PART_ID,
Max(dbo_CUSTOMER_ORDER.ORDER_DATE) AS MaxOfORDER_DATE,
Count(dbo_CUSTOMER_ORDER.ID) AS Orders
FROM dbo_CUST_ORDER_LINE LEFT JOIN dbo_CUSTOMER_ORDER ON
dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID
WHERE (((dbo_CUST_ORDER_LINE.PART_ID) Is Not Null And
(dbo_CUST_ORDER_LINE.PART_ID)="306657") AND
((dbo_CUSTOMER_ORDER.ORDER_DATE) Between #1/1/2010# And #1/31/2010#))
GROUP BY dbo_CUST_ORDER_LINE.CUST_ORDER_ID,
dbo_CUST_ORDER_LINE.PART_ID;

keep in mind that i would be removing the part number and between date
criteria so that i would get this over my entire line of parts.

if anyone knows the answer or any ideas i could try please let me
know. as all ways, any and all help appreciated. :)
 
You could try the following

Query One: Get the parts and dates that the parts were sold (qPartsOrderedOnDate)
SELECT dbo_CUST_ORDER_LINE.PART_ID
, dbo_CUSTOMER_ORDER.ORDER_DATE
FROM dbo_CUST_ORDER_LINE INNER JOIN dbo_CUSTOMER_ORDER
ON dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID

Now use that query twice to get the prior date that something was sold and use
datediff to calculate the number of elapsed days between sales of specific
part. If you are looking for the time between sales to a particular customer
then you will need to add the customer information into both queries.

SELECT A.Part_ID
, A.Order_Date
, Max(B.Order_Date) as PriorDate
, DateDiff("d", Max(B.Order_Date), A.Order_Date) as TimeLag
FROM qPartsOrderedOnDate as A LEFT JOIN qPartsOrderedOnDate as B
ON A.Part_ID = B.Part_ID
AND A.Order_Date > B.Order_Date
GROUP BY A.Part_ID, A.Order_Date



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
You could try the following

Query One: Get the parts and dates that the parts were sold (qPartsOrderedOnDate)
SELECT dbo_CUST_ORDER_LINE.PART_ID
, dbo_CUSTOMER_ORDER.ORDER_DATE
FROM dbo_CUST_ORDER_LINE INNER JOIN dbo_CUSTOMER_ORDER
ON dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID

Now use that query twice to get the prior date that something was sold and use
datediff to calculate the number of elapsed days between sales of specific
part.  If you are looking for the time between sales to a particular customer
then you will need to add the customer information into both queries.

SELECT A.Part_ID
, A.Order_Date
, Max(B.Order_Date) as PriorDate
, DateDiff("d", Max(B.Order_Date), A.Order_Date) as TimeLag
FROM qPartsOrderedOnDate as A LEFT JOIN qPartsOrderedOnDate as B
ON A.Part_ID = B.Part_ID
AND A.Order_Date > B.Order_Date
GROUP BY A.Part_ID, A.Order_Date

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County



- Show quoted text -
Click to expand...

Thanks John.
 
Back
Top