SQL Help Please

In the sql code below, I need to modify it such that I get Part and Part_Site information even if an Inventory_Balance recode does not exist. I know most of you are much better at SQL than I am so your help would be greatly appreciated.

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

Jeff Graham
Cascade Consulting

LEFT JOIN INVENTORY_BALANCE
or
RIGHT JOIN INVENTORY_BALANCE

[infosonline.net]

Thanks for the suggestion but I have tried both and get the same result as without. I have tried changing the "LEFT OUTER JOIN" to "JOIN", "LEFT JOIN" AND "RIGHT JOIN" but all give the same result.

Any further suggestions?

Jeff Graham
Cascade Consulting



Edited 1 time(s). Last edit at 01/04/2022 09:25PM by Jeff Graham.

You are using one INNER JOIN between P (PART) and Y (INVENTORY_BALANCE)
INNER JOIN
( SELECT
PART_ID,
MAX (POSTING_DATE) MaxDate
FROM
INVENTORY_BALANCE
GROUP BY
PART_ID
) y
ON y.PART_ID = p.ID

Regards

Paulo Oliveira

Hi Graham

Without knowing the exact data model, I think the below should do the trick somehow...

Just not sure on the data model. I would expect to have the INEVNTORY_BALANCE table to be joined on PartID and SiteID since you would typically hold inventory per site/branch/plant/... for an item. You are only joining it on the part.
Also wondering why quantities are being stored on the Parts table instead of the Inventory table. This seems weird design to me...

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

Cheers

Peter H.

Thanks, Peter that may work. I am testing it.

Jeff Graham
Cascade Consulting



Edited 1 time(s). Last edit at 01/05/2022 06:34PM by Jeff Graham.