The title doesn't quite capture what I mean, and this may be a duplicate.
Here's the long version: given a guest's name, their registration date, and their checkout date, how do I generate one row for each day that they were a guest?
Ex: Bob checks in 7/14 and leaves 7/17. I want
('Bob', 7/14), ('Bob', 7/15), ('Bob', 7/16), ('Bob', 7/17)
as my result.
Thanks!
I would argue that for this specific purpose the below query is about as efficient as using a dedicated lookup table.
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;
Results:
Bob 2011-07-14
Bob 2011-07-15
Bob 2011-07-16
Bob 2011-07-17
Presumably you'll need this as a set, not for a single member, so here is a way to adapt this technique:
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 -- 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.Member, n.d
FROM n CROSS JOIN @t AS t
WHERE n.d BETWEEN t.RegistrationDate AND t.CheckoutDate;
----------^^^^^^^ not many cases where I'd advocate between!
Results:
Member d
-------- ----------
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
As @Dems pointed out, this could be simplified to:
;WITH natural AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 AS val
FROM sys.all_objects
)
SELECT t.Member, d = DATEADD(DAY, natural.val, t.RegistrationDate)
FROM @t AS t INNER JOIN natural
ON natural.val <= DATEDIFF(DAY, t.RegistrationDate, t.CheckoutDate);
WHERE n < = (SELECT MAX()) which means that this can be even further simplified... WITH natural AS (SELECT ROW_NUMBER() OVER (ORDER BY id) - 1 AS val FROM sys.objects) SELECT t.Member, DATEADD(DAY, natural.val, t.start) FROM @t AS t INNER JOIN natural ON natural.val <= DATEDIFF(DAY, t.start, t.end) [But, even then, a straight look-up table is still going to use less CPU cycles at the very least.]
TOP against sys.all_objects. You're right that it could be simplified.
I usually do this with a trick using row_number() on some table. So:
select t.name, dateadd(d, seq.seqnum, t.start_date)
from t left outer join
(select row_number() over (order by (select NULL)) as seqnum
from t
) seq
on seqnum <= datediff(d, t.start_date, t.end_date)
The calculation for seq goes pretty fast, since no calculation or ordering is required. However, you need to be sure the table is big enough for all time spans.
If you have a "Tally" or "Numbers" table, life get's real simple for things like this.
SELECT Member, DatePresent = DATEADD(dd,t.N,RegistrationDate)
FROM @t
CROSS JOIN dbo.Tally t
WHERE t.N BETWEEN 0 AND DATEDIFF(dd,RegistrationDate,CheckoutDate)
;
Here's how to build a "Tally" table.
--===================================================================
-- Create a Tally table from 0 to 11000
--===================================================================
--===== Create and populate the Tally table on the fly.
SELECT TOP 11001
IDENTITY(INT,0,1) AS N
INTO dbo.Tally
FROM Master.sys.ALL_Columns ac1
CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
;
GO
For more information on what a "Tally" table is in SQL and how it can be used to replace While loops and the "Hidden RBAR" of reursive CTEs that count, please see the following article.
You can now use GENERATE_SERIES to build a range of values / dates like this
SELECT 'Bob', DATEADD(DAY, value, '2024-07-14')
FROM GENERATE_SERIES(0,3);
| Name | Date |
|---|---|
| Bob | 2024-07-14 00:00:00.000 |
| Bob | 2024-07-15 00:00:00.000 |
| Bob | 2024-07-16 00:00:00.000 |
| Bob | 2024-07-17 00:00:00.000 |
Further Reading: How to Expand a Range of Dates into Rows with the SQL Server Function GENERATE_SERIES
This may work for ya:
with mycte as
(
select cast('2000-01-01' as datetime) DateValue, 'Bob' as Name
union all
select DateValue + 1 ,'Bob' as Name
from mycte
where DateValue + 1 < '2000-12-31'
)
select *
from mycte
OPTION (MAXRECURSION 0)
I would create a trigger to create extra records and run it upon checkout. Alternatively, you can have a daily midnight job doing the same (if you need up-to-date info in your database).
WHERE calendar.date >= user.start_date AND calendar.date <= user.leave_dateYou CAN generate sets using loops, or recursive queries, but they are never as fast as using a look-up table.