MySQL Average days between two dates

Databases
1

Hi,

I have a MySQL database table (tblinvoices) containing a list of invoices. Each invoice contains a customer ID (custId) an invoice date (OrderDate) and a paid date (PaidDate).

What I’m trying to do is find which customers pay quickest on average. To do that I need to calculate the difference in days between the invoice date and the paid for date for each customer (each customer will have one or more invoices), calculate the average and then display the results in order of the lowest average days.

For example:
Customer A invoice date 2015.09.01 - paid date 2015.09.16 - therefore date difference 15 days

Customer A invoice date 2015.09.05 - paid date 2015.09.18 - therefore date difference 13 days

Therefore the average days for this customer is 14 days

Customer B invoice date 2015.09.01 - paid date 2015.09.13 - therefore date difference 12 days

Customer B invoice date 2015.09.05 - paid date 2015.09.11 - therefore date difference 6 days

Therefore the average days for this customer is 9 days

I’m not sure how this can be done. I know about date difference but not sure how to group by customer,etc and then work out the average for that customer.

Any help will be gratefully received.

Many thanks.

2

SELECT custId , AVG(DATEDIFF(PaidDate,OrderDate)) AS avg_datediff FROM tblinvoices GROUP BY custID ORDER BY avg_datediff ASC

3

Sorry for the delay in replying…

Thank you so much for your solution - it works a treat.

Many thanks for your help.

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.