There may be other ways but apart from using a dedicated number table rather than spt_values, this is exactly the way I do it.

I am intrigued by the comment that this looks too complicated. I would like to see a simpler example.

In short, create a calendar table that holds the date / time / base value range and then embellish it with the actual data. How can this be achieved any simpler? Please share.

A good article in my opinion.

Dave