SQL Help Please January 02, 2022 09:11PM |
Registered: 5 years ago Posts: 74 |
SELECT P.ID, P.Description, P.USER_2, P.PRODUCT_CODE, P.QTY_ON_HAND, p.STOCK_UM, PS.unit_price, PS.UNIT_MATERIAL_COST, PS.UNIT_LABOR_COST, PS.UNIT_BURDEN_COST, PS.UNIT_SERVICE_COST, P.STOCK_UM, SUM(X.QTY) AS Inv_Qty, SUM( X.MATERIAL_AMOUNT + X.LABOR_AMOUNT+X.BURDEN_AMOUNT+X.SERVICE_AMOUNT) AS Inv_Value, SUM(PS.UNIT_MATERIAL_COST+PS.UNIT_LABOR_COST+PS.UNIT_BURDEN_COST+PS.UNIT_SERVICE_COST) AS Unit_Site_Cost FROM PART P LEFT OUTER JOIN INVENTORY_BALANCE x ON P.ID = x.PART_ID INNER JOIN ( SELECT PART_ID, MAX (POSTING_DATE) MaxDate FROM INVENTORY_BALANCE GROUP BY PART_ID ) y ON y.PART_ID = p.ID AND x.POSTING_DATE = MaxDate JOIN PART_SITE PS ON P.ID = PS.PART_ID WHERE P.USER_2 = 'QUAC' --AND P.QTY_ON_HAND > 0 GROUP BY p.ID, P.Description, P.QTY_ON_HAND, p.USER_2, P.PRODUCT_CODE, P.Description, PS.unit_price, PS.UNIT_MATERIAL_COST, PS.UNIT_LABOR_COST, PS.UNIT_BURDEN_COST, PS.UNIT_SERVICE_COST, P.STOCK_UM ORDER BY p.PRODUCT_CODE, p.ID
Re: SQL Help Please January 02, 2022 11:23PM |
Registered: 5 years ago Posts: 236 |
Re: SQL Help Please January 04, 2022 09:16PM |
Registered: 5 years ago Posts: 74 |
Re: SQL Help Please January 05, 2022 10:45AM |
Registered: 5 years ago Posts: 296 |
Re: SQL Help Please January 05, 2022 11:51AM |
Registered: 3 years ago Posts: 107 |
SELECT P.ID, --PS.SITE_ID, ??? P.Description, P.USER_2, P.PRODUCT_CODE, AVG(P.QTY_ON_HAND), P.STOCK_UM, AVG(PS.unit_price), AVG(PS.UNIT_MATERIAL_COST), AVG(PS.UNIT_LABOR_COST), AVG(PS.UNIT_BURDEN_COST), AVG(PS.UNIT_SERVICE_COST), SUM(X.QTY) AS Inv_Qty, SUM(X.MATERIAL_AMOUNT + X.LABOR_AMOUNT+X.BURDEN_AMOUNT+X.SERVICE_AMOUNT) AS Inv_Value, AVG(PS.UNIT_MATERIAL_COST+PS.UNIT_LABOR_COST+PS.UNIT_BURDEN_COST+PS.UNIT_SERVICE_COST) AS Unit_Site_Cost, MAMX.POSTING_DATE) AS MaxDate FROM PART P INNER JOIN PART_SITE PS ON P.ID = PS.PART_ID AND P.USER_2 = 'QUAC' LEFT OUTER JOIN INVENTORY_BALANCE X ON P.ID = X.PART_ID --AND PS.SITE_ID = X.SITE_ID ??? LEFT OUTER JOIN INVENTORY_BALANCE MX ON P.ID = MX.PART_ID --AND PS.SITE_ID = MX.SITE_ID ??? GROUP BY P.ID, --PS.SITE_ID, ??? P.Description, P.USER_2, P.PRODUCT_CODE, P.STOCK_UM ORDER BY P.PRODUCT_CODE, --PS.SITE_ID P.ID
Re: SQL Help Please January 05, 2022 06:27PM |
Registered: 5 years ago Posts: 74 |