Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 TotalMTD  [new]
IDVT
Member

Откуда:
Сообщений: 313
Всем привет, выручите пожалуйста.

У меня есть выборка, содержащая движения товара за период и фактический остаток товара на конечную дату выборки. Хочу подготовить данные для куба, в котором планирую использовать функцию TotalMTD.

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

В запросе не нравится "Cross join", за счет которого я создаю недостающие строки на начало каждого месяца, как его заменить для более эффективного расчета, выборка достаточно большая (но фактически ее пока нет, сложно оценить)


+
Drop table if exists #StageData
Drop table if exists #Calendar

Declare @BeginDate   Date = null
      , @EndDate     Date = null
      , @CurrentDate Date = null

Create table #StageData        --> сырые данные
   ( StockDate  Date not null  -- дата движения
   , StoreId    Int  not null  -- код магазина
   , ProductId  Int  not null  -- код товара
   , MoveQty    Int  not null  -- остаток/движение
   ) 

Insert into #StageData              --> тестовый набор данных
  values( '20180208', 1,111, -46 )  --   приход
      , ( '20180210', 1,111, 8   )  -- продажа
      , ( '20180211', 1,111, -40 )  --   приход
      , ( '20180301', 1,111, -32 )  --   приход
      , ( '20180308', 1,111, 11  )  -- продажа
      , ( '20180309', 1,111, 10  )  -- продажа
      , ( '20180310', 1,111, -12 )  --   приход
      , ( '20180516', 1,111, 16  )  -- продажа
      , ( '20180518', 1,111, 5   )  -- продажа
      , ( '20180520', 1,111, -40 )  --   приход
      , ( '20180801', 1,111, 20  )  -- продажа
      , ( '20180802', 1,111, 100 )  -- остаток на текущий день

Create table #Calendar         --> календарь для месячных итогов на первый день месяца
   ( Date Date not null
   )
   
select @BeginDate = DateAdd( day
                           , 1
                           , EoMonth( Min( StockDate)
                                    , -1
                                    )
                           )
     , @EndDate = Max( StockDate)
     , @CurrentDate = Max( StockDate)
from #StageData

while @BeginDate < @EndDate   -- таблица календаря есть, это только в рамках примера
begin 
      insert into #Calendar
             select @BeginDate
      set @BeginDate = dateadd( Month
                              , 1
                              , @BeginDate
                              )
end

    
select StockDate
     , StoreId
     , ProductId
     , case Day( StockDate)                                    -- если первый день месяца
            when 1 then Sum( MoveQty)                          --      тогда нарастающий итог (остаток на конец первого дня месяца)
                             over( partition by StoreId        --      иначе движение товара
                                              , ProductId
                                   order by StockDate desc
                           ) - case StockDate                  --      если дата строки равняется текущей дате строки
                                    when @CurrentDate then 0   --           тогда это фактический остаток
                                    else MoveQty
                               end
            else case StockDate                                --      если дата строки равняется текущей дате строки
                      when @CurrentDate then 0                 --           тогда это фактический остаток
                      else MoveQty 
                           * - 1 
                 end
       end as BalanceOrMoveQty_EndDay                          -- остаток на конец месяца и движение в течении этого месяца
     --, Sum( MoveQty)
     --       over( partition by StoreId
     --                        , ProductId
     --             order by StockDate desc
     --     ) as BalanceQty_BegDay                               -- остаток на начало дня
     --, case StockDate                                          -- если дата строки равняется текущей дате строки
     --       when @CurrentDate then 0                           --      тогда это фактический остаток
     --       else MoveQty 
     --            * - 1 
     --  end as MoveQtyDay                                       -- движение за день
     --, Sum( MoveQty)
     --       over( partition by StoreId
     --                        , ProductId
     --             order by StockDate desc
     --     ) - case StockDate                                   -- если дата строки равняется текущей дате строки
     --              when @CurrentDate then 0                    --      тогда это фактический остаток
     --              else MoveQty
     --         end as BalanceQty_EndDay                         -- остаток на конец дня
from(	
   -- генерация строк для месячных итогов, на дату которых нет движения
   select cl.Date as StockDate
         , sd.StoreId
         , sd.ProductId
         , 0 as MoveQty
    from( 
          select distinct
                 StoreId
               , ProductId
          from #StageData
        ) as sd
         cross join #Calendar as cl
    where not exists(
                      select 1
                      from #StageData as exs
                      where exs.ProductId = sd.ProductId
                            and exs.StoreId = sd.StoreId
                            and exs.StockDate = cl.Date
                    )
    union 
    -- исходные данные
    select *
    from #StageData
   )x
