How to Unlock your SQL power to the next level — Order Fulfillment Simulation Part 2

Previously on How to Unlock your SQL power to the next level — Order Fulfillment Simulation Part 1, we talked about how to get the underlying data and calculate the distance between destinations and origins. In this article, we will continue on how order lines will be fulfilled.

Now let’s assume all products are available in every DC, which means they will be fulfilled by the closest DC. We are using windows calculation to get the ranking of distance for each destination (DEST_ZIP) and then return the DC number with the shortest distance (RANKING = 1)

ALTER TABLE [dbo].[Orders]
ADD
SOURCE_DC_NUMBER INT;

UPDATE A
SET SOURCE_DC_NUMBER = B.DC_NUMBER
FROM  [dbo].[Orders] A
INNER JOIN
(
SELECT *
FROM
(
SELECT DEST_ZIP
, DC_NUMBER
, DISTANCE
, Row_Number () OVER (PARTITION BY DEST_ZIP ORDER BY DISTANCE ASC) RANKING
FROM   [dbo].SOURCE_DEST_PRODUCT_COMBO
) A
WHERE RANKING = 1
) B
ON
A.[Postal Code] = B.DEST_ZIP;

We can’t stock every product in every DC due to the capacity so let’s assume some products are not available somewhere. To simplify the problem and data complexity, we assume the stocking decision is at the PRODUCT GROUP level (first 6 digits of product ID), which means products under the same PRODUCT GROUP will be available in the same places. Since now the decision is based on not only destination zip code but product group, PRODUCT_GROUP should be added within partition when calculating row_number.

ALTER TABLE [dbo].[Orders]
ADD
PRODUCT_GROUP NVARCHAR(255)
, SOURCE_DC_NUMBER_2 INT;

UPDATE A
SET PRODUCT_GROUP = LEFT(A.[Product ID],6)
FROM [dbo].[Orders]  A;

UPDATE A
SET SOURCE_DC_NUMBER_2 = B.DC_NUMBER
FROM  [dbo].[Orders] A
INNER JOIN
(
SELECT *
FROM
(
SELECT PRODUCT_GROUP
, DEST_ZIP
, DC_NUMBER
, DISTANCE
, Row_Number () Over (PARTITION BY PRODUCT_GROUP, DEST_ZIP ORDER BY DISTANCE Asc) RANKING
FROM  [dbo].SOURCE_DEST_PRODUCT_COMBO
WHERE STOCKED  = 'STOCKED'
) A
WHERE RANKING = 1
) B
ON
A.PRODUCT_GROUP = B.PRODUCT_GROUP
AND
A.[Postal Code] = B.DEST_ZIP;

Now let’s look into the difference between the first and the second scenario.

SELECT * FROM [dbo].[Orders]
WHERE SOURCE_DC_NUMBER <> SOURCE_DC_NUMBER_2

We can find for the product group OFF-AP, it should be sourced from DC5 as it is the nearest one. However, since it is only available in DC1,2,3, we can only fulfill it through the closer DC among DC 1,2,3.

Thank you and more to come :)