How to Generate a Date Range for Multiple Guests' Stays in SQL Server?

How to generate date range for check-in dates of multiple guests in SQL Server?

This article will introduce an efficient method to generate daily records in SQL Server for each guest during their stay. Slightly different from the title "How to generate date range in SQL Server", this method focuses more on generating daily check-in records for each guest. We use Common Table Expressions (CTEs) to achieve this.

Solution:

The following query cleverly combines the CTE and ROW_NUMBER() functions to generate a date sequence covering the entire guest's stay:

<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

Expand to multiple guests:

To accommodate multiple guests, we can use a second CTE to join the guest table with the generated date sequence:

<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 MAX(d) FROM [range])
)
SELECT t.Member, n.d
FROM n CROSS JOIN @t AS t
WHERE n.d BETWEEN t.RegistrationDate AND t.CheckoutDate;</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

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