Supply and Demand task. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Supply and Demand task.

Author

Topic

ElenaSTL
Starting Member

10 Posts

Posted - 2014-10-24 : 12:33:15

Several years ago I had to create a report which shows on what week some orders will be fulfilled.
This task appeared to be the most complex SQL task I ever had. I spent almost a week trying to make a code and the right decision came to my mind when I woke up at 2:00 am. The trick was to delete one or several lines from temp table and then insert back just one record in the same table with remaining value.
The task:
Company has some orders from internet and each order can have one or several items ordered. In my example I have 4 orders for 5,4,9,9 items. If they don’t have these items in the Warehouse, the order is becoming a backorder, so the company need to receive additional items from the manufacturer. Manufacturer is sending boxes with this item and one box may have enough items to fulfill several orders but, on the other hand one order could be fulfilled from several parcels. In my example we plan to receive boxes with 1,15,1,20 items on appropriate dates Feb. 1st 2012 and so on. Orders should be fulfilled in order they come, so the order 001 should be fulfilled before order 002.
The Task was actually more complex and I had an outer loop for each Item and then grouped by weeks to show which orders will be fulfilled on which week and sort by Items with the biggest backorders amount.
Here is my code just for one item. Maybe, somebody can propose a more original decision for this task.

--table for Orders for one Item Number
DECLARE @OrdersTable table (
OrderNo varchar(20),
OrderedQty int
)
--table for Supplies for one ItemNumber
DECLARE @SupplyTable table (
PlanDate varchar(8),
SupplyQty int
)
--table for Supplies for one ItemNumber with RunningTotal field
DECLARE @RunningSumSupplyTable table (
PlanDate varchar(8),
SupplyQty int,
RunningTotal int
)
--Final Table
DECLARE @FinalTable table (
OrderNo varchar(20),
OrderedQty int,
PlanDate varchar(8)
)

INSERT INTO @OrdersTable VALUES ('001', 5)
INSERT INTO @OrdersTable VALUES ('002', 4)
INSERT INTO @OrdersTable VALUES ('003', 9)
INSERT INTO @OrdersTable VALUES ('004', 9)

INSERT INTO @SupplyTable VALUES ('20120201', 1)
INSERT INTO @SupplyTable VALUES ('20120202', 15)
INSERT INTO @SupplyTable VALUES ('20120203', 1)
INSERT INTO @SupplyTable VALUES ('20120204', 20)

--calculate RunningTotal for @SupplyTable:
INSERT INTO @RunningSumSupplyTable
SELECT a.PlanDate,
a.SupplyQty,
SUM(b.SupplyQty) AS RunningTotal
FROM @SupplyTable a
CROSS JOIN @SupplyTable b
WHERE (b.PlanDate <= a.PlanDate)
GROUP BY a.PlanDate,a.SupplyQty
ORDER BY a.PlanDate,a.SupplyQty

DECLARE @CurrentOrderNo varchar(20)
DECLARE @CurrentOrderQty int
SET @CurrentOrderQty = (SELECT TOP 1 OrderedQty FROM @OrdersTable)
DECLARE @CurrentSupplyQty int
DECLARE @CurrentPlanDate varchar(8)

WHILE (SELECT count(*) FROM @OrdersTable) > 0
BEGIN
SET @CurrentOrderNo = (SELECT TOP 1 OrderNo FROM @OrdersTable)
SET @CurrentOrderQty = (SELECT TOP 1 OrderedQty FROM @OrdersTable)
SET @CurrentSupplyQty = (SELECT TOP 1 RunningTotal FROM @RunningSumSupplyTable WHERE RunningTotal >= @CurrentOrderQty)
SET @CurrentPlanDate = (SELECT TOP 1 PlanDate FROM @RunningSumSupplyTable WHERE RunningTotal >= @CurrentOrderQty)

