Início / dba / Perguntas / 25809
Accepted
Federico Giust
Asked: 2012-10-12 05:24:23 +0800 CST

Retornar uma coluna por data em um intervalo

  • 772

Digamos que eu tenha a Tabela A: BookingsPerPerson

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

O que eu preciso alcançar com uma visão é o seguinte:

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

O sistema é para eventos, então cada reserva de hotel pode levar de 1 a 15 dias, mas não mais que isso. Todas as idéias seriam muito apreciadas.

sql-server sql-server-2008

3 respostas

  1. Best Answer

    Você pode usar a PIVOTfunção para realizar esta consulta. Minha resposta incluirá uma versão estática e dinâmica, porque às vezes é mais fácil entendê-la usando uma versão estática.

    Um Pivô Estático é quando você codifica todos os valores que deseja transformar em colunas.

    -- 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;

    Resultados (consulte 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

    A versão dinâmica irá gerar a lista de valores para transformar em colunas:

    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)

    Os resultados são os mesmos (veja 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
    • 29

  2. Eu sou da velha escola, e acho CASEmais fácil trabalhar na minha cabeça do que PIVOT. Tenho certeza de que o bluefeet aparecerá em breve e me deixará envergonhado, mas enquanto isso você pode brincar com essa consulta SQL dinâmica. Assumindo que sua mesa armazena DATEe não DATETIME(ou pior ainda, 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;

    Um dos poucos casos, BTW, onde eu poderia justificar o uso BETWEENde consultas de intervalo de datas.

    • 8

  3. Que tal isso para gerar uma lista de datas

    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
    • 0

relate perguntas