How to use IS NOT NULL on a case when?

I have the below SQL query in SSMS where I want to exclude NULL rows that show up in the ‘intercompany budget’ columns.

I’ve tried to search for answers and putting a WHERE clause for 'Intercompany Budget' IS NOT NULL, like in the query below, but NULL rows still show up.

Any suggestions? Thanks!

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) <> 0
ORDER BY 
    one.PROJECT_CODE

Answer

'Intercompany Budget' IS NOT NULL can never be true, 'Intercompany Budget' is a literal string and thus doesn’t have the value NULL. This is actually one reason why using literal strings for aliases is a bad habit, as it causes misunderstandings like this. 'Intercompany Budget' doesn’t refer to the column with the alias defined using AS 'Intercompany Budget'; it’s literally a literal string. It’s only in aliasing that the syntax is (unfortunately) accepted.

If you must use aliases that need to be delimit identified, then use the dialect’s delimit identifier, brackets ([]) in T-SQL, or the ANSI delimit identifier, double quotes ("). Ideally, however, don’t use aliases/names that require delimit identification at all. I tend to use PascalCase (as shown below).

As for filtering out the rows, one method would be to use a CTE:

WITH CTE AS(
    SELECT ONE.PROJECT_CODE AS CharterNumber,
           TWO.Charter_sales_amount - SUM(ONE.HOME_VALUE) AS GrossMargin,
           SUM(CASE WHEN (ONE.COSTCENTRE_CODE = 'ACS 50% GM') THEN (ONE.HOME_VALUE) END) AS IntercompanyBudget,
           (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 BudgetAdjustmentRequired
    FROM.dbo.AA_CST_CENTRE_TRANSACTION_SIMPLE_VIEW ONE
        LEFT JOIN.dbo.CFMS_Charters_FlightsAndGrossMargin_IncICFLIGHT 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'
    GROUP BY ONE.PROJECT_CODE,
             TWO.Charter_sales_amount,
             TWO.Charter_Gross_Margin
    HAVING SUM(ONE.HOME_VALUE) <> 0
    ORDER BY ONE.PROJECT_CODE)
SELECT CharterNumber,
       GrossMargin,
       IntercompanyBudget,
       Difference,
       BudgetAdjustmentRequired
FROM CTE
WHERE IntercompanyBudget IS NOT NULL;