LutzM (1/10/2012)
an the missing last date in Pauls code: gaps in data are covered by a dynamic calendar table in Pauls code. To include the last date, just change
SELECT TOP(DATEDIFF(DAY, R.CheckIn, R.CheckOut))
to
SELECT TOP(DATEDIFF(DAY, R.CheckIn, R.CheckOut)+1)
Off-by-one errors, my speciality 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi