title: How to use IS NOT NULL on a case when?
body: <p>I have the below SQL query in SSMS where I want to exclude <code>NULL</code> rows that show up in the 'intercompany budget' columns.</p>
  <p>I've tried to search for answers and putting a <code>WHERE</code> clause for <code>'Intercompany Budget' IS NOT NULL</code>, like in the query below, but <code>NULL</code> rows still show up.</p>
  <p>Any suggestions? Thanks!</p>
  <pre><code>SELECT 
      ONE.PROJECT_CODE AS 'Charter Number',
      TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE) AS 'Gross Margin',
      SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE) END) AS 'Intercompany Budget',
      (TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE)) - SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE) ELSE 0 END) AS 'Difference',
      ((TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE)) - SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE) ELSE 0 END)) / 2 AS 'Budget Adjustment Required'
  FROM 
      dbo.AA_CST_CENTRE_TRANSACTION_SIMPLE_VIEW AS ONE 
  LEFT JOIN 
      dbo.CFMS_Charters_FlightsAndGrossMargin_IncICFLIGHT AS TWO ON ONE.PROJECT_CODE = TWO.Charter_Number
  WHERE 
      ONE.CT_DEADLINE BETWEEN '2/1/2021' AND '5/15/2021'
      AND ONE.CT_TRANTYPE= 'MSC'
      AND 'Intercompany Budget' IS NOT NULL
  GROUP BY 
      ONE.PROJECT_CODE, TWO.Charter_sales_amount, TWO.Charter_Gross_Margin
  HAVING 
      SUM(ONE.HOME_VALUE) &lt;&gt; 0
  ORDER BY 
      one.PROJECT_CODE
  </code></pre>