Multiple Table Joins

Transact-SQL
1

I've created the following script for the purpose of retrieving:

  1. Current Inventory Item quantities On Hand by Bin # where:
    a. quantities are greater than 0
    b. Items are in active status
  2. The Last Sale Date, Last Unit Cost of Sale, Last Unit Price of sale

The script is working with the following two exceptions:

  1. If the Inventory Item has no sales, it's excluded from the data. I need to see the current on hand quantities with a null value in the Last Sale column
  2. If there were two or more invoices on the last sale date, and the unit price is different on the Invoices, both sales are listed. I need to see the invoice with the highest sale price.

Any help appreciated.

Select DISTINCT
B.ITEMNMBR AS ITEM_NUMBER,
I.ITEMDESC AS ITEM_DESCRIPTION,
B.BIN,
B.QUANTITY,
(CASE
When B.ITEMNMBR = ' ' then ' '
ELSE MD.Last_Sale
END) AS Last_Sale,
MP.Max_Price AS Unit_Price,
I.CURRCOST AS Unit_Cost_FOB,
I.ITMCLSCD AS CLASS,
I.USCATVLS_1 AS SOURCE
from IV00112 B
INNER JOIN SOP30300 HH
ON B.ITEMNMBR = HH.ITEMNMBR
left outer JOIN sop30200 HL
ON HL.SOPTYPE = HH.SOPTYPE AND HH.SOPNUMBE = HL.SOPNUMBE
inner join
(select max(H.DOCDATE) Last_Sale, D.ITEMNMBR
from SOP30200 H
inner join SOP30300 D
on H.SOPTYPE = D.SOPTYPE AND H.SOPNUMBE = D.SOPNUMBE
where H.SOPTYPE = 3 AND H.VOIDSTTS = 0
group by D.ITEMNMBR) MD --MAX DATE
on HH.ITEMNMBR = MD.ITEMNMBR AND HL.DOCDATE = MD.Last_Sale
inner join
(select H.DOCDATE, D.ITEMNMBR, max(D.UNITPRCE) Max_Price
from SOP30200 H
inner join SOP30300 D
on H.SOPTYPE = D.SOPTYPE AND H.SOPNUMBE = D.SOPNUMBE
where H.SOPTYPE = 3 AND H.VOIDSTTS = 0
group by H.DOCDATE, D.ITEMNMBR) MP --max price
on HH.ITEMNMBR = MP.ITEMNMBR
and HH.UNITPRCE = MP.Max_Price
and MP.DOCDATE = MP.DOCDATE
LEFT OUTER JOIN IV00101 I
ON I.ITEMNMBR = B.ITEMNMBR
WHERE B.QUANTITY <> 0
order by B.ITEMNMBR, B.BIN, B.QUANTITY

2

Difficult to tell without DDL and consumable test data.
At a guess I would start with something like:

WITH Sales
AS
(
	SELECT D.itemnmbr, H.docdate, D.UnitPrce
		,MAX(H.docdate) OVER (PARTITION BY D.itemnmbr) AS Last_Sale
		,ROW_NUMBER() OVER (PARTITION BY D.itemnmbr, H.docdate ORDER BY D.UnitPrce DESC) AS rn
	FROM sop30200 H
		JOIN sop30300 D
			ON H.soptype = D.soptype
				AND H.sopnumbe = D.sopnumbe
	WHERE H.soptype = 3
		AND H.voidstts = 0
)
SELECT B.itemnmbr AS ITEM_NUMBER
	,I.itemdesc AS ITEM_DESCRIPTION
	,B.bin
	,B.quantity
	,S.Last_Sale
	,S.UnitPrce AS Unit_Price
	,I.currcost AS Unit_Cost_FOB
	,I.itmclscd AS CLASS
	,I.uscatvls_1 AS SOURCE
FROM iv00112 B
	LEFT JOIN iv00101 I
		ON I.itemnmbr = B.itemnmbr
	LEFT JOIN Sales S
		ON B.itemnmbr = S.itemnmbr
			AND S.rn = 1
WHERE B.quantity <> 0
ORDER BY ITEM_NUMBER, bin, quantity;