Geek Culture
Published in

Geek Culture

How to keep SQL “GROUP BY” details/information for the purpose of tracking

Take MSSQL as example

Photo from: https://learnsql.com/blog/group-by-in-sql-explained/

Suppose we have table “ProductionWork” that record the manufacture meta data for the production process.

DECLARE @ProductionWork TABLE
(
WorkOrder varchar(20), -- store work order number
Material varchar(15),
Quantity smallint,
Multiplier smallint -- multiplier for the quantity
);
-- create some sample data
INSERT INTO @ProductionWork VALUES
('WO#1', 'M1', 9, 2),
('WO#1', 'M2', 11, 1),
('WO#1', 'M3', 1, 2),
('WO#2', 'M1', 2, 5),
('WO#2', 'M3', 7, 1),
('WO#3', 'M1', 6, 3),
('WO#3', 'M5', 5, 2);

According above table, we might having information for the WO#1 which complete the whole manufacturing process should consume material(s) M1: 18 (9 x 2), M2: 11 (11 x 1) and M3: 2 (1 x 2).

Then we have other table “MaterialInventoryOnHand” that keep the latest inventory quantity of each material(s).

DECLARE @MaterialInventoryOnHand TABLE
(
Material varchar(15),
Quantity smallint -- quantity on hand
);
-- create some sample data on hand
INSERT INTO @MaterialInventoryOnHand VALUES
('M1', 100),
('M2', 10),
('M3', 10),
('M4', 1),
('M5', 25);

Next, we need to statistic and calculate of current production work for the material(s) to decide if we could make our final goods without any issue? The below script summary the material for the manufacturing data but also record down the WO# among the GROUP BY action.

;WITH Sum_Material_Qty_CTE AS
(
SELECT
Material,
STUFF((
SELECT ',' + WorkOrder
FROM @ProductionWork AS S
WHERE O.Material = S.Material
FOR XML PATH('')
), 1, 1, '') AS WO_SEQ,
SUM(O.ComsumeQty) as TotalConsumeQtyPerMaterial
FROM
(
SELECT
WorkOrder,
Material,
Quantity * Multiplier AS [ComsumeQty]
FROM @ProductionWork
) AS O
GROUP BY Material
)
SELECT
S.Material,
WO_SEQ,
TotalConsumeQtyPerMaterial,
H.Quantity AS OnInvHandQty
FROM Sum_Material_Qty_CTE AS S
-- join back with on hand qty
LEFT JOIN @MaterialInventoryOnHand AS H
ON S.Material = H.Material

The key point here was the STUFF function match with FOR XML PATH function that help us keep trace of each WO# merge inside of GROUP BY.

Sum_Material_Qty_CTE

So according to the result as above. We could find that the work order: WO#1 might not be completed due to on hand inventory of M2 was shortage.

You could also use WO# as key to join back to table Sum_Material_Qty_CTE through:

-- caution for the performance for using full wildcard
✍️WO_SEQ LIKE '%,' + WorkOrder + ',%'
✍️WO_SEQ LIKE '%,' + WorkOrder✍️WO_SEQ LIKE WorkOrder + ',%'✍️WO_SEQ = WorkOrder

Reference

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store