• to show full set of two tables

    From Erland Sommarskog@21:1/5 to M.G. on Tue Jul 26 11:51:23 2016
    M.G. (michael@gurfinkel.us) writes:
    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.
    Any ideas?

    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.

    Erland Sommarskog, Stockholm, esquel@sommarskog.se

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)