Display a 0 value if null in grouping, but only under a specific condition in SQL

Question

I am using SSRS 2008, and have a large query I inherited with multiple sales reps as a parameter. I wanted to create a total row with groupings of total closed sales by month. In the body of the large query I used a CASE statement for creating those monthly totals:

CASE WHEN C.ClosingDate BETWEEN '1/1/2020' AND '1/31/2020' THEN '1/2020' 
CASE WHEN C.ClosingDate BETWEEN '2/1/2020' AND '2/28/2020' THEN '2/2020'
END AS 'Monthly_Total'

My grouping by Monthly_Total, then correctly shows a total sales amount for each month, in order.

My problem is when I select say one particular sales agent, and that person does not have a closed sale in January, there is no value appearing in the grouping. But if I select all agents for that month, then a value does appear. So I specifically need a value to appear as 0, even if there is no data based on that agents sales.

Answer 1

try use WHEN C.ClosingDate IS NULL THEN for handle null when not exist values

example:

CASE 
 WHEN C.ClosingDate IS NULL THEN '0' 
 WHEN C.ClosingDate BETWEEN '1/1/2020' AND '1/31/2020' 
  THEN '1/2020' 
 WHEN C.ClosingDate BETWEEN '2/1/2020' AND '2/28/2020' 
  THEN '2/2020' 
 ELSE '0'
END AS 'Monthly_Total'