DELETE FROM @SupplyTable WHERE PlanDate <= @CurrentPlanDate
INSERT INTO @SupplyTable VALUES (@CurrentPlanDate,@CurrentSupplyQty)
UPDATE @SupplyTable SET SupplyQty = @CurrentSupplyQty - @CurrentOrderQty WHERE PlanDate = @CurrentPlanDate

DELETE FROM @RunningSumSupplyTable
--recalculate RunningTotal
INSERT INTO @RunningSumSupplyTable
SELECT a.PlanDate,
a.SupplyQty,
SUM(b.SupplyQty) AS RunningTotal
FROM @SupplyTable a
CROSS JOIN @SupplyTable b
WHERE (b.PlanDate <= a.PlanDate)
GROUP BY a.PlanDate,a.SupplyQty
ORDER BY a.PlanDate,a.SupplyQty

DELETE FROM @OrdersTable WHERE OrderNo = @CurrentOrderNo

INSERT INTO @FinalTable VALUES (@CurrentOrderNo,@CurrentOrderQty,@CurrentPlanDate)
END

DELETE FROM @FinalTable WHERE PlanDate is null

SELECT * FROM @FinalTable

ElenaSTL
Starting Member

10 Posts

Posted - 2014-11-14 : 11:04:07

Another decision made by my friend S.T. using recursion:

-- The real code would be more complicated - need to know what is in warehouse right now
-- We assume our warehouse is empty and this is our first orders and supplies

-- Create and populate original sample "order" table
CREATE TABLE #Order (ItemKey Int NOT NULL, OrderKey Int NOT NULL, OrderQty Int NOT NULL)
INSERT INTO #Order VALUES (1, 1, 5)
INSERT INTO #Order VALUES (1, 2, 4)
INSERT INTO #Order VALUES (1, 3, 9)
INSERT INTO #Order VALUES (1, 4, 9)
INSERT INTO #Order VALUES (2, 5, 2)
INSERT INTO #Order VALUES (2, 6, 7)
INSERT INTO #Order VALUES (2, 7, 6)
INSERT INTO #Order VALUES (2, 8, 3)
INSERT INTO #Order VALUES (3, 5, 2)
INSERT INTO #Order VALUES (3, 10, 3)
INSERT INTO #Order VALUES (3, 11, 5)
INSERT INTO #Order VALUES (3, 12, 8)

--Create and populate original sample "supply" table
CREATE TABLE #Supply (ItemKey Int NOT NULL, DeliveryDate Datetime NOT NULL, DeliveryQty Int NOT NULL)
INSERT INTO #Supply VALUES (1, '2012-02-01', 1)
INSERT INTO #Supply VALUES (1, '2012-02-02', 15)
INSERT INTO #Supply VALUES (1, '2012-02-03', 1)
INSERT INTO #Supply VALUES (1, '2012-02-04', 20)
INSERT INTO #Supply VALUES (2, '2012-02-05', 2)
INSERT INTO #Supply VALUES (2, '2012-02-06', 17)
INSERT INTO #Supply VALUES (2, '2012-02-07', 1)
INSERT INTO #Supply VALUES (2, '2012-02-08', 10)
INSERT INTO #Supply VALUES (3, '2012-02-09', 1)
INSERT INTO #Supply VALUES (3, '2012-02-10', 14)
INSERT INTO #Supply VALUES (3, '2012-02-11', 1)
INSERT INTO #Supply VALUES (3, '2012-02-12', 1)

-- Create and populate "order" working table
CREATE TABLE #OT (RowN Int NOT NULL, ItemKey Int NOT NULL, OrderKey Int NOT NULL, OrderQty Int NOT NULL)
INSERT INTO #OT (RowN, ItemKey, OrderKey, OrderQty)
SELECT ROW_NUMBER() OVER(PARTITION BY ItemKey ORDER BY OrderKey), ItemKey, OrderKey, OrderQty FROM #Order;

