MAX(Case When) multiple Where statements

Question

I am attempting to add a secondary condition to my statement below in order to only show records that are NOT NULL in the Application Count Column. I am assuming since I am creating the Alias that it is the reason this is not working, but even if I put the Actual name I still see all of the Nulls.

Where am I going wrong?

Here is the Code that works but shows tons of Null Fields in Column Application Count and Create Date, Name is populated in each row.

SELECT Row_number() over(order by data_point_group_key) 'data_id', 
       report_id, section_group, section_name, data_point_group_key,
       MAX(CASE WHEN data_point = 'Application Count' THEN data_point_value END) as 'ApplicationCount',
       MAX(CASE WHEN data_point = 'Create Date' THEN data_point_value END) as 'CreateDate',
       MAX(CASE WHEN data_point = 'Name' THEN data_point_value END) as 'SolutionName'
FROM tblReportData 
WHERE report_id = (select max(report_id) from tblReportData)
group by report_id,section_group, section_name,data_point_group_key

Current output:

+----------------------+------------------+------------+--------------+
| data_point_group_key | ApplicationCount | CreateDate | SolutionName |
+----------------------+------------------+------------+--------------+
| 1234564646464        |      NULL        |   NULL     | Sol Name     |
+----------------------+------------------+------------+--------------+
| 1234564646464        |      8           |   01-01-18 | New Name     |
+----------------------+------------------+------------+--------------+
| 132512512            |      NULL        |   NULL     | Old Name     |
+----------------------+------------------+------------+--------------+

Here I add the second Where filter and am still returned all of the Nulls

SELECT Row_number() over(order by data_point_group_key) 'data_id', 
       report_id, section_group, section_name, data_point_group_key,
       MAX(CASE WHEN data_point = 'Application Count' THEN data_point_value END)as 'ApplicationCount',
       MAX(CASE WHEN data_point = 'Create Date' THEN data_point_value END)as 'CreateDate',
       MAX(CASE WHEN data_point = 'Name' THEN data_point_value END)as 'SolutionName'
FROM tblReportData 
WHERE report_id = (select max(report_id) from tblReportData) AND ('ApplicationCount' IS NOT NULL OR 'CreateDate' IS NOT NULL)
group by report_id,section_group, section_name,data_point_group_key

Hopeful Output:

+----------------------+------------------+------------+--------------+
| data_point_group_key | ApplicationCount | CreateDate | SolutionName |
+----------------------+------------------+------------+--------------+
| 1234564646464        |      8           |   01-01-18 | New Name     |
+----------------------+------------------+------------+--------------+

I'm NOT getting errors when it returns values, so I assume the issue is that I am choosing the wrong field because of the Alias.

Answer 1

If I'm understanding your request correctly you just want to ignore the rows in the results that have NULL in then. You could make that a CTE:

WITH REPORT_CTE AS (
    SELECT Row_number() over(order by data_point_group_key) 'data_id', 
           report_id, section_group, section_name, data_point_group_key,
           MAX(CASE WHEN data_point = 'Application Count' THEN data_point_value END)as 'ApplicationCount',
           MAX(CASE WHEN data_point = 'Create Date' THEN data_point_value END)as 'CreateDate',
           MAX(CASE WHEN data_point = 'Name' THEN data_point_value END)as 'SolutionName'
    FROM tblReportData 
    WHERE report_id = (select max(report_id) from tblReportData)
    group by report_id,section_group, section_name,data_point_group_key
)
SELECT * FROM REPORT_CTE
WHERE ApplicationCount is not null and CreateDate is not null and SolutionName is not null

Another option would be to include tblReportData three times with inner joins, but because you're using group by with columns not in the question I'm not sure how to formulate that or if your grouping is just so you can pick out the fields with the CASE and MAX...

Another option might be to use a pivot

Answer 2

Update

While my observation is correct, the proposed solution is not. You got the SolutionName column null because the where clause already filtered out all data_point values that are not 'Application Count' or 'Create Date'.

An easy fix would be to wrap the original query in a common table expression and then select from it, with a where clause based on it's columns:

;WITH cte AS
(
    SELECT Row_number() over(order by data_point_group_key) 'data_id', 
           report_id, section_group, section_name, data_point_group_key,
           MAX(CASE WHEN data_point = 'Application Count' THEN data_point_value END) as 'ApplicationCount',
           MAX(CASE WHEN data_point = 'Create Date' THEN data_point_value END) as 'CreateDate',
           MAX(CASE WHEN data_point = 'Name' THEN data_point_value END) as 'SolutionName'
    FROM tblReportData 
    WHERE report_id = (select max(report_id) from tblReportData) 
    AND (data_point = 'Application Count' OR data_point = 'Create Date')
    GROUP BY report_id,section_group, section_name,data_point_group_key
)

SELECT *
FROM cte
WHERE ApplicationCount IS NOT NULL
OR CreateDate IS NOT NULL

First version

'ApplicationCount' and 'CreateDate' are string literals, they can't be null.

You need to check if the data_point column contains these values:

SELECT Row_number() over(order by data_point_group_key) 'data_id', 
       report_id, section_group, section_name, data_point_group_key,
       MAX(CASE WHEN data_point = 'Application Count' THEN data_point_value END)as 'ApplicationCount',
       MAX(CASE WHEN data_point = 'Create Date' THEN data_point_value END)as 'CreateDate',
       MAX(CASE WHEN data_point = 'Name' THEN data_point_value END)as 'SolutionName'
FROM tblReportData 
WHERE report_id = (select max(report_id) from tblReportData) 
AND (data_point = 'Application Count' OR data_point = 'Create Date')
group by report_id,section_group, section_name,data_point_group_key