How to Efficiently Generate a Date Range for Multiple Guest Registrations in SQL Server?

Efficiently generate guest registration date range in SQL Server

While the title of the article is accurate, it does not fully convey the specific scenario of generating a row for each guest on each day based on the guest's check-in and check-out dates. This improved reply will delve into a technique for achieving efficiency using specialized lookup tables.

Use a dedicated lookup table

The solution using the ROW_NUMBER() function provides commendable efficiency for this particular task. The following optimized query utilizes a lookup table to generate the necessary date range:

<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>

Expand to multiple guests

To extend this technique to multiple guests, the following query can be adapted:

<code class="language-sql">DECLARE @t TABLE
(
    Member NVARCHAR(32),
    RegistrationDate DATE,
    CheckoutDate 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(RegistrationDate), MAX(CheckoutDate))+1, MIN(RegistrationDate) 
  FROM @t 
),
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 d FROM [range])
)
SELECT t.Member, n.d
FROM @t t
INNER JOIN n ON n.d BETWEEN t.RegistrationDate AND t.CheckoutDate;</code>

This modified query effectively generates the required date range for all members in the provided table.

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