select average - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select average

Author

Topic

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-09-06 : 02:30:41

How do i find a average time from two dates. This is what I have so far.
Pay attention to this :
AVG (DATEDIFF (HOUR,a.InceptionDate,f.InceptionDate)AS DateDifference)
How to find the average time for each department.

SELECT a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate,
a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType, a.ComplaintType AS ComplaintTypeID,
a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC,f.ComplaintID, f.Comment, f.InceptionDate AS UpdateDate,
DATEDIFF (HOUR,a.InceptionDate,f.InceptionDate)AS DateDifference
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Comments AS f ON a.ComplaintID = f.ComplaintID
WHERE (f.Comment = N'Status changed from Pending To Open')
ORDER BY f.InceptionDate ASC

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 03:59:24

"How to find the average time for each department"

Basically


SELECT Department, AVG (DATEDIFF (HOUR,a.InceptionDate,f.InceptionDate)AS DateDifference)
FROM list of tables
GROUP BY Department

Kristen

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-09-06 : 04:37:10

quote:
Originally posted by Kristen

"How to find the average time for each department"

Basically

SELECT Department, AVG (DATEDIFF (HOUR,a.InceptionDate,f.InceptionDate)AS DateDifference)
FROM list of tables
GROUP BY Department

Kristen

I get the following....
'AVG' is not a recognized built-in function name.

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-09-06 : 04:48:17

I got it working. Needed to just add
AVG(DISTINCT DATEDIFF(HOUR, a.InceptionDate, b.InceptionDate))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-06 : 05:47:19

Oh my...

See this article and learn
http://www.sqlteam.com/article/how-to-use-group-by-with-distinct-aggregates-and-derived-tables

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 06:00:21

"I get the following....
'AVG' is not a recognized built-in function name."

Sorry, typo fixed in code above.

I very much DOUBT that you should be using DISTINCT!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources