SQL Server Performance

A Little More Help

Discussion in 'SQL Server Reporting Services' started by mstopkey, Jun 22, 2007.

  1. mstopkey New Member

    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.
  2. Madhivanan Moderator

    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
  3. Adriaan New Member

    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)
  4. Madhivanan Moderator

    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=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  5. mstopkey New Member

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

    <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=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  7. mstopkey New Member

    Fantasitc!!!!! Thanks for the tutoring.
  8. Madhivanan Moderator

    FYI, thats called Cross-tab Reports [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  9. Adriaan New Member

    <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=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">You mean this one:<br />"Warning: Null value is eliminated by an aggregate or other SET operation."<br /><br />That message only comes up if you do COUNT(&lt;column&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /> and there are rows where that column contains null. To avoid the message, include &lt;column&gt; IS NOT NULL in the WHERE clause.<br /><br />With the CASE expression, the COUNT is not evaluating a column, so no value has been eliminated.

Share This Page