Nick -

I don't know if it matters, but Paul's solution doesn't quite do what you specified, as it doesn't return data for the last date in the report range when nobody was using a room.

This is a frequent problem I have when I create reports for people - you can't easily report on data that isn't there!

I started to look at an alternative approach, using a recursive CTE to generate a continuous list of dates that covered the report range (that worked a treat, btw), with the intent of then using that in a 'left-join' approach with the occupation data. But 'real work' got in the way, so I didn't get time to finish it.

Anyway - good luck with it all.

Bob