Блог

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

КУПИТЬ стол:

ПРОДАМ стол:

Ожидаемые данные:

Описание ожидаемых данных:

• Идентификатор строки = 1 —> 2022-01-01 Я купил 1000 карандашей и продал 1000 карандашей несколько часов назад.
• Строки ID = 2 и 3 —> 2022-02-02 Я купил 2000 карандашей и продал 1200 карандашей клиенту № 1 (ID = 2) и 800 карандашей клиенту № 2 (ID = 3).
• Идентификатор строки = 4 и 5 —> 2022-03-03 Я купил 6500 карандашей в двух транзакциях (3000 и 3500) и продал их в одной транзакции (6500). В этом случае лимит в строке определяется транзакцией покупки.
• Идентификатор строки = 6 и 7 —> 2022-04-04 Я купил 4000 карандашей и продал 3200 в двух транзакциях (2100 1100). Теперь у меня на складе есть 800 карандашей (4000 — 3200 = 800), предназначенных для следующей транзакции (- ов). В этом случае лимит в строке определяется транзакцией продажи.
• В ожидаемых данных нет книг, поскольку они не были проданы.

Я попытался решить эту проблему с помощью курсора, основанного на «Таблице ПРОДАЖ». Когда я делал это, я заметил, что мне пришлось использовать курсор внутри курсора. Второй курсор должен был бы основываться на «таблице ПОКУПОК» плюс на том, что не было указано в таблице ожидаемых данных, и на следующих шагах мне придется использовать IF. Может быть, есть более простой способ решить эту проблему? CTE?

Объедините данные о покупке продаже и рассчитайте совокупное «количество на складе».:

 if object_id('tempdb..#tmp_fifo_count') is not null drop table #tmp_fifo_count
select
   b.ID_BUY,
   s.ID_SELL,
   (b.BuyedCummulativeCount - s.SoldCummulativeCount) as CountInStock,
   row_number() over(partition by b.ID_BUY order by s.[Date] asc) as RN, -- flag first sold over limit
   case
      when (b.BuyedCummulativeCount - s.SoldCummulativeCount) < 0 and b.BuyedCummulativeCount >= s.[Count]
        then (b.BuyedCummulativeCount - s.SoldCummulativeCount) -- correction to case over limit (for n:m relationship)
      else 0
   end  
   case
      when b.[Count] >= s.[Count] then s.[Count]    -- i have more in stock then i sold
      else b.[Count]                                -- i cannot sell over limit
   end as [Count]
into #tmp_fifo_count
from (
   select *, (select sum([Count]) from #tmp_buy b2 where b2.[Date] <= b0.[Date]) as BuyedCummulativeCount
   from #tmp_buy b0) as b
left join (
   select *, (select sum([Count]) from #tmp_sell s2 where s2.[Date] <= s0.[Date]) as SoldCummulativeCount
   from #tmp_sell s0) as s
on b.[Date] <= s.[Date]
 

Получить результат:

 -- output
select
   row_number() over(order by ID_BUY, ID_SELL) as ID,
   ID_BUY, 
   ID_SELL, 
   [Count] 
   --case when CountInStock >= 0 then CountInStock else 0 end as CountInStock
from #tmp_fifo_count
where (CountInStock >= 0 or RN = 1) -- Fully covered by one buy or not Fully covered by one buy
   and ID_SELL is not null          -- they were sold