SO I’m trying to develop a dashboard that shows
Part:Description:Primary Vendor:Supplier Part Number:Last Cost:Avg Cost:UOMs:Calculated field (Avg vs last, display higest)
I THINK i have the query sort of working, but I have somewhere between 14 and 55 duplications for every part in my database, so even running a 50 part test takes 1-2 minutes.
I’m thinking it’s a join, but It could be something else, any guidance would be appreciated
select
[PartPlant].[PartNum] as [PartPlant_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
(case
when PartCost.AvgMaterialCost > PartCost.LastMaterialCost then PartCost.AvgMaterialCost
ELSE PartCost.LastMaterialCost
end) as [Calculated_HighMatCost],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
[VendPart].[VenPartNum] as [VendPart_VenPartNum],
[PartCost].[LastMaterialCost] as [PartCost_LastMaterialCost],
[PartCost].[AvgMaterialCost] as [PartCost_AvgMaterialCost],
[Part].[PUM] as [Part_PUM],
[Part].[SalesUM] as [Part_SalesUM],
[Part].[IUM] as [Part_IUM]
from Erp.PartPlant as PartPlant
inner join Erp.Vendor as Vendor on
PartPlant.Company = Vendor.Company
and PartPlant.VendorNum = Vendor.VendorNum
inner join Erp.VendPart as VendPart on
VendPart.Company = Vendor.Company
and VendPart.VendorNum = Vendor.VendorNum
inner join Erp.PartCost as PartCost on
PartCost.Company = VendPart.Company
and PartCost.PartNum = VendPart.PartNum
and PartCost.LastMaterialCost = VendPart.BaseUnitPrice
inner join Erp.Part as Part on
VendPart.Company = Part.Company
and VendPart.PartNum = Part.PartNum
where (PartPlant.Plant = 'MfgSys')
order by PartPlant.PartNum
Start a new BAQ, and just link two tables. See if you have duplicates with the fields you have.
If not then start adding them one at a time, and keep checking.
At whatever point you find duplicates, you will know where they are coming from, and you can see if you need to adjust your joins, or criteria to narrow it down, etc.
here’s a good link that I’ve used to help explain to people what joins are and the different types. BAQ is just SQl with a fancy GUI. You can change the type in the bottom right corner just above you criteria tables.
Edit: This was the one I used before. Both are fine, but this one has pictures, which I think help.
Thank you for the instructional materials in re Joins.
If I use just part plant, with part # displayed, no duplicates
I add Vendor, and display supplier ID, no duplicates
I display vendor name, no duplicates
I add Table VendPart, no display fields, duplicates
I display VenPartNum, still duplicates
In this scenario I tried all 16 potential relationships, none of them changed the level of duplication
I remove part # display field from Part Plant, no duplicates
Add back part cost, no duplicates
I’m guessing those PartNum are the same, they seem to be, and by using the VendPart_PartNum it resolved the duplication shurg
Thanks for all your help, guess it wasn’t the joins after all (unless there’s a way to do what I did with the display fields with the joins, then I’ll blame the joins again)
VendPart has Effectivity Logic. So, there can be (and probably are) more than one per part. This will definitely give you duplicates. Add the EffectiveDate to your display and you’ll see what I mean.
What this does is assigns the ROW_NUMBER to each record within the part in descending order (most recent to oldest). I did this is in a new query for demonstration purposes, and this only has the VendPart subquery in it. Notice what we now get:
We still have all the duplicates but now the most recent record is marked as 1 and each older record counts up. Armed with this subquery, we can filter everything else out except the 1s.
…only one record per part - the most recent VendPart record per part. Use this subquery in place of the VendPart table in your query, and things will look a whole lot better.
The moral of the story is to pick easier BAQs to start with!
