Trying to figure out a set-based approach to a stock eta problem
-
Due to the pandemic, the shipping/receiving of products has been a bit delayed. This delay has created a back order problem that our customer service department would like filled with a stop-gap measure: what is the ETA of the product that has been ordered on X order. I think this should be an easy problem to solve (and it would with RBAR/looping but that wouldn't be performant) but I am trying to get some help in a more set-based solution to this problem.
The #orders table has the basic order information. The order id is how we determine which order takes precedence over each other. For simplicity I am only including orders which are back ordered (and their associated amounts). The #supplierDeliveries table shows the date which the next shipment shows up and the amounts which should be on that shipment. Once again for simplicity sake, I am only using one product "PRN1".
Below is the basic setup and desired result.
--Setup
CREATE TABLE #orders(
OrderIdINT NOT NULL,
ItemNumberVARCHAR(25) NOT NULL,
OrderAmountINT NOT NULL,
RowNumINT NULL,
ETADATE NULL)
CREATE TABLE #supplierDeliveries(
ItemNumber VARCHAR(25) NOT NULL,
NextDeliveryDate DATE NOT NULL,
OrderedAmount INT NOT NULL,
rankingINT NULL,
leftOver INT NULL)
INSERT INTO #orders(OrderId, ItemNumber, OrderAmount,RowNum)
VALUES
(8213081,'PRN1',2,1)
,(8213304,'PRN1',10,2)
,(8214671,'PRN1',7,3)
,(8214905,'PRN1',1,4)
,(8214982,'PRN1',10,5)
,(8215164,'PRN1',1,6)
,(8215312,'PRN1',1,7)
,(8215316,'PRN1',1,8)
,(8215364,'PRN1',1,9)
,(8215668,'PRN1',1,10)
,(8215750,'PRN1',1,11)
,(8215758,'PRN1',1,12)
,(8215773,'PRN1',1,13)
,(8215839,'PRN1',1,14)
,(8215915,'PRN1',1,15)
,(8215973,'PRN1',1,16)
,(8216090,'PRN1',3,17)
,(8216154,'PRN1',1,18)
,(8216355,'PRN1',1,19)
,(8216401,'PRN1',1,20)
,(8216467,'PRN1',1,21)
,(8216570,'PRN1',1,22)
,(8216588,'PRN1',1,23)
,(8216689,'PRN1',1,24)
,(8216764,'PRN1',1,25)
,(8216820,'PRN1',10,26)
,(8216822,'PRN1',10,27)
,(8216823,'PRN1',10,28)
INSERT INTO #supplierDeliveries(ItemNumber,NextDeliveryDate,OrderedAmount,ranking)
VALUES
('PRN1','2021-03-17',7,1)
,('PRN1','2021-03-22',60,2)
,('PRN1','2021-04-13',100,3)
--Expected values
SELECT *
FROM (VALUES
(8213081,2,1,'2021-03-17'),
(8213304,5,2,'2021-03-17'),
(8213304,5,2,'2021-03-22'),
(8214671,7,3,'2021-03-22'),
(8214905,1,4,'2021-03-22'),
(8214982,10,5,'2021-03-22'),
(8215164,1,6,'2021-03-22'),
(8215312,1,7,'2021-03-22'),
(8215316,1,8,'2021-03-22'),
(8215364,1,9,'2021-03-22'),
(8215668,1,10,'2021-03-22'),
(8215750,1,11,'2021-03-22'),
(8215758,1,12,'2021-03-22'),
(8215773,1,13,'2021-03-22'),
(8215839,1,14,'2021-03-22'),
(8215915,1,15,'2021-03-22'),
(8215973,1,16,'2021-03-22'),
(8216090,3,17,'2021-03-22'),
(8216154,1,18,'2021-03-22'),
(8216355,1,19,'2021-03-22'),
(8216401,1,20,'2021-03-22'),
(8216467,1,21,'2021-03-22'),
(8216570,1,22,'2021-03-22'),
(8216588,1,23,'2021-03-22'),
(8216689,1,24,'2021-03-22'),
(8216764,1,25,'2021-03-22'),
(8216820,10,26,'2021-03-22'),
(8216822,5,27,'2021-03-22'),
(8216822,5,27,'2021-04-13'),
(8216823,10,28,'2021-04-13')) as x(OrderId,OrderAmount,RowNum,ETA)
--tear down
drop table #orders
drop table #supplierDeliveries -
I think this will work for you
with cteOrders as (
select OrderId, ItemNumber, OrderAmount,RowNum,
sum(OrderAmount) over(partition by ItemNumber order by RowNum) - OrderAmount + 1 as sumOrderFrom,
sum(OrderAmount) over(partition by ItemNumber order by RowNum) as sumOrderTo
from #orders
),
ctesupplierDeliveries as (
select ItemNumber,NextDeliveryDate,OrderedAmount,ranking,
sum(OrderedAmount) over(partition by ItemNumber order by NextDeliveryDate) - OrderedAmount+1 as sumOrderedFrom,
sum(OrderedAmount) over(partition by ItemNumber order by NextDeliveryDate) as sumOrderedTo
from #supplierDeliveries
)
select o.OrderId, o.ItemNumber,
case when o.sumOrderTo > s.sumOrderedTo then o.sumOrderTo - s.sumOrderedTo
when o.sumOrderFrom < s.sumOrderedFrom then s.sumOrderedFrom - o.sumOrderFrom
else o.OrderAmount end as OrderAmount,o.RowNum,
s.NextDeliveryDate as ETA
from cteOrders o
inner join ctesupplierDeliveries s on s.ItemNumber = o.ItemNumber and o.sumOrderTo >= s.sumOrderedFrom and o.sumOrderFrom <= s.sumOrderedTo
order by o.RowNum,s.NextDeliveryDate;____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark!
Thank you so much! I have been so blocked on this issue. I appreciate the quick response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply