how to mix avg() with case - 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)

how to mix avg() with case

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2012-05-15 : 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

264 Posts

Posted - 2012-05-15 : 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

808 Posts

Posted - 2012-05-17 : 10:53:47

What works!

Thank you!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources