SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 AVG With DATEDIFF
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

evanburen
Posting Yak Master

155 Posts

Posted - 06/16/2014 :  10:09:00  Show Profile  Reply with Quote
I'm getting some odd results with trying to find the average of the number of minutes between two date values.

Here is a list of records showing the number of minutes between two values which is correct.

select top 10
DATEDIFF(minute, timeofreview, endtimeofreview) AS CallMins
from calls



But what I want is the average of these ten and this produces the results below.

select top 10
AVG(DATEDIFF(minute, timeofreview, endtimeofreview)) as AVGDiff
from calls


Edited by - evanburen on 06/16/2014 10:43:46

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 06/16/2014 :  10:43:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
with ctesource as (select top(10) datediff(minute, timeofreview, endtimeofreview) as avgdiff)
select avg(avgdiff) from ctesource


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

evanburen
Posting Yak Master

155 Posts

Posted - 06/16/2014 :  10:51:37  Show Profile  Reply with Quote
Thanks but I'm getting back this

Invalid column name 'timeofreview'
Invalid column name 'endtimeofreview'
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 06/16/2014 :  11:26:38  Show Profile  Reply with Quote
Check your column names?

We are the creators of our own reality!
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/16/2014 :  11:35:39  Show Profile  Reply with Quote
There is no table name on the select in the CTE.

with ctesource as (select top(10) datediff(minute, timeofreview, endtimeofreview) as avgdiff from calls)
select avg(avgdiff) from ctesource
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 06/16/2014 :  11:38:51  Show Profile  Reply with Quote
Yep missing From

We are the creators of our own reality!
Go to Top of Page

evanburen
Posting Yak Master

155 Posts

Posted - 06/16/2014 :  13:26:25  Show Profile  Reply with Quote
working now. thanks a lot.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
370 Posts

Posted - 06/16/2014 :  13:33:04  Show Profile  Reply with Quote


SELECT TOP (10)
    CAST(SUM(DATEDIFF(SECOND, timeofreview, endtimeofreview)) / 60.0 / COUNT(*) AS decimal(5, 1)) AS AVGDiff
FROM calls


Edited by - ScottPletcher on 06/16/2014 13:34:19
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000