Show missing date from table in results

Question

I have one problem that it seems I can't solve, so I need your help. I have the below table of data. The ParientStatus and valueD are varchars and unfortunately, they cannot change (e.g. valueD to become datetime).

and the query:

SET @enrolled = 0, @screened = 0;
select
   MAX(IF(PatientStatus = 'Enrolled', @enrolled := @enrolled + 1, @enrolled)) AS EnrolledPatientsInitial,
   MAX(IF(PatientStatus = 'Screened', @screened := @screened + 1, @screened)) AS ScreenedPatientsInitial,
valueD                                                                   as Date
from table_name
GROUP BY valueD;

What was requested from me, is to show one column with the dates and two more with the status of the patients that were enrolled or screened. If you ran the above code in this table, you will get the:

which is correct for the data part, but the problem is that it needs to fill missing dates. For the specific example, I need to show also 2020-03 for which I have no data.

I have read several issues here in stackoverflow, but none of them helped really.

Can anyone help me with this?

Thank you very much in advance.

Generate dates list in CTE or subquery and join your output to it. — Akina, Jul 30 '20 at 05:09
*and the query* This query is incorrect logically (you're lucky if the result generated by this query is correct) - nothing determines rows processing order... — Akina, Jul 30 '20 at 05:11
Join this query output by creating a new table with only date and join on date column and make it `full outer join` to also get missing dates. — CaffeinatedCod3r, Jul 30 '20 at 05:20

Show missing date from table in results

0 Answers0