order by 1,2,3


Сообщение было отредактировано: 13 фев 19, 13:30
13 фев 19, 13:18    [21808377]     Ответить | Цитировать Сообщить модератору
 Re: TotalMTD  [new]
IDVT
Member

Откуда:
Сообщений: 313
Блин, потерял остаток на конечную дату загружаемого периода и пустышек не исключил, этот запрос верный

+
Drop table if exists #StageData
Drop table if exists #Calendar

Declare @BeginDate   Date = null
      , @EndDate     Date = null
      , @CurrentDate Date = null

Create table #StageData        --> сырые данные
   ( StockDate  Date not null  -- дата движения
   , StoreId    Int  not null  -- код магазина
   , ProductId  Int  not null  -- код товара
   , MoveQty    Int  not null  -- остаток/движение
   ) 

Insert into #StageData              --> тестовый набор данных
  values( '20180208', 1,111, -46 )  --   приход
      , ( '20180210', 1,111, 8   )  -- продажа
      , ( '20180211', 1,111, -40 )  --   приход
      , ( '20180301', 1,111, -32 )  --   приход
      , ( '20180308', 1,111, 11  )  -- продажа
      , ( '20180309', 1,111, 10  )  -- продажа
      , ( '20180310', 1,111, -12 )  --   приход
      , ( '20180516', 1,111, 16  )  -- продажа
      , ( '20180518', 1,111, 5   )  -- продажа
      , ( '20180520', 1,111, -40 )  --   приход
      , ( '20180801', 1,111, 20  )  -- продажа
      , ( '20180802', 1,111, 100 )  -- остаток на текущий день

Create table #Calendar         --> календарь для месячных итогов на первый день месяца
   ( Date Date not null
   )
   
select @BeginDate = DateAdd( day
                           , 1
                           , EoMonth( Min( StockDate)
                                    , -1
                                    )
                           )
     , @EndDate = Max( StockDate)
     , @CurrentDate = Max( StockDate)
from #StageData

while @BeginDate < @EndDate   -- таблица календаря есть, это только в рамках примера
begin 
      insert into #Calendar
             select @BeginDate
      set @BeginDate = dateadd( Month
                              , 1
                              , @BeginDate
                              )
end

    
select StockDate
     , StoreId
     , ProductId
     , case Day( StockDate)                                    -- если первый день месяца
            when 1 then Sum( MoveQty)                          --      тогда нарастающий итог (остаток на конец первого дня месяца)
                             over( partition by StoreId        --      иначе движение товара
                                              , ProductId
                                   order by StockDate desc
                           ) - case StockDate                  --      если дата строки равняется текущей дате строки
                                    when @CurrentDate then 0   --           тогда это фактический остаток
                                    else MoveQty
                               end
            else case StockDate                                --      если дата строки равняется текущей дате строки
                      when @CurrentDate then MoveQty           --           тогда это фактический остаток
                      else MoveQty 
                           * - 1 
                 end
       end as BalanceOrMoveQty_EndDay                          -- остаток на конец месяца и движение в течении этого месяца
     --, Sum( MoveQty)
     --       over( partition by StoreId
     --                        , ProductId
     --             order by StockDate desc
     --     ) as BalanceQty_BegDay                               -- остаток на начало дня
     --, case StockDate                                          -- если дата строки равняется текущей дате строки
     --       when @CurrentDate then 0                           --      тогда это фактический остаток
     --       else MoveQty 
     --            * - 1 
     --  end as MoveQtyDay                                       -- движение за день
     --, Sum( MoveQty)
     --       over( partition by StoreId
     --                        , ProductId
     --             order by StockDate desc
     --     ) - case StockDate                                   -- если дата строки равняется текущей дате строки
     --              when @CurrentDate then 0                    --      тогда это фактический остаток
     --              else MoveQty
     --         end as BalanceQty_EndDay                         -- остаток на конец дня
from(	
   -- генерация строк для месячных итогов, на дату которых нет движения
   select cl.Date as StockDate
         , sd.StoreId
         , sd.ProductId
         , 0 as MoveQty
    from( 
          select distinct
                 StoreId
               , ProductId
          from #StageData
        ) as sd
         cross join #Calendar as cl
    where not exists(                   -- исключение комбинаций строк, у которых есть движение на первый день месяца
                      select 1
                      from #StageData as exs
                      where exs.ProductId = sd.ProductId
                            and exs.StoreId = sd.StoreId
                            and exs.StockDate = cl.Date
                    )
          and exists(                   -- исключение комбинаций строк, по которым нет остатков в последующих датах
                      select 1
                      from #StageData as exs
                      where exs.ProductId = sd.ProductId
                            and exs.StoreId = sd.StoreId
                            and exs.StockDate > cl.Date
                    )
                 
    union 
    -- исходные данные
    select *
    from #StageData
   )x
