SQL-сервер - динамический свод и вычисляемые столбцы

Мне нужна помощь в создании таблицы с динамическими столбцами и вычисляемыми значениями в SQL Server из следующих (сокращенных) исходных таблиц:

Исходная таблица назначений

Name     StartDate     EndDate      Value     Factor
Jim      2013-08-01    2013-09-06   200.0     0.5
Bob      2013-07-27    2013-11-01   140.0     1.0
Alice    2013-08-29    2014-03-22   200.0     0.8
Jim      2013-08-20    2013-09-01   250.0     0.5

Исходная таблица отчетных недель

WeekEndDate
2013-08-18
2013-08-25
2013-09-01
2013-09-08

Таблица желаемых результатов

Name     StartDate     EndDate      Value     Factor  2013-08-18  2013-08-25  2013-09-01 2013-09-08
Jim      2013-08-01    2013-09-06   200.0     0.5     100.0       100.0       100.0      0.0
Bob      2013-07-27    2013-11-01   140.0     1.0     140.0       140.0       140.0      140.0
Alice    2013-08-29    2014-03-22   200.0     0.8     0.0         0.0         160.0      160.0
Jim      2013-08-20    2013-09-01   250.0     0.5     0.0         125.0       125.0      0.0

По сути, мне нужно превратить таблицу строк отчетов в столбцы, а затем вычислить значение, при котором дата сводного столбца (Reporting Weeks/WeekEndDate) находится между StartDate и EndDate. Если дата находится за пределами этого диапазона, значение должно быть установлено равным нулю. Таблица «Отчетные недели» может меняться со временем и может быть создана из другого запроса. StartDate и EndDate обычно не совпадают с WeekEndDate. Я просмотрел множество вопросов/ответов о динамических поворотах sql, но не нашел ничего, что я мог бы адаптировать для удовлетворения потребностей, учитывая мой ограниченный опыт в этой области.


sql-server pivot dynamic-pivot
person jb413    schedule 06.01.2014    source источник
comment
Можете ли вы опубликовать образец отчета SQL, показывающий, что вы уже пробовали? Я почти проголосовал за то, чтобы закрыть это из-за того, что динамическая сводка была рассмотрена так много раз, но вычисляемые столбцы — интересный поворот.   -  person Stuart Ainsworth    schedule 06.01.2014

Ответы (1)


arrow_upward
0
arrow_downward

Прежде чем заняться динамической версией SQL, я бы сначала написал запрос как статическую версию, используя ваши ограниченные даты. Чтобы получить результат, я бы вычислил значение, которое вам нужно, в подзапросе, а затем повернулся бы к weekenddates.

Основной синтаксис, если у вас есть ограниченное количество значений, будет таким:

select name, startdate, enddate,
  value, factor,
  coalesce([2013-08-18], 0) [2013-08-18], 
  coalesce([2013-08-25], 0) [2013-08-25],
  coalesce([2013-09-01], 0) [2013-09-01], 
  coalesce([2013-09-08], 0) [2013-09-08]
from
(
  select a.name,
    a.startdate,
    a.enddate,
    a.value,
    a.factor,
    convert(varchar(10), r.weekenddate, 120) weekenddate,
    amt = a.value * a.factor
  from assignments a
  inner join reportingweeks r
    on r.weekenddate >= a.startdate
    and r.weekenddate <= a.enddate
) d
pivot
(
  sum(amt)
  for weekenddate in ([2013-08-18], [2013-08-25], [2013-09-01], 
                      [2013-09-08])
) piv;

См. SQL Fiddle с демонстрацией. Если у вас есть правильная логика, вы можете преобразовать запрос в динамический SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @colsNull AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(dt) 
                    from
                    (
                      select convert(varchar(10), weekenddate, 120) dt
                      from reportingweeks
                    ) d
                    group by dt
                    order by dt
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsNull = STUFF((SELECT ', coalesce(' + QUOTENAME(dt)+', 0) as '+QUOTENAME(dt)
                    from
                    (
                      select convert(varchar(10), weekenddate, 120) dt
                      from reportingweeks
                    ) d
                    group by dt
                    order by dt
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT name, startdate, enddate,
                value, factor, ' + @colsNull + ' 
            from 
            (
              select a.name,
                a.startdate,
                a.enddate,
                a.value,
                a.factor,
                convert(varchar(10), r.weekenddate, 120) weekenddate,
                amt = a.value * a.factor
              from assignments a
              inner join reportingweeks r
                on r.weekenddate >= a.startdate
                and r.weekenddate <= a.enddate
            ) x
            pivot 
            (
                sum(amt)
                for weekenddate in (' + @cols + ')
            ) p '

execute sp_executesql @query;

См. SQL Fiddle с демонстрацией. Оба дадут результат:

|  NAME |  STARTDATE |    ENDDATE | VALUE | FACTOR | 2013-08-18 | 2013-08-25 | 2013-09-01 | 2013-09-08 |
|-------|------------|------------|-------|--------|------------|------------|------------|------------|
| Alice | 2013-08-29 | 2014-03-22 |   200 |    0.8 |          0 |          0 |        160 |        160 |
|   Bob | 2013-07-27 | 2013-11-01 |   140 |      1 |        140 |        140 |        140 |        140 |
|   Jim | 2013-08-01 | 2013-09-06 |   200 |    0.5 |        100 |        100 |        100 |          0 |
|   Jim | 2013-08-20 | 2013-09-01 |   250 |    0.5 |          0 |        125 |        125 |          0 |
person Taryn    schedule 06.01.2014
comment
Спасибо! Это заставит меня перейти к более сложной ситуации, из которой я был абстрагирован. Большое спасибо за быстрый ответ. - person jb413; 06.01.2014