Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / TotalMTD / 10 сообщений из 10, страница 1 из 1
13.02.2019, 13:18
    #39773312
IDVT
IDVT
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TotalMTD
Всем привет, выручите пожалуйста.

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

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

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


 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
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

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.02.2019, 13:27
    #39773318
IDVT
IDVT
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TotalMTD
Блин, потерял остаток на конечную дату загружаемого периода и пустышек не исключил, этот запрос верный

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
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

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.02.2019, 14:24
    #39773377
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TotalMTD
IDVT,

так держите отдельную таблицу для месячных срезов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.02.2019, 14:34
    #39773394
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TotalMTD
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
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;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.02.2019, 15:30
    #39773443
IDVT
IDVT
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TotalMTD
Владислав Колосов,

Честно говоря, не вижу смысла в этом, т.к. приходится делать обратный расчет каждый раз за определенный период (редактирование документов задним числом). К сожалению месячных итогов в готовом виде нет в системе источника данных.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.02.2019, 15:31
    #39773444
IDVT
IDVT
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TotalMTD
invm,

Спасибо! Буду разбираться (не знаю рекурсию), результаты выдает не совсем верные, но дальше сам справлюсь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.02.2019, 15:38
    #39773446
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TotalMTD
IDVTБуду разбираться (не знаю рекурсию)Там нет рекурсии.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.02.2019, 16:29
    #39773479
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TotalMTD
IDVT,

так это неверно, надо формировать корректировки, а не вносить изменения непосредственно в исторические записи, если корректировок меньше, чем документов. Иначе вам понадобиться всю ретроспективу обрабатывать. А через десять лет работы она будет только к вечеру заканчиваться.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.02.2019, 16:31
    #39773481
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TotalMTD
Если с корректировками сложно куб собрать, то можно изменять сами срезы при изменениях задним числом. В бухгалтерии, например, задним числом запрещено что-то изменять.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.02.2019, 16:56
    #39773492
IDVT
IDVT
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TotalMTD
Код: plaintext
Если с корректировками сложно куб собрать, то можно изменять сами срезы при изменениях задним числом
В настоящее время заказчик работает над этим, переход на 1С. Текущая учетная система не лучший инструмент, нет в нем срезов и накопительных итогов (хотя бы месячных), все расчетное. В случае потребности посмотреть остаток в учетке на 2008 год, будет запущен обратный расчет от текущей даты по всем товарам, к утру следующего дня сотрудник получит результат.

Но и не все тонкости мне пока известны. Если корректировки документов задним числом отражаются на остатках в системе как сторно, приход или отгрузка от текущей даты, то значительно облегчит решение задачи.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / TotalMTD / 10 сообщений из 10, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]