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 DISTINCT
COUNT(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=’

‘ />] Madhivanan Failing to plan is Planning to fail

Thanks for the help!
Trying it now. Our customer will be plaesed to see this report when done.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by mstopkey Thanks for the help! Trying it now. Our customer will be plaesed to see this report when done. <hr height="1" noshade id="quote"></blockquote id="quote"> Yes [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Fantasitc!!!!! Thanks for the tutoring.

FYI, thats called Cross-tab Reports [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan 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=’

‘ />] Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote">You mean this one: "Warning: Null value is eliminated by an aggregate or other SET operation." That message only comes up if you do COUNT(<column&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> and there are rows where that column contains null. To avoid the message, include <column> IS NOT NULL in the WHERE clause. With the CASE expression, the COUNT is not evaluating a column, so no value has been eliminated.

]]>