I want to get all dates between two dates arrival date and leaving date.
I tried that recursive query but it didn't work.
;with dates as (
SELECT GuestID, ArrivalDate as reserveddate
from dbo.Guest
union all
SELECT GuestID, dateadd (day,1,dbo.Guest. ArrivalDate) as reserveddate
from dbo.Guest
where dateadd (day,1,dbo.Guest. ArrivalDate) <dbo.Guest.leavingate
)
SELECT *
from dates
option (maxrecursion 0)
You need recursive CTE:
DECLARE @arrival_date date = '2016-01-01',
@leaving_date date = '2016-02-01'
;WITH cte AS (
SELECT @arrival_date as date_
UNION ALL
SELECT CAST(DATEADD(day,1,date_) as date)
FROM cte
WHERE date_ < @leaving_date
)
SELECT *
FROM cte
OPTION (MAXRECURSION 0)
Output:
date_
2016-01-01
2016-01-02
2016-01-03
...
2016-01-30
2016-01-31
2016-02-01
EDIT1
Based on your sample:
;WITH cte AS (
SELECT GuestID, CAST(ArrivalDate as date) as date_
FROM Guests
UNION ALL
SELECT c.GuestID, CAST(DATEADD(day,1,date_) as date)
FROM cte c
INNER JOIN Guests g
ON g.GuestID = c.GuestID
WHERE date_ < g.LeavingDate
)
SELECT *
FROM cte
ORDER BY GuestID, date_
OPTION (MAXRECURSION 0)
EDIT2
;WITH Guests AS (
SELECT 1 as GuestID,
'2016-01-01' ArrivalDate,
'2016-01-05' LeavingDate
UNION ALL
SELECT 2 ,
'2016-06-17',
'2016-06-20'
), cte AS (
SELECT GuestID, CAST(ArrivalDate as date) as date_
FROM Guests
UNION ALL
SELECT c.GuestID, CAST(DATEADD(day,1,date_) as date)
FROM cte c
INNER JOIN Guests g
ON g.GuestID = c.GuestID
WHERE date_ < g.LeavingDate
)
SELECT *
FROM cte
ORDER BY GuestID, date_
OPTION (MAXRECURSION 0)
Output:
GuestID date_
1 2016-01-01
1 2016-01-02
1 2016-01-03
1 2016-01-04
1 2016-01-05
2 2016-06-17
2 2016-06-18
2 2016-06-19
2 2016-06-20
My preference for this type of thing is to use a tally table. I keep a view on every system that looks like this.
create View [dbo].[cteTally] as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
GO
This way I have a tally table at my fingertips at all times. You can read more about tally tables and how they replace loops here. http://www.sqlservercentral.com/articles/T-SQL/62867/
To solve your current problem this becomes fairly simple. First we need a table and some sample data.
create table #Guest
(
GuestID int identity
, ArrivalDate date
, DepartureDate date
)
insert #Guest
select '2016-06-01', '2016-06-15' union all
select '2016-07-01', '2016-07-12'
Here is where the power of the tally table really shows. To produce the output for your challenge is as simple as this code.
SELECT GuestID
, ArrivalDate
, DepartureDate
, DATEADD(Day, N - 1, ArrivalDate) as EachDate
from #Guest g
join cteTally t on t.N <= DATEDIFF(day, ArrivalDate, DepartureDate) + 1
order by GuestID
, EachDate
The other big advantage of this type of approach over a recursive cte is that you are using what is known as a triangular join and can cause some performance challenges. Here is some more info on triangular joins. http://www.sqlservercentral.com/articles/T-SQL/61539/
Try This
DECLARE @DateFrom DateTime = DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())-5,0)
DECLARE @DateTo DateTime = CONVERT(DATE,GETDATE())
;WITH Numbers (Number) AS (
SELECT row_number() OVER (ORDER BY object_id)
FROM sys.all_objects
)
SELECT dateadd(DAY, number-1, @DateFrom)
FROM Numbers
WHERE number <= datediff(DAY, @DateFrom-1, @DateTo-1)
you can change the change the date interval by changing DAY to WEEK, MONTH, YEAR etc. in the select dateadd and the where datediff
datestodbo.Guestin recursive part of query.