Создайте динамический запрос во временную таблицу вместо операторов жесткого кодирования union

#sql #sql-server

#sql #sql-сервер

Вопрос:

Я жестко запрограммировал кучу операторов объединения, чтобы создать строку для каждого региона, для каждого месяца для каждого финансового года (пример только для 2017 финансового года). Вместо всех этих строк кода я бы действительно хотел «построить цикл», который динамически создает временную таблицу вместо сотен строк кода и инструкций объединения.

***** Обновите код из предложенного Гордоном решения

 SELECT dt.fulldate AS trade_month, 
       t.trade_gy  AS trade_gy, 
       r.dvsregion_id, 
       r.dvsregion_name 
FROM   dimdate dt 
       CROSS JOIN (VALUES ('2017'), 
                          ('2018')) AS t(trade_gy) 
       CROSS JOIN (VALUES ('1', 
                  'Midwest'), 
                          ('2', 
                  'Northeast')) as r(dvsregion_id, dvsregion_name) 
WHERE  Year(dt.fulldate) >= 2012 
       AND dt.fulldate = dt.firstofmonthdate 
       AND dt.fulldate IN (SELECT p.fulldate 
                           FROM   dimdate p 
                           WHERE  ( Year(p.fulldate) ) >= 2012 
                                  AND p.lastdayofgasyear <= '2018-03-31') 
ORDER  BY trade_gy ASC, 
          trade_month ASC 
  
  • Дата начала будет 1-1-2012*

  • Дата окончания меняется для каждого финансового года.

  • Наш финансовый год заканчивается 31 марта (2017 заканчивается 3-31-18 и так далее).

  • Финансовые годы включают 2017-2023

  • Регионы: Средний Запад, северо-восток, Запад, Канада

Я высоко ценю любые предложения или ссылки, которые помогут мне!

Спасибо

Пример кода за 2017 финансовый год. Все остальные годы имеют тот же формат, за исключением того, что я обновляю p.LastDayofGasYear <= ‘3-31-2018’) для каждого финансового года.

  Select *
into #Regions
From (
--Create regions temp table for GY 2017
select
dt.FullDate as trade_month ,
'2017' as trade_gy,
'1' as dvsregion_id, 
'Midwest' as dvsregion_name
from dimDate dt
where  (year(dt.FullDate)) >= 2012  
and dt.FullDate = dt.FirstOfMonthDate
and dt.FullDate IN (
Select p.FullDate
from dimDate p
where (year(p.FullDate)) >= 2012 and p.LastDayofGasYear <= '3-31-2018')
Union
select
dt.FullDate as trade_month ,
'2017' as trade_gy,
'2' as dvsregion_id, 
'Northeast' as dvsregion_name
from dimDate dt
where  (year(dt.FullDate)) >= 2012  
and dt.FullDate = dt.FirstOfMonthDate
and dt.FullDate IN (
Select p.FullDate
from dimDate p
where (year(p.FullDate)) >= 2012 and p.LastDayofGasYear <= '3-31-2018')
Union
select
dt.FullDate as trade_month ,
'2017' as trade_gy,
'3' as dvsregion_id, 
'Western' as dvsregion_name
from dimDate dt
where  (year(dt.FullDate)) >= 2012  
and dt.FullDate = dt.FirstOfMonthDate
and dt.FullDate IN (
Select p.FullDate
from dimDate p
where (year(p.FullDate)) >= 2012 and p.LastDayofGasYear <= '3-31-2018')
Union
select
dt.FullDate as trade_month ,
'2017' as trade_gy,
'5' as dvsregion_id, 
'Canada' as dvsregion_name
from dimDate dt
where  (year(dt.FullDate)) >= 2012  
and dt.FullDate = dt.FirstOfMonthDate
and dt.FullDate IN (
Select p.FullDate
from dimDate p
where (year(p.FullDate)) >= 2012 and p.LastDayofGasYear <= '3-31-2018')
  

Конечные результаты будут выглядеть примерно так

Комментарии:

1. В предыдущем вопросе вы упомянули, что у вас есть таблица регионов. Итак, почему вы думаете, что вам нужно жестко кодировать эти значения в вашем запросе? Требуется время и работа, чтобы начать мыслить в терминах множеств — у вас их впереди совсем немного. И вообще говоря, использование жестко запрограммированных идентификаторов — это большой красный флаг, который предполагает, что вам нужно пересмотреть свой подход.

2. Да, это правильно. Мне нужно использовать другой подход. У меня есть таблица регионов и таблица DimDate, которую я могу использовать. Спасибо

Ответ №1:

Похоже, вам просто нужны cross join s для постоянных значений:

 select dt.FullDate as trade_month ,
       t.trade_gy as trade_gy,
       r.dvsregion_id, 
       r.dvsregion_name
from dimDate dt cross join
     (values ('2017', '2018', . . .) as t(trade_gy) cross join
     (values ('1', 'Midwest'),
             ('2', 'Northeast'),
             . . .
     ) r(dvsregion_id, dvsregion_name)
where year(dt.FullDate)) >= 2012 and
      dt.FullDate = dt.FirstOfMonthDate and
      dt.FullDate in (Select p.FullDate
                      from dimDate p
                      where (year(p.FullDate)) >= 2012 and p.LastDayofGasYear <= '2018-03-31'
                     );
  

Я не уверен, что полностью следую всей логике вашего довольно сложного запроса, но я думаю, что его можно структурировать следующим образом.

Комментарии:

1. Я взгляну на это, как только вернусь в офис этим утром. Спасибо!

2. Здравствуйте, извините за задержку ответа, но вчера был занят. Я смог немного изменить это, чтобы заставить его работать, и он близок. 2017 финансовый год заканчивается на ‘2018-03-31’, но 2018 финансовый год заканчивается на ‘2019-3-01’. Это верно для всех финансовых лет. Я не уверен, как изменить оператор WHERE, чтобы настроить увеличение для каждого финансового года. Я обновлю свой первоначальный вопрос, чтобы показать предложенные вами обновления вашего кода. Я ограничен по количеству символов в области комментариев. Спасибо

3. @Brent . . . Я понятия не имею, где в where предложении используется финансовый год.

4. Извините за это: И p.lastdayofgasyear <= ‘2018-03-31’) Это конец 2017 финансового года

5. Я собираюсь разбить этот запрос на пару разных временных таблиц, чтобы упростить его. Ваш ответ выше предоставляет то, что мне было нужно для первой временной таблицы. Спасибо!!