hello can someone look at my query bellow im trying to pull in all sku’s with free stock and showing an open PO against the sku if there is one from one of our supplier however when i do it , the query only shows sku’s with open PO’s i want it to show all sku’s regardless if they have a PO or not
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/select
[Part].[Company] as [Part_Company],
[Part].[PartNum] as [Part_PartNum],
(Sum(PartBin.OnhandQty )) as [Calculated_OnHandQty],
(sum(PartBin.OnhandQty -PartBin.SalesAllocatedQty- PartBin.SalesPickingQty- PartBin.SalesPickedQty)) as [Calculated_FreeStco],
[Part].[plmin_c] as [Part_plmin_c],
[Part].[plmax_c] as [Part_plmax_c]
from Erp.Part as Part
inner join Erp.PartBin as PartBin on
Part.Company = PartBin.Company
and Part.PartNum = PartBin.PartNum
and ( PartBin.WarehouseCode = 'MAIN' or PartBin.WarehouseCode = 'UNIT5' or PartBin.WarehouseCode = 'MALTBY' )
inner join (select
[Vendor].[VendorID] as [Vendor_VendorID]
from Erp.PODetail as PODetail
inner join Erp.POHeader as POHeader on
PODetail.Company = POHeader.Company
and PODetail.PONUM = POHeader.PONum
inner join Erp.Vendor as Vendor on
POHeader.Company = Vendor.Company
and POHeader.VendorNum = Vendor.VendorNum
and ( Vendor.VendorID = 'ELST04' )
where (PODetail.OpenLine = TRUE)) as SubQuery2 on
SubQuery2. = Part.
where (Part.Company = @CurrentCompany)
and Part.Shown_c = TRUE
group by [Part].[Company],
[Part].[PartNum],
[Part].[plmin_c],
[Part].[plmax_c]