So, I have been asked to pull a list that has order number, job number, on hand qty, demand, qty, WIP, calculated Available Qty, and Requested date. As such, I get multiple lines of the same info. How do I remove those multiple lines?
The Left and right joins, are me trying to figure how to limit the duplicates while still getting my info.
If you add one table at a time, when do the duplicates start. That is where your join will need some work.
Post your query as we will be able to help quicker seeing that.
I am guessing that you are using Part as your start table? If so, it should be #1 not #2.
Then every join should be coming off the part table as long as the connecting table has part id in it.
Well, I’ll post the query and see what you guys think further. The duplicates seem to happen after I added the orderdtl. But that is the only place to get the ordernum that I am aware of. And we want to sort it by Requestdate, thus needing the orderdtl.
select
[Part].[PartNum] as [Part_PartNum],
[JobPart].[JobNum] as [JobPart_JobNum],
[Part].[PartDescription] as [Part_PartDescription],
[PartPlant].[MinimumQty] as [PartPlant_MinimumQty],
[PartPlant].[MaximumQty] as [PartPlant_MaximumQty],
[PartWhse].[DemandQty] as [PartWhse_DemandQty],
[PartWhse].[OnHandQty] as [PartWhse_OnHandQty],
[PlantWhse].[PrimBin] as [PlantWhse_PrimBin],
(JobPart.PartQty-JobPart.ReceivedQty) as [Calculated_WIPQTY],
(PartWhse.OnHandQty-PartWhse.DemandQty) as [Calculated_AVAILQTY],
[PartPlant].[PrimWhse] as [PartPlant_PrimWhse],
[OrderHed].[RequestDate] as [OrderHed_RequestDate]
from Erp.Part as Part
inner join Erp.PartPlant as PartPlant on
Part.Company = PartPlant.Company
And
Part.PartNum = PartPlant.PartNum
and ( PartPlant.PrimWhse like ‘FG-’ )
inner join Erp.PartWhse as PartWhse on
PartWhse.Company = PartPlant.Company
And
PartWhse.PartNum = PartPlant.PartNum
And
PartWhse.WarehouseCode = PartPlant.PrimWhse
inner join Erp.PlantWhse as PlantWhse on
PartPlant.Company = PlantWhse.Company
And
PartPlant.PartNum = PlantWhse.PartNum
And
PartPlant.Plant = PlantWhse.Plant
and ( PlantWhse.PrimBin = ‘STOCK’ or PlantWhse.PrimBin = ‘CONSIGN’ )
left outer join Erp.JobPart as JobPart on
JobPart.Company = Part.Company
And
JobPart.PartNum = Part.PartNum
and ( not JobPart.JobNum like ‘UNF’ and JobPart.JobComplete = false )
full outer join Erp.OrderDtl as OrderDtl on
OrderDtl.Company = Part.Company
And
OrderDtl.PartNum = Part.PartNum
and ( OrderDtl.RequestDate = GETDATE() and OrderDtl.RequestDate >= DATEADD (day, 1, GETDATE()) )
full outer join Erp.OrderHed as OrderHed on
OrderDtl.Company = OrderHed.Company
And
OrderDtl.OrderNum = OrderHed.OrderNum
where (Part.NonStock = false and Part.RunOut = false and Part.TypeCode = ‘M’)
So you are seeing duplicates then for each line of the order. You should add that to your query output.
Do you need to filter by only open orders? I don’t see a filter on that table, so it will grab old orders too.
First off, your SQL and the picture of the tables do not match. The picture does not contain Order header.
Second, you have 2 full outer joins in the SQL. A full outer join pulls all records from both tables regardless if there is a match.
John, you are correct. My apologies for not taking that off before copying. To everyone that has replied so far, Thanks! I am going to add in a change a few things. I’ll let you know how its going and the results. Thanks you!
Here is the updated Query:
select
[Part].[PartNum] as [Part_PartNum],
[JobPart].[JobNum] as [JobPart_JobNum],
[Part].[PartDescription] as [Part_PartDescription],
[PartPlant].[MinimumQty] as [PartPlant_MinimumQty],
[PartPlant].[MaximumQty] as [PartPlant_MaximumQty],
[PartWhse].[DemandQty] as [PartWhse_DemandQty],
[PartWhse].[OnHandQty] as [PartWhse_OnHandQty],
[PlantWhse].[PrimBin] as [PlantWhse_PrimBin],
(JobPart.PartQty-JobPart.ReceivedQty) as [Calculated_WIPQTY],
(PartWhse.OnHandQty-PartWhse.DemandQty) as [Calculated_AVAILQTY],
[PartPlant].[PrimWhse] as [PartPlant_PrimWhse],
[OrderDtl].[RequestDate] as [OrderDtl_RequestDate],
[JobOper].[OprSeq] as [JobOper_OprSeq],
[JobOper].[OpCode] as [JobOper_OpCode],
[JobOper].[AssemblySeq] as [JobOper_AssemblySeq]
from Erp.Part as Part
inner join Erp.PartPlant as PartPlant on
Part.Company = PartPlant.Company
And
Part.PartNum = PartPlant.PartNum
and ( PartPlant.PrimWhse like ‘FG-’ )
inner join Erp.PartWhse as PartWhse on
PartWhse.Company = PartPlant.Company
And
PartWhse.PartNum = PartPlant.PartNum
And
PartWhse.WarehouseCode = PartPlant.PrimWhse
inner join Erp.PlantWhse as PlantWhse on
PartPlant.Company = PlantWhse.Company
And
PartPlant.PartNum = PlantWhse.PartNum
And
PartPlant.Plant = PlantWhse.Plant
and ( PlantWhse.PrimBin = ‘STOCK’ or PlantWhse.PrimBin = ‘CONSIGN’ )
left outer join Erp.JobPart as JobPart on
JobPart.Company = Part.Company
And
JobPart.PartNum = Part.PartNum
and ( not JobPart.JobNum like ‘UNF’ and JobPart.JobComplete = false )
left outer join Erp.OrderDtl as OrderDtl on
Part.Company = OrderDtl.Company
And
Part.PartNum = OrderDtl.PartNum
and ( OrderDtl.VoidLine = false and OrderDtl.OpenLine = true and OrderDtl.RequestDate >= GETDATE() )
left outer join Erp.JobOper as JobOper on
Part.Company = JobOper.Company
And
Part.PartNum = JobOper.PartNum
where (Part.NonStock = false and Part.RunOut = false and Part.TypeCode = ‘M’)
This gets me really close to what i am looking for without to many duplicates. Then I put it into SSRS and make the report. At this juncture I get everything to hide and show as I want it to. However, the white space from the hidden info won’t go away. But the big issue of the few remaining duplicates is that there are job numbers that are being mentioned multiple times for the same part number at the same ship date, with the exact quantity.
I would imagine the Job records are being multiplied by JobOper.
plus if you have multiple lines on an order for the same part will cause dups.
Ok, so really no good way to fix this then, correct? That’s my thought, but wanted to see what others thought.
If it were me, I would change the following.
Join Part and PartWhse directly
Join Part and PlantWhse directly
Use JobHead instead of JobPart
Join JobHead and JobOper directly
It would also be good to see what the order of your tables is. Can you post a screenshot of that?
plus then use JobProd to get the order detail information. (as you can then join to the orderdtl table from here)
1 Like
You can also create a DISTINCT function by going Query Builder > SubQuery Options>Result Set rows>Distinct.
This mimics doing a SELECT DISTINCT in SQL SERVER
You might need to play around with any ORDER BY clauses you as it seems to affect them for some reason.
As an update, I’ve started to redo this with jkane ideas. However, I have a more pressing issue to work on. So I will revisit this in about a week or so.
Thanks for all the great suggestions, ideas, and help!