I need help with this query. I have a table that I'm running the below query on.

SELECT COALESCE(NULLIF(reason, ''), reason) AS reason,
       COALESCE(NULLIF(count, ''), '0') AS count,
       COALESCE(NULLIF(count_date, ''), count_date) AS count_date
  from (select 'data_count' AS reason, count(*) as count, count_date 
          from temp_table 
         where count_date in ('01/02/2022' , '02/02/2022') 
         group by count_date
  UNION ALL
       select 'smith_call' AS reason, count(*) as count, count_date 
          from temp_table 
         where count_date in ('01/02/2022' , '02/02/2022') and name = 'Smith'
         group by count_date
  UNION ALL
       select 'will_call' AS reason, count(*) as count, count_date 
          from temp_table 
         where count_date in ('01/02/2022' , '02/02/2022') and name = 'Will'
         group by count_date);

I have an empty table returned as show below

+-------------+-------------+---------------+
|reason       |count        |count_date     |
+-------------+-------------+---------------+
| data_count  |20           |01/02/2022     |
+-------------+-------------+---------------+

However, I want the result to look like this.

+-------------+-------------+---------------+
|reason       |count        |count_date     |
+-------------+-------------+---------------+
| data_count  |20           |01/02/2022     |
+-------------+-------------+---------------+
| data_count  |0            |02/02/2022     |
+-------------+-------------+---------------+
| smith_call  |0            |01/02/2022     |
+-------------+-------------+---------------+
| smith_call  |0            |02/02/2022     |
+-------------+-------------+---------------+
| will_call   |0            |01/02/2022     |
+-------------+-------------+---------------+
| will_call   |0            |02/02/2022     |
+-------------+-------------+---------------+

For date where there are no records, instead of completely ommiting in the result, I want it to have '0' as count.


A possible solution is to left join on values.
Or to a Calendar reference table.

SELECT
  'data_count' AS reason
, count(t.count_date) as [count] 
, v.count_date
FROM (VALUES 
   (CAST('2022-02-01' AS DATE)), 
   ('2022-02-02')
) v(count_date) 
LEFT JOIN temp_table t
  ON t.count_date = v.count_date
GROUP BY v.count_date;