SQL Server price table weighted to buying price-CodePudding

I have 3 tables:

Buys

ID	Item	qty	price
1	1001	10	1.00
2	1001	10	2.00
3	1001	10	3.00
4	1002	10	2.00
5	1002	10	1.00
6	1003	10	1.00

Fallback

Item	price
1001	3.00
1002	3.00
1003	4.00

Stock

Item	stock
1001	15
1002	5
1003	25

I have to calculate actually price each item. For that, I have to check the table "buys" each row from the biggest ID to smallest ID and take all prices as long as the stock is sufficient. If not enough buys in the table, I have to use the fallback prices for part of stock, I don't have price in first table.

So for item no. 1001, stock is 15. Price for 10 pcs found in ID 3 (3.00 USD); price for rest 5 pieces in row ID 2 (2.00 USD). So correct actually stockprice is 2.66 USD.

For item no. 1002, stock is 5. Price for latest buy is 1.00 USD in row ID 5 with quantity more than 5. So correct actually stockprice is 1.00 USD.

For item no. 1003, stock is 25. Only one entry in row ID 6 with 10 pcs for 1.00 USD each. so price for missing 15pcs have to take from fallback table 4.00 USD. So correct actually stockprice is 2.80 USD.

Result should be like this:

Item	stock	value
1001	15	2.66
1002	5	1.00
1003	25	2.80

But I have no idea how that works. Thank you very much for help.

CodePudding user response：

Using conditional aggregation when comparring stock to buys runnig totals, finally apply the fallback

select t.item, (s   t.qf * f.price) s, stock, (s   t.qf * f.price) / stock  price
from (
  select s.Item, s.Stock, 
     sum(coalesce(case when b.qe <= Stock then b.qty else Stock - b.qs end * b.price, 0)) s,
     --  qty for fallback
     min(case when Stock > coalesce(b.qe,0) then Stock - coalesce(b.qe,0) else 0 end) qf
  from Stock s
  left join (
      select Item, qty, price, ID,
        sum(qty) over(partition by Item order by ID desc) - qty qs, -- starting runnig total
        sum(qty) over(partition by Item order by ID desc) qe  -- ending runnig total
      from Buys
    ) b on s.Item = b.Item and s.Stock > b.qs
  group by s.Item, s.Stock
) t
join Fallback f on f.Item = t.Item;
order by t.Item;

db<>fiddle

CodePudding user response：

You need to create a running sum of the quantity in Buys and calculate the price based off that.

This is somewhat complicated by the fact that you may have too many, or not enough, rows in Buys to fulfil the stock.

SELECT
  s.Item,
  s.stock,
  (
    ISNULL(b.FoundStockPrice, 0)
      CASE WHEN s.stock > ISNULL(b.FoundStock, 0)
           THEN s.stock - ISNULL(b.FoundStock, 0)
           ELSE 0 END * f.price
  ) / s.stock
FROM Stock s
JOIN Fallback f ON f.Item = s.Item
OUTER APPLY (
    SELECT
      FoundStock = SUM(b.qty),
      FoundStockPrice = SUM(
        CASE WHEN b.FullStock > b.RunningSum THEN b.qty
          ELSE b.FullStock - (b.RunningSum - b.qty) END
        * b.price)
    FROM (
        SELECT *,
          RunningSum = SUM(b.qty) OVER (PARTITION BY b.Item
                         ORDER BY b.ID DESC ROWS UNBOUNDED PRECEDING),
          FullStock = s.stock
        FROM Buys b
        WHERE b.Item = s.Item
    ) b
    WHERE b.RunningSum - b.qty < s.stock
) b;

Steps are as follows:

For every Stock take all relevant Buys rows.
Calculate a running sum of qty, and then filter to only rows where the running sum includes the final stock (in other words it must up to the previous running sum).
Sum these Buys rows multiplied by their price, taking into account that we need to net off anything over the necessary stock. Take also a total sum of the quantity.
The final price is: the previous calculated total, plus any remaining unfound stock multiplied by the fallback.price, all divided by the total stock.

db<>fiddle