| Author |
 Topic  |
|
|
Hommer
Aged Yak Warrior
693 Posts |
 Posted - 05/15/2012 : 15:57:41
|
Hey, everyone,
I have this select that is almost there. It gave me the avg.
Select avg(datediff(d,convert( datetime, DateAcknow , 101), convert(datetime, DateResolv, 101))) avg_days
,Subcategory
from ... where ... group by Subcategory
However, some records have DateAcknow > DateResolv, so the business logic calls if that is the case use 0, instead of negative days.
I have tried next two but none is working:
case when(datediff(d,convert(datetime, DateAcknow , 101), convert(datetime, DateResolv, 101)))<0
then avg(datediff(d,convert( datetime, DateResolv , 101), convert(datetime, DateResolv, 101))) --if ackdate is later than resolv
else avg(datediff(d,convert( datetime, DateAcknow , 101), convert(datetime, DateResolv, 101))) end avg_days
,calllog.Subcategory
avg(case when(datediff(d,convert(datetime, DateAcknow , 101), convert(datetime, DateResolv, 101)))<0
then datediff(d,convert( datetime, DateResolv , 101), convert(datetime, DateResolv, 101))
else datediff(d,convert( datetime, DateAcknow , 101), convert(datetime, DateResolv, 101))) end avg_days
,calllog.Subcategory
Thanks! |
|
|
malpashaa
Constraint Violating Yak Guru
Saudi Arabia
257 Posts |
Posted - 05/15/2012 : 18:54:15
|
Try this:
AVG(CASE WHEN CONVERT(DATETIME, DateAcknow , 101) > CONVERT(DATETIME, DateResolv, 101)
THEN 0
ELSE DATEDIFF(D, CONVERT(DATETIME, DateAcknow , 101), CONVERT(DATETIME, DateResolv, 101))
END) AS avg_days
For us, there is only the trying. The rest is not our business. ~T.S. Eliot
Muhammad Al Pasha |
 |
|
|
Hommer
Aged Yak Warrior
693 Posts |
Posted - 05/17/2012 : 10:53:47
|
What works!
Thank you!!! |
|
|
| |
Topic |
|
|
|
how to mix avg() with case
