November 1, 2018 at 10:47 am
Hey guys! I am trying to rebuild an old inventory report and have ran into a bit of an issue. As stated in the title I am trying to find the total amount of each item. So far, I have gotten it to the point where it shows accurate results, but the goal is to have only one instance of each item in the result set along with a total or "System_Count". These are the results that I have gotten so far. The results circled in red should be one result with the sum, for example, with 2500005519 I want to see one result with a system count of 34. Thanks ahead of time to whoever can assist!
SELECT Part_LocationsReorderLevel.Bin, Part.PartNum, Part.Description, UnitOfMeasure.Code, WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized AS System_Count
FROM Inventory_Utilization LEFT OUTER JOIN (WorkRequest_PartRequest LEFT OUTER JOIN
(Part_Locations LEFT OUTER JOIN ((UnitOfMeasure LEFT OUTER JOIN Part ON UnitOfMeasure.LegacyKey = Part.Part_UnitOfMeasureID) LEFT OUTER JOIN
Part_LocationsReorderLevel ON Part.ID = Part_LocationsReorderLevel.PartID) ON Part_Locations.ID = Part_LocationsReorderLevel.Part_LocationsID)
ON WorkRequest_PartRequest.Part_PartID = Part.PartID) ON Inventory_Utilization.Part_PartID = Part.PartID
WHERE Part_Locations.Description ='FILTER HOUSE'
GROUP BY Part_LocationsReorderLevel.Bin, Part.PartNum, Part.Description, UnitOfMeasure.Code, WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized
Order By [Part_LocationsReorderLevel].[Bin];
November 1, 2018 at 10:55 am
Replace this WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized
with SUM(WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized)
and then take the expression out of the GROUP BY clause also.
November 1, 2018 at 11:08 am
mark.humphreys - Thursday, November 1, 2018 10:55 AMReplace this WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized
with SUM(WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized)
and then take the expression out of the GROUP BY clause also.
You would think that this code would be correct (I tried that before posting here), but it gives you some absurdly large and incorrect numbersSELECT Part_LocationsReorderLevel.Bin, Part.PartNum, Part.Description, UnitOfMeasure.Code, SUM(WorkRequest_PartRequest.QuantityRequested - Inventory_Utilization.QuantityUtilized) AS System_Count
FROM Inventory_Utilization LEFT OUTER JOIN (WorkRequest_PartRequest LEFT OUTER JOIN
(Part_Locations LEFT OUTER JOIN ((UnitOfMeasure LEFT OUTER JOIN Part ON UnitOfMeasure.LegacyKey = Part.Part_UnitOfMeasureID) LEFT OUTER JOIN
Part_LocationsReorderLevel ON Part.ID = Part_LocationsReorderLevel.PartID) ON Part_Locations.ID = Part_LocationsReorderLevel.Part_LocationsID)
ON WorkRequest_PartRequest.Part_PartID = Part.PartID) ON Inventory_Utilization.Part_PartID = Part.PartID
WHERE Part_Locations.Description ='FILTER HOUSE'
GROUP BY Part_LocationsReorderLevel.Bin, Part.PartNum, Part.Description, UnitOfMeasure.Code
Order By [Part_LocationsReorderLevel].[Bin];
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply