Return a column per date in a range
You can use the PIVOT
function to perform this query. My answer will include both a Static and dynamic version because sometimes it is easier to understand it using a static version.
A Static Pivot is when you hard-code all of the values that you want to transform into columns.
-- first into into a #temp table the list of dates that you want to turn to columns
;with cte (datelist, maxdate) as
(
select min(arrivaldate) datelist, max(departuredate) maxdate
from BookingsPerPerson
union all
select dateadd(dd, 1, datelist), maxdate
from cte
where datelist < maxdate
)
select c.datelist
into #tempDates
from cte c
select *
from
(
select b.person_id, b.arrivaldate, b.departuredate,
d.datelist,
convert(CHAR(10), datelist, 120) PivotDate
from #tempDates d
left join BookingsPerPerson b
on d.datelist between b.arrivaldate and b.departuredate
) x
pivot
(
count(datelist)
for PivotDate in ([2012-01-01], [2012-01-02], [2012-01-03],
[2012-01-04], [2012-01-05], [2012-01-06] , [2012-01-07])
) p;
Results (See SQL Fiddle With Demo):
PERSON_ID | ARRIVALDATE | DEPARTUREDATE | 2012-01-01 | 2012-01-02 | 2012-01-03 | 2012-01-04 | 2012-01-05 | 2012-01-06 | 2012-01-07
=====================================================================================================================================
123456 | 2012-01-01 | 2012-01-04 | 1 | 1 | 1 | 1 | 0 | 0 | 0
213415 | 2012-01-02 | 2012-01-07 | 0 | 1 | 1 | 1 | 1 | 1 | 1
The dynamic version will generate the list of values to transform to columns:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
;with cte (datelist, maxdate) as
(
select min(arrivaldate) datelist, max(departuredate) maxdate
from BookingsPerPerson
union all
select dateadd(dd, 1, datelist), maxdate
from cte
where datelist < maxdate
)
select c.datelist
into #tempDates
from cte c
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(CHAR(10), datelist, 120))
from #tempDates
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT person_id, arrivaldate, departuredate, ' + @cols + ' from
(
select b.person_id, b.arrivaldate, b.departuredate,
d.datelist,
convert(CHAR(10), datelist, 120) PivotDate
from #tempDates d
left join BookingsPerPerson b
on d.datelist between b.arrivaldate and b.departuredate
) x
pivot
(
count(datelist)
for PivotDate in (' + @cols + ')
) p '
execute(@query)
The results are the same (see SQL Fiddle With Demo):
PERSON_ID | ARRIVALDATE | DEPARTUREDATE | 2012-01-01 | 2012-01-02 | 2012-01-03 | 2012-01-04 | 2012-01-05 | 2012-01-06 | 2012-01-07
=====================================================================================================================================
123456 | 2012-01-01 | 2012-01-04 | 1 | 1 | 1 | 1 | 0 | 0 | 0
213415 | 2012-01-02 | 2012-01-07 | 0 | 1 | 1 | 1 | 1 | 1 | 1
I'm old school, and find CASE
easier to work out in my head than PIVOT
. I'm sure bluefeet will show up shortly and put me to shame, but in the meantime you can play with this dynamic SQL query. Assuming your table stores DATE
and not DATETIME
(or even worse, VARCHAR
):
USE tempdb;
GO
CREATE TABLE dbo.a
(
Person_Id INT,
ArrivalDate DATE,
DepartureDate DATE
);
INSERT dbo.a SELECT 123456, '2012-01-01', '2012-01-04'
UNION ALL SELECT 213415, '2012-01-02', '2012-01-07';
DECLARE @sql NVARCHAR(MAX) = N'SELECT Person_Id';
;WITH dr AS
(
SELECT MinDate = MIN(ArrivalDate),
MaxDate = MAX(DepartureDate)
FROM dbo.a
),
n AS
(
SELECT TOP (DATEDIFF(DAY, (SELECT MinDate FROM dr), (SELECT MaxDate FROM dr)) + 1)
d = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id])-1,
(SELECT MinDate FROM dr))
FROM sys.all_objects
)
SELECT @sql += ',
' + QUOTENAME(d) + ' = CASE WHEN ''' + CONVERT(CHAR(10), d, 120)
+ ''' BETWEEN ArrivalDate AND DepartureDate THEN ''1'' ELSE '''' END' FROM n;
SELECT @sql += ' FROM dbo.a;'
EXEC sp_executesql @sql;
GO
DROP TABLE dbo.a;
One of the very few cases, BTW, where I could justify using BETWEEN
for date range queries.