Return a column per date in a range

Let’s say I have Table A: BookingsPerPerson

Person_Id    ArrivalDate    DepartureDate
123456       2012-01-01     2012-01-04
213415       2012-01-02     2012-01-07

What I need to achieve with a view is the following:

Person_Id    ArrivalDate    DepartureDate    Jan-01    Jan-02    Jan-03    Jan-04    Jan-05    Jan-06    Jan-07
123456       2012-01-01     2012-01-04       1         1         1         1
213415       2012-01-02     2012-01-07                 1         1         1         1         1         1

The system is for events, so each hotel booking could take anything between 1 to 15 days but no more than that. Any ideas would be very much appreciated.

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.

How about this to generate a list of dates

declare @Date01 as smalldatetime
declare @Date02 as smalldatetime
select @Date01 = min(periodstart), @Date02 = max(periodend)
    from BookingTable

declare @DateDiff as int
select @DateDiff = (select DATEDIFF(DAY, @Date01, @Date02))
;

WITH Tally (N) AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
SELECT DATEADD(day, N, @Date01)
FROM Tally
where N <= @DateDiff