T-SQL, Warehouse principle, two datasets combine to one dataset using FIFO

Question

I would like to combine data between two datasets using FIFO. I think the warehouse principle is a good example to explain what I need.

BUY table:

ID_BUY	Date	Item	Count
39	2022-01-01 00:00	Pencil	1000
41	2022-02-02 00:00	Pencil	2000
44	2022-03-03 10:00	Pencil	3000
45	2022-03-03 12:00	Pencil	3500
47	2022-04-04 00:00	Pencil	4000
51	2022-05-05 00:00	Glue	5000
53	2022-06-06 00:00	Book	6000

SELL table:

ID_SELL	Date	Item	Count
40	2022-01-01 16:00	Pencil	1000
42	2022-02-02 17:00	Pencil	1200
43	2022-02-02 18:00	Pencil	800
46	2022-03-03 14:00	Pencil	6500
48	2022-04-04 15:00	Pencil	2100
49	2022-04-04 16:00	Pencil	1100
52	2022-05-05 20:00	Glue	2000

Expected data:

ID	ID_BUY	ID_SELL	Count
1	39	40	1000
2	41	42	1200
3	41	43	800
4	44	46	3000
5	45	46	3500
6	47	48	2100
7	47	49	1100
8	51	52	2000

Expected data description:

Row ID = 1 --> 2022-01-01 I bought 1000 pencils and sold 1000 pencils few hours leter.
Row ID = 2 and 3 --> 2022-02-02 I bought 2000 pencils and sold 1200 pencils to customer number 1 (ID = 2) and 800 pencils to customer number 2 (ID = 3).
Row ID = 4 and 5 --> 2022-03-03 I bought 6500 pencils in two transactions (3000 and 3500) and sold them in one transaction (6500). In this case the limit in a row is determined by the buy transaction.
Row ID = 6 and 7 --> 2022-04-04 I bought 4000 pencils and sold 3200 in two transactions (2100 + 1100). Now I have in stock 800 pencils (4000 - 3200 = 800) wating for next transaction(s). In this case the limit in a row is determined by the sell transaction.
There are no books in the expected data as they were not sold.

I have tried to solve this problem with a cursor based on "SALES table". As I was doing this, I noticed that I had to use the cursor inside the cursor. The second cursor would have to be based on the "BUY table" plus what has not been settled from the expected data table, and in the next steps I would have to use IF. Maybe there is an easier way to solve this problem? CTE?

score 0 · Answer 1 · answered Jul 23 '21 at 23:50

Combine buy + sell data and compute cumulative "count in stock":

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]

Get result:

-- 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

Almost perfect. The item in the BUY table must match the item in the SELL table. I modified your query by adding table1.item = table2.item. It worked. The problem arises, however, when in the SELL table I have, for example, only one record with count = 100 (pencils), and the BUY table remains unchanged. As a result, I get a set of 5 records, i.e. a record from the sales table is matched against each record from the BUY table. This is not correct as it should only match the first record in the BUY table (for pencils). Do you have an idea how to modify proposed solution? — Faltran, Jul 29 '21 at 09:00
The same problem is also when I only have one record "pencil count = 5000" in the SELL table. In this case, the correct solution should be three records with the correct count value. — Faltran, Jul 29 '21 at 09:21

T-SQL, Warehouse principle, two datasets combine to one dataset using FIFO

1 Answers1