WITH OrderTable(RowN, ItemKey, OrderKey, OrderQty) AS
(
SELECT RowN, ItemKey, OrderKey, OrderQty FROM #OT WHERE RowN = 1
UNION ALL
SELECT OT.RowN, OT.ItemKey, OT.OrderKey, O.OrderQty + OT.OrderQty FROM OrderTable O INNER JOIN #OT OT ON O.ItemKey = OT.ItemKey AND O.RowN + 1 = OT.RowN
)

UPDATE T
SET T.OrderQty = O.OrderQty
FROM #OT T INNER JOIN OrderTable O ON T.RowN = O.RowN AND T.ItemKey = O.ItemKey AND T.Orderkey = O.Orderkey
OPTION (MAXRECURSION 0)

-- Create and populate "supply" working table
CREATE TABLE #ST (RowN Int NOT NULL, ItemKey Int NOT NULL, DeliveryDate Datetime NOT NULL, DeliveryQty Int NOT NULL)
INSERT INTO #ST (RowN, ItemKey, DeliveryDate, DeliveryQty)
SELECT ROW_NUMBER() OVER(PARTITION BY ItemKey ORDER BY DeliveryDate), ItemKey, DeliveryDate, DeliveryQty FROM #Supply;

WITH SupplyTable(RowN, ItemKey, DeliveryDate, DeliveryQty) AS
(
SELECT RowN, ItemKey, DeliveryDate, DeliveryQty FROM #ST WHERE RowN = 1
UNION ALL
SELECT ST.RowN, ST.ItemKey, ST.DeliveryDate, S.DeliveryQty + ST.DeliveryQty FROM SupplyTable S INNER JOIN #ST ST ON S.ItemKey = ST.ItemKey AND S.RowN + 1 = ST.RowN
)

UPDATE T
SET T.DeliveryQty = S.DeliveryQty
FROM #ST T INNER JOIN Supplytable S ON T.RowN = S.RowN AND T.ItemKey = S.ItemKey AND T.DeliveryDate = S.DeliveryDate
OPTION (MAXRECURSION 0)

-- Create and populate final table. Needed for cases when more then one kind of item in the same order
CREATE TABLE #FinalTable (ItemKey Int NOT NULL, OrderKey Int NOT NULL, OrderQty Int NOT NULL, ShippingDate Datetime NULL)
INSERT INTO #FinalTable (ItemKey, OrderKey, OrderQty, ShippingDate)
SELECT O.ItemKey,
O.OrderKey,
O.OrderQty,
MIN(ST.DeliveryDate) ShippingDate
FROM #Order O LEFT JOIN #OT OT ON O.ItemKey = OT.ItemKey AND O.OrderKey = OT.OrderKey
LEFT JOIN #ST ST ON OT.ItemKey = ST.ItemKey AND OT.OrderQty <= ST.DeliveryQty
GROUP BY O.ItemKey,
O.OrderKey,
O.OrderQty

--Final SELECT
SELECT Orderkey,
SUM(OrderQty) OrderQty,
MAX(ShippingDate) ShippingDate,
CASE WHEN COUNT(*) = 1 AND MAX(ShippingDate) IS NOT NULL THEN 'Your item(s) will be shipped on ' + CONVERT(Varchar(25), MAX(ShippingDate), 101)
WHEN COUNT(*) > 1 AND MAX(ShippingDate) IS NOT NULL THEN 'We will ship your items in one package on ' + CONVERT(Varchar(25), MAX(ShippingDate), 101)
WHEN MAX(ShippingDate) IS NULL THEN 'Your item is on backorder. We will notify you when it''s available.'
ELSE 'Please contact us for more information' END Message
FROM #FinalTable
GROUP BY OrderKey
ORDER BY OrderKey,
OrderQty

DROP TABLE #Order
DROP TABLE #Supply
DROP TABLE #OT
DROP TABLE #ST
DROP TABLE #FinalTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources