Bob Cullen-434885 (1/10/2012)

---

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

    SELECT TOP(DATEDIFF(DAY, R.CheckIn, R.CheckOut))

    SELECT TOP(DATEDIFF(DAY, R.CheckIn, R.CheckOut)+1)

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)

Bob Cullen-434885 (1/10/2012)

---

I started to look at an alternative approach, using a recursive CTE to generate a continuous list of dates that covered the report range

drew.allen (1/11/2012)

---

Jeff Moden has an article that shows how badly recursive CTEs can perform for certain types of tasks and it sounds like yours may fall into that category. Check out his article Hidden RBAR: Counting with Recursive CTE's

SQL Kiwi (1/11/2012)

---

It's pretty tough to imagine a list of dates long enough to make any practical difference regarding performance here. Before anyone says it, yes I know that it often makes sense to code for the 10-million row case, but that would take us into the year 4749 here. A mere 4000 rows (150ms from Jeff's article graph) is more than enough to cover a ten-year period.

I congratulate Bob for finding my error and pursuing an alternative line of enquiry. My concern is that we shouldn't be too keen in promoting The One True Way to generate numbers such that we end up stifling innovation and exploration.

drew.allen (1/11/2012)

---

If you read the article, you'll see that even the mere 4000 row CTE has a significant performance impact. That's how badly recursive CTEs can perform.

SET STATISTICS TIME ON;
WITH R AS (SELECT 1 AS N UNION ALL SELECT R.N + 1 FROM R WHERE R.N <= 4000)
SELECT * INTO #bucket FROM R OPTION (MAXRECURSION 0)
SET STATISTICS TIME OFF;

SQL Kiwi (1/11/2012)

---

You might have missed that I quoted the performance impact directly from the article "(150ms from Jeff's article graph)", which is one I have read many times (see the article's discussion thread for my comments). 150ms is not 'significant' in the current context, and it is 46ms on my laptop in any case: