I have done this many times. My preferred solution was to create a recursive CTE beginning with the desired start date and ending with the desired end date. I o

I have done this many times. My preferred solution was to create a recursive CTE beginning with the desired start date and ending with the desired end date. I often based these on MIN and MAX subqueries, but obviously you could also pick arbitrary dates. You can use days, months, weeks, years, or whatever you wanted as the "increment" in the CTE using the DATEADD function. Then (for nulls) you simply LEFT JOIN your CTE with the desired date part (aggregated) of your table. This gives you your first answer, and a simply filter will get you your second answer. It's also common for people to just create all these date tables beforehand as actual, materialized tables. In my opinion, this is less elegant (what happens in 2101?! Somebody had better remember to add to the table!) but it naturally works just as well and probably saves some perf.