SQL Server Performance Forum – Threads Archive
A Little More Help
My previous post results solved one part of the query. Here is the last part that is not working. I have stripped the query down to make it easier to work on. SELECT DISTINCTCOUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 0 THEN master.number ELSE 0 END) AS ‘#_Accts_This_Month’,
COUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 1 THEN master.number ELSE 0 END) AS ‘#_Accts_This_Month’
FROM master What I need is to show how many accounts received for current month and how many for previous month. This query works for a money field I am using with the (SUM) argument but not the (COUNT) argument for the master.number column.
You dont need ELSE part when using count SELECT DISTINCT
COUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 0 THEN master.number END) AS ‘#_Accts_This_Month’,
COUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 1 THEN master.number END) AS ‘#_Accts_This_Month’
FROM master or Use 1 in place of column
SELECT DISTINCT
COUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 0 THEN 1 ELSE 0 END) AS ‘#_Accts_This_Month’,
COUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 1 THEN 1 ELSE 0 END) AS ‘#_Accts_This_Month’
FROM master
Madhivanan Failing to plan is Planning to fail
If you want to ignore the row for your aggregate calculation, use 0 when doing a SUM, and use NULL when doing a COUNT: SUM(CASE WHEN … THEN 1 ELSE 0 END) COUNT(CASE WHEN … THEN 1 ELSE NULL END)
For COUNT I prefer using SUM(CASE WHEN .. 1 ELSE 0 END) method as COUNT(CASE WHEN .. 1 ELSE NULL END) will give warning for Aggregate function [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
Thanks for the help!
Trying it now. Our customer will be plaesed to see this report when done.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mstopkey</i><br /><br />Thanks for the help!<br />Trying it now. Our customer will be plaesed to see this report when done.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
Fantasitc!!!!! Thanks for the tutoring.
FYI, thats called Cross-tab Reports [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />For COUNT I prefer using SUM(CASE WHEN .. 1 ELSE 0 END) method as COUNT(CASE WHEN .. 1 ELSE NULL END) will give warning for Aggregate function [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
![Wink ;) ;)](styles/default/xenforo/smilies/wink.png)
]]>