JOIN/Union Two tables, removing NULL where possible (similar to pandas concatenate)
I've tried a UNION, FULL JOIN, and SELF JOIN but none of those seem to
  produce the desired result


FULL JOIN works fine. Maybe you were joining on the wrong predicate or didn't coalesce the results from the ID and Date columns (done with ISNULL below)? 

db &lt;> fiddle

SELECT ISNULL(T1.[ID], T2.[ID]) AS [ID],
       ISNULL(T1.Date, T2.Date) AS Date,
       [FloorRating],
       [CeilingRating],
       [WindowRating],
       [FanRating]
FROM   Table1 T1
       FULL JOIN Table2 T2
              ON T1.[ID] = T2.[ID]
                 AND T1.[Date] = T2.[Date]
ORDER  BY [ID],
          Date