I think I may have found a solution. It's all in the joins. I created a recursive CTE to generate a framework of end-of-month dates covering the period...

Thanks for the advice. It's been really helpful.