order by 1,2,3


Сообщение было отредактировано: 13 фев 19, 13:30
13 фев 19, 13:27    [21808393]     Ответить | Цитировать Сообщить модератору
 Re: TotalMTD  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7754
IDVT,

так держите отдельную таблицу для месячных срезов.
13 фев 19, 14:24    [21808487]     Ответить | Цитировать Сообщить модератору
 Re: TotalMTD  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
with t (StockDate, StoreId, ProductId, BalanceOrMoveQty_EndDay, NextStocDate) as
(
 select
  b.d, a.StoreId, a.ProductId,
  sum(sum(c.MoveQty)) over (partition by a.StoreId, a.ProductId order by b.d) -
  sum(case when day(a.StockDate) = 1 then 0 else c.MoveQty end),
  lead(b.d) over (partition by a.StoreId, a.ProductId order by b.d)
 from
  #StageData a cross apply
  (select dateadd(day, -day(a.StockDate) + 1, StockDate)) b(d) cross apply
  (select case when StockDate = @CurrentDate then a.MoveQty else -a.MoveQty end) c(MoveQty)
 group by
  b.d, a.StoreId, a.ProductId
)
select
 isnull(c.Date, t.StockDate) as StockDate, t.StoreId, t.ProductId, t.BalanceOrMoveQty_EndDay
from
 t left join
 #Calendar c on c.Date >= t.StockDate and c.Date < t.NextStocDate

union all

select
 a.StockDate, a.StoreId, a.ProductId, b.MoveQty
from
 #StageData a cross apply
 (select case when StockDate = @CurrentDate then a.MoveQty else -a.MoveQty end) b(MoveQty)
where
 day(StockDate) > 1

order by
 StockDate, StoreId, ProductId;
13 фев 19, 14:34    [21808509]     Ответить | Цитировать Сообщить модератору
 Re: TotalMTD  [new]
IDVT
Member

Откуда:
Сообщений: 313
Владислав Колосов,

Честно говоря, не вижу смысла в этом, т.к. приходится делать обратный расчет каждый раз за определенный период (редактирование документов задним числом). К сожалению месячных итогов в готовом виде нет в системе источника данных.
13 фев 19, 15:30    [21808592]     Ответить | Цитировать Сообщить модератору
 Re: TotalMTD  [new]
IDVT
Member

Откуда:
Сообщений: 313
invm,

Спасибо! Буду разбираться (не знаю рекурсию), результаты выдает не совсем верные, но дальше сам справлюсь.
13 фев 19, 15:31    [21808594]     Ответить | Цитировать Сообщить модератору
 Re: TotalMTD  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
IDVT
Буду разбираться (не знаю рекурсию)
Там нет рекурсии.
13 фев 19, 15:38    [21808606]     Ответить | Цитировать Сообщить модератору
 Re: TotalMTD  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7754
IDVT,

так это неверно, надо формировать корректировки, а не вносить изменения непосредственно в исторические записи, если корректировок меньше, чем документов. Иначе вам понадобиться всю ретроспективу обрабатывать. А через десять лет работы она будет только к вечеру заканчиваться.
13 фев 19, 16:29    [21808691]     Ответить | Цитировать Сообщить модератору
 Re: TotalMTD  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7754
Если с корректировками сложно куб собрать, то можно изменять сами срезы при изменениях задним числом. В бухгалтерии, например, задним числом запрещено что-то изменять.
13 фев 19, 16:31    [21808697]     Ответить | Цитировать Сообщить модератору
 Re: TotalMTD  [new]
IDVT
Member

Откуда:
Сообщений: 313
Если с корректировками сложно куб собрать, то можно изменять сами срезы при изменениях задним числом
В настоящее время заказчик работает над этим, переход на 1С. Текущая учетная система не лучший инструмент, нет в нем срезов и накопительных итогов (хотя бы месячных), все расчетное. В случае потребности посмотреть остаток в учетке на 2008 год, будет запущен обратный расчет от текущей даты по всем товарам, к утру следующего дня сотрудник получит результат.

Но и не все тонкости мне пока известны. Если корректировки документов задним числом отражаются на остатках в системе как сторно, приход или отгрузка от текущей даты, то значительно облегчит решение задачи.
13 фев 19, 16:56    [21808723]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить