There is an assignment schedule where several entities are assigned on a daily basis, in ID/DATE simple fashion.
I need to report all IDS per every date in the work dates calendar,
including instances where no assignment exists:
It works OK, but I suspect my solution could (and should) be simplified.
So two things strikes me as odd here.
1) If the result set is supposed to show whether there is an assignment for
a certain ID on a certain date, I would expect three columns: date, id and a yes/no column. Now there are multiple rows with NULL on the same date if
there is no assignment at all.
2) I would expect there to be a table to hold the IDs as such. In that case
the SELECT DISTINCT in the CTE could be replace with a straight SELECT
from that table.
Else, this is the typical pattern for this type of query.