Replace NULL with the Date in the WHERE clause in Mysql

Question

So, in order to get 0s in my count column I have tried out this query which works.

SELECT b.TXT_RECORD_DATE, a.TXT_CALL_TYPE, 
      SUM(CASE 
        WHEN b.TXT_CALL_TYPE IS NOT NULL
          THEN 1
        ELSE 0
      END) AS StatusCount
FROM (
  SELECT DISTINCT TXT_CALL_TYPE
  FROM CDR
  ) a
LEFT JOIN CDR b ON a.TXT_CALL_TYPE = b.TXT_CALL_TYPE AND b.TXT_RECORD_DATE IN ('2022-04-12', '2022-04-13','2022-04-14')
GROUP BY a.TXT_CALL_TYPE, b.TXT_RECORD_DATE;

But it shows [NULL]s in the columns where StatusCount = 0

So my question is that is there a way to actually assign the date that is being currently searched instead of the [NULL]

The current result looks like this

TXT_RECORD_DATE	TXT_CALL_TYPE	StatusCount
	BRD	0
2022-04-12	Busy Call	9
	IDIN	0
	IDOT	0
2022-04-12	Incoming - Missed Call	133
2022-04-13	Incoming - Missed Call	38
2022-04-14	Incoming - Missed Call	29
	ITRS	0
	IVIN	0
2022-04-12	IVOT	21
2022-04-13	IVOT	27
2022-04-14	IVOT	20
	PIN	0
2022-04-12	POT	1
2022-04-12	PTRS	19
2022-04-13	PTRS	4
2022-04-14	PTRS	14

Sorry if I forgot anything or was not clear. I'm writing to you in the middle of the night and is so tired. Thanks Anyways. You guys are always awesome.

Answer 1

You need to generate a list of the dates you are interested in and CROSS JOIN that to the list of call types; then you can LEFT JOIN that to the call records to get the result you want. In MariaDB you can make use of the sequence storage engine to easily generate a list of the dates:

SELECT d.TXT_RECORD_DATE, a.TXT_CALL_TYPE, 
      SUM(CASE 
        WHEN b.TXT_CALL_TYPE IS NOT NULL
          THEN 1
        ELSE 0
      END) AS StatusCount
FROM (
  SELECT '2022-04-12' + INTERVAL (seq) DAY AS TXT_RECORD_DATE
  FROM seq_0_to_2
) d
CROSS JOIN (
  SELECT DISTINCT TXT_CALL_TYPE
  FROM CDR
  WHERE TXT_CALL_TYPE IS NOT NULL
) a
LEFT JOIN CDR b ON a.TXT_CALL_TYPE = b.TXT_CALL_TYPE AND d.TXT_RECORD_DATE = b.TXT_RECORD_DATE
GROUP BY d.TXT_RECORD_DATE, a.TXT_CALL_TYPE

Demo on dbfiddle