I need to calculate the avg days between date of sales:
My DB is like this:
id | customer | creation_date | payment_date
1 | 234 | 2017/07/6 | 2017/07/8
34 | 234 | 2017/08/4 | 2017/08/10
53 | 234 | 2017/09/15 | 2017/09/17
67 | 234 | 2017/10/1 | 2017/07/6
So I need to calculate de difference of days (creation_date) between Order 1 and Order 34, Order 34 and Order 53, Order 53 and Order 67, etc...
and calculate an AVG of days depending the number of results.
So I know how to calculate the difference of days between 2 dates using this small script:
$seconds=strtotime($date1) - strtotime($date2);
$difference=intval($seconds/60/60/24);
$positive = $difference * -1;
but I don´t know how to take the date of the las result and compare it with the next result.
Please someone who can help me with this enigma. Thanks!
I could be misunderstanding what you are looking for, but I would think something like this should work
(TO_DAYS(MAX(creation_date))-TO_DAYS(MIN(creation_date))) / (COUNT(1)-1)
This will get you the total days between the first and last; and divide by the number of "spaces" between orders.
Edit: ....and if you wanted to treat orders on the same date as a single order, you can just change COUNT(1) to COUNT(DISTINCT creation_date).
...all this assumes the db designer was sane and actually used DATE data types for date values.
To summarize, the average of the span sizes should be the same as the total span divided by the number of spans.
You can keep track of the previous result using a variable outside of the loop to get your MySQL table and then run the loop through the rows of the table:
$last_positive = 0;
while ($row = $result->fetch_assoc()){
$date1 = $row['creation_date'];
$date2 = $row['payment_date'];
$seconds=strtotime($date1) - strtotime($date2);
$difference=intval($seconds/60/60/24);
$positive = abs($difference);
//DO SOME COMPARISON HERE
echo($last_positive >= $positive);
$last_positive = $positive;
}
I'd also suggest using abs to get the absolute value instead of multiplying by -1.
SOLVED WITH THIS:
SELECT DATEDIFF(MAX(creation_date), MIN(creation_date)) / (COUNT(creation_date) - 1) AS SaleAverage FROM table WHERE customer = '$customer'