MS SQL : Get average time between dates

I need to write an MS SQL to return the average time in days or months between all dates in a list. For example, a have an array containing the dates 10/2/2001, 11/5/2001, 12/18/2001, 02/04/2003. What SQL statement would return the average time between all dates?
brucesilverAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nmcdermaidCommented:
SQL can be run on tables in databases. It can't be run on array variables.

Do you want some code that will get the average out of an date array?

If you want to put them into a table, are all dates consecutive?

In that case the SQL would something like this:

SELECT AVG(DATEDIFF(day,tblDates.DateField, tblPreviousDate.PreviousDateField))
FROM
tblDates,
(SELECT MAX(DateField) As PreviousDateField FROM tblDates WHERE DateField < tblDates.DateField) tblPreviousDate
brucesilverAuthor Commented:
Sorry  nmcdermaid

I don't know what I was thinking by talking about an array. I have a Access DB with one date field per record, such as would exist in a log of phone contacts. I need an SQL to get an average numer of days between calls to a certain contact, each contact having a unique ID number. The dates that the calls were made would be consecutive.
nmcdermaidCommented:
Don't worry I'm still on the case....


N
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

nmcdermaidCommented:
Can you post the table name, and the date and contact field names? I'll give you the SQL you need.
brucesilverAuthor Commented:
Table name: accounts
Date field: ActivDate
Customer ID: Customerid

Much appreciate the attention to this issue.

I increased the points by 50.

It would appear to be a simple thing to do with Datediff, but DateDiff would require two dates to calculate, and there is but one date field in the table. The whole thing could of course be done programmatically, but with considerably more code.
nmcdermaidCommented:
You use the DMax function to retreive the previous date in the same record.... like so:


SELECT
DMax("[ActivDate]","accounts","[ActivDate] < #" & Format([ActivDate],"mm/dd/yyyy") & "# AND Customerid= '" & [Customerid] & "'") AS PreviousDate,
accounts.CallerID,
DateDiff("d",DMax("[ActivDate]","accounts","[ActivDate] < #" & Format([ActivDate],"mm/dd/yyyy") & "#"),[ActivDate]) AS CallInterval
FROM accounts;

This query retreives:
PreviousDate: not required... only there for illustration
CallerID
CallInterval

For each record. Note that this part of the expression:  ~ Format([ActivDate],"mm/dd/yyyy") ~ was required to convert the date format to US style..... it should not be required but it was!!...  you may find that you don't need it, you can just use ActivDate


Once you have this query workingm save it as a query (Called say qryCallInterval)

Then run this query to get the average interval



SELECT CallerID, AVG(CallInterval)
FROM qryCallInterval


If you want to filter on dates, just include ActivDate in all queries and filter on it in a where clause.
brucesilverAuthor Commented:
This is tremendous. I had long ago given up on this. Thank you!!
nmcdermaidCommented:
No worries.... this handy function was brought to my attention by Bonjour_aut, only yesterday!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
planoczCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
No response from brucesilver from 12/11/2003 comment
Award points to nmcdermaid is recommend.
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

planocz
EE Cleanup Volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.