How to Efficiently Generate Date Ranges for Multiple Guests in SQL Server?

SQL Server date range generation

Question:

Although the prompt involves generating a date range, it seems more focused on creating a table where each row represents each day of a guest's stay. Specifically, given a guest's name, check-in date, and check-out date, the goal is to generate a table in the following format:

('Bob', 7/14), ('Bob', 7/15), ('Bob', 7/16), ('Bob', 7/17)

Efficient solution:

The following query is considered an efficient method for this specific purpose and may perform better than using a dedicated lookup table:

<code class="language-sql">DECLARE @start DATE, @end DATE;
SELECT @start = '20110714', @end = '20110717';

;WITH n AS 
(
  SELECT TOP (DATEDIFF(DAY, @start, @end) + 1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects
)
SELECT 'Bob', DATEADD(DAY, n-1, @start)
FROM n;</code>

Result:

客人	日期
Bob	2011-07-14
Bob	2011-07-15
Bob	2011-07-16
Bob	2011-07-17

Collection extension:

This technique can be extended to a dataset using the following query:

<code class="language-sql">DECLARE @t TABLE
(
    会员 NVARCHAR(32), 
    入住日期 DATE, 
    退房日期 DATE
);

INSERT @t SELECT N'Bob', '20110714', '20110717'
UNION ALL SELECT N'Sam', '20110712', '20110715'
UNION ALL SELECT N'Jim', '20110716', '20110719';

;WITH [range](d,s) AS 
(
  SELECT DATEDIFF(DAY, MIN(入住日期), MAX(退房日期))+1,
    MIN(入住日期)
    FROM @t -- WHERE ?
),
n(d) AS
(
  SELECT DATEADD(DAY, n-1, (SELECT MIN(s) FROM [range]))
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects) AS s(n)
  WHERE n <= (SELECT MAX(d) FROM [range])
)
SELECT t.会员, n.d
FROM n CROSS JOIN @t AS t
WHERE n.d BETWEEN t.入住日期 AND t.退房日期;</code>

Result:

会员	日期
Bob	2011-07-14
Bob	2011-07-15
Bob	2011-07-16
Bob	2011-07-17
Sam	2011-07-12
Sam	2011-07-13
Sam	2011-07-14
Sam	2011-07-15
Jim	2011-07-16
Jim	2011-07-17
Jim	2011-07-18
Jim	2011-07-19

Simplified:

As @Dems pointed out, this query can be further simplified:

<code class="language-sql">;WITH natural AS 
(
  SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 AS val 
  FROM sys.all_objects
) 
SELECT t.会员, d = DATEADD(DAY, natural.val, t.入住日期) 
  FROM @t AS t INNER JOIN natural 
  ON natural.val <= DATEDIFF(DAY, t.入住日期, t.退房日期);</code>

The above is the detailed content of How to Efficiently Generate Date Ranges for Multiple Guests in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!