Ask the Expert

Average date difference

I read your response to this question: Calculating AVG date. I am having problems trying to put this in the correct syntax. I have a begin_date and end_date range and I want to get the average of the (completed_date - assigned_date) so will it be

 SELECT BETWEEN (begin_date) AND (end_date) AVG (completed_date - assigned_date) ???

I am trying to get the average completion time between the two dates.
The

    Requires Free Membership to View

earlier column pointed out that AVG only works on numbers, and showed a technique for mapping DATEs to numbers so you could use AVG. However, in this case, we are averaging numbers. (completed_date - assigned_date) is the number of days between assigned_date and completed_date. The technique for averaging numbers is the same whether the numbers quantify days, weeks, dollars or apples: simply use the AVG group function.

If your table is called assignment, you can compute the completion time for each row by saying:

 SELECT completed_date - assigned_date AS completion_days FROM assignment;

This shows the number of days between assigned_date and completed_date, assuming that neither of the dates is NULL. If either date is NULL (or if both are), then the difference will be NULL also.

To get the average, use the regular AVG group function.

 SELECT AVG (completed_date - assigned_date) AS avg_completion_days FROM assignment;

This will compute the average completion time (in days) for all rows where the expression "completed_date - assigned_date" is not NULL, which is probably what you want.

You didn't specify exactly what has to be between begin_date and end_date. The query below requires both completed_date and assigned_date to be in that range. The query also assumes that begin_date and end_date are stored in a one-row table called date_range.

 SELECT AVG (completed_date - assigned_date) AS avg_completion_days FROM assignment, date_range WHERE assigned_date BETWEEN begin_date AND end_date AND completed_date BETWEEN begin_date AND end_date;

Since AVG (x - y) = AVG (x) - AVG (y) (assuming NULLs are handled correctly), you could get the same results by using the technique described in the earlier question:

 SELECT AVG (completed_date - SYSDATE) - AVG (assigned_date - SYSDATE) AS avg_completion_days FROM assignment, date_range WHERE assigned_date BETWEEN begin_date AND end_date AND completed_date BETWEEN begin_date AND end_date;

but since it's longer to type, harder to understand and slower to run, why would you want to?

This was first published in November 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: