December 23, 2011 at 7:43 am
The problem with this query is that the order of the UOMs is not meaningful. It just happens that the UOM for capacity comes before the UOM for weight in most instances, but that is not necessarily the case. The OP said that the UOM for capacity could be 'CS', 'HD', 'COMBO', 'BOX', 'PC' and that the UOM for weight was only 'LB'. As you can see, the capacity is sorted alphabetically before the weight in all cases except 'PC' and 'LB'. By incorporating the Products table we can incorporate meaning into the order of the UOMs and simultaneously reduce the number of CTEs that are required.
WITH Lots AS (
SELECT p.PartCode, l.Lot, l.Status, l.Warehouse
, Sum(CASE WHEN l.UOM = p.CapacityUOM THEN Qty END) AS Capacity_Qty
, Max(CASE WHEN l.UOM = p.CapacityUOM THEN UOM END) AS Capacity_UOM
, Sum(CASE WHEN l.UOM = p.WeightUOM THEN Qty END) AS Weight_Qty
, Max(CASE WHEN l.UOM = p.WeightUOM THEN UOM END) AS Weight_UOM
FROM #myLots AS l
INNER JOIN #myProducts AS p
ON l.PartCode = p.PartCode
GROUP BY p.PartCode, l.Lot, l.Status, l.Warehouse
)
, Bins AS (
SELECT p.PartCode, b.Lot, b.Status, b.Warehouse, b.Bin
, Sum(CASE WHEN b.UOM = p.CapacityUOM THEN Qty END) AS Capacity_Qty
, Max(CASE WHEN b.UOM = p.CapacityUOM THEN UOM END) AS Capacity_UOM
, Sum(CASE WHEN b.UOM = p.WeightUOM THEN Qty END) AS Weight_Qty
, Max(CASE WHEN b.UOM = p.WeightUOM THEN UOM END) AS Weight_UOM
FROM #myBins AS b
INNER JOIN #myProducts AS p
ON b.PartCode = p.PartCode
GROUP BY p.PartCode, b.Lot, b.Status, b.Warehouse, b.Bin
)
SELECT l.PartCode, l.Lot, l.Status, l.Warehouse, b.Bin
, b.Capacity_Qty, b.Capacity_UOM, b.Weight_Qty, b.Weight_UOM
, l.Capacity_Qty, l.Capacity_UOM, l.Weight_Qty, l.Weight_UOM
FROM Lots AS l
LEFT OUTER JOIN Bins AS b
ON l.PartCode = b.PartCode
AND l.Lot = b.Lot
AND l.Status = b.Status
AND l.Warehouse = b.Warehouse
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 23, 2011 at 8:46 am
I noticed that as well and for some reason was having touble incorporating the Product_Master table last night(fatigue I think) so was handling that with a formula field in the report to compare the UOM to the product_master UOM's to see if I was dealing with Capacity or Weight...However, I have modified the code to incorporate your suggestion and now I have only 2 cte's, less columns in the result set, and no longer have to analyze the QTY or UOM in the report...Thank You
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply