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.

In many databases you would create a function to fill the role of that "numbers table" and, effectively, map that function onto rows. That function can express whatever "richness" you need (i.e. civil calendar month, phase of the moon, etc... >smile<) that might not be easily expressed as a series.

There are date functions in most SQL packages which can create the limiting factors you're looking for, just as in procedural languages. Once you have the list of dates you're interested in, the problem becomes simple.