Convert Query from access

13 Jul 2006 9:13 PM

kohai

Hi,

I'm learning to use sql server ('00) and am trying to convert this query
from access
that will count the # of increases, declines, and no changes for a field.

SELECT dDate, Grp, Sum(IIf([DRet]>0,1,0)) AS Adv, Sum(IIf([DRet]<0,1,0)) AS
[Dec], Sum(IIf([DRet]=0,1,0)) AS NoChg
FROM DailyData
GROUP BY dDate, Grp
ORDER BY dDate, Grp;

Since IIF is not a function in SQL server, I'm a little stumped.

Thank you.

kohai

13 Jul 2006 9:52 PM

ML

T-SQL's equivalent to Access's IIF function is the CASE expression. Look it
up in Books Online.

SELECT dDate
           ,Grp
           ,Sum(case when [DRet]>0 then 1 else 0 end)) AS Adv
           ,Sum(case when [DRet]<0 then 1 else 0 end)) AS [Dec]
           ,Sum(case when [DRet]=0 then 1 else 0 end)) AS NoChg
           FROM DailyData
           GROUP BY dDate
                          ,Grp
           ORDER BY dDate
                           ,Grp;

ML

---
http://milambda.blogspot.com/

13 Jul 2006 9:54 PM

ML

Ah, crap!

Too many parentheses.

SELECT dDate
           ,Grp
           ,Sum(case when [DRet]>0 then 1 else 0 end) AS Adv
           ,Sum(case when [DRet]<0 then 1 else 0 end) AS [Dec]
           ,Sum(case when [DRet]=0 then 1 else 0 end) AS NoChg
           FROM DailyData
           GROUP BY dDate
                          ,Grp
           ORDER BY dDate
                           ,Grp;

ML

---
http://milambda.blogspot.com/

13 Jul 2006 10:01 PM

kohai

ML,

Thanks for the quick reply. Works like a champ!! Thank you very much.
I'll read up on the Case statement.

kohai

Show quote

"ML" wrote:

> T-SQL's equivalent to Access's IIF function is the CASE expression. Look it
> up in Books Online.
>
> SELECT dDate
>            ,Grp
>            ,Sum(case when [DRet]>0 then 1 else 0 end)) AS Adv
>            ,Sum(case when [DRet]<0 then 1 else 0 end)) AS [Dec]
>            ,Sum(case when [DRet]=0 then 1 else 0 end)) AS NoChg
>            FROM DailyData
>            GROUP BY dDate
>                           ,Grp
>            ORDER BY dDate
>                            ,Grp;
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

13 Jul 2006 10:05 PM

ML

And remember this newsgroup. :)

ML

---
http://milambda.blogspot.com/