How to keep SQL “GROUP BY” details/information for the purpose of tracking
Take MSSQL as example
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
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
Quantity smallint -- quantity on hand
);-- create some sample data on hand
INSERT INTO @MaterialInventoryOnHand VALUES
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 ',' + WorkOrder
FROM @ProductionWork AS S
WHERE O.Material = S.Material
FOR XML PATH('')
), 1, 1, '') AS WO_SEQ,
SUM(O.ComsumeQty) as TotalConsumeQtyPerMaterial
Quantity * Multiplier AS [ComsumeQty]
) AS O
GROUP BY Material
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.
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