Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS SQL : Get average time between dates

Posted on 2003-12-10
10
Medium Priority
?
313 Views
Last Modified: 2010-08-05
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?
0
Comment
Question by:brucesilver
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
10 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9912999
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
0
 

Author Comment

by:brucesilver
ID: 9913747
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.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9919105
Don't worry I'm still on the case....


N
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9919203
Can you post the table name, and the date and contact field names? I'll give you the SQL you need.
0
 

Author Comment

by:brucesilver
ID: 9919786
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.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9919882
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.
0
 

Author Comment

by:brucesilver
ID: 9920206
This is tremendous. I had long ago given up on this. Thank you!!
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 800 total points
ID: 9920233
No worries.... this handy function was brought to my attention by Bonjour_aut, only yesterday!
0
 
LVL 27

Expert Comment

by:planocz
ID: 10260609
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question