| Author |
 Topic  |
|
|
evanburen
Posting Yak Master
167 Posts |
 Posted - 06/16/2014 : 10:09:00
|
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
30421 Posts |
Posted - 06/16/2014 : 10:43:43
|
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 |
 |
|
|
evanburen
Posting Yak Master
167 Posts |
Posted - 06/16/2014 : 10:51:37
|
Thanks but I'm getting back this
Invalid column name 'timeofreview'
Invalid column name 'endtimeofreview' |
|
|
|
sz1
Aged Yak Warrior
United Kingdom
555 Posts |
Posted - 06/16/2014 : 11:26:38
|
Check your column names?
We are the creators of our own reality! |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
4614 Posts |
Posted - 06/16/2014 : 11:35:39
|
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 |
|
|
|
sz1
Aged Yak Warrior
United Kingdom
555 Posts |
Posted - 06/16/2014 : 11:38:51
|
Yep missing From
We are the creators of our own reality! |
|
|
|
evanburen
Posting Yak Master
167 Posts |
Posted - 06/16/2014 : 13:26:25
|
| working now. thanks a lot. |
|
|
|
ScottPletcher
Aged Yak Warrior
USA
550 Posts |
Posted - 06/16/2014 : 13:33:04
|
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 |
|
|
| |
Topic |
|
AVG With DATEDIFF