Hi, I’ve been going a little nuts trying to figure out a way to get the results I want for a BAQ I’m writing.
I want to have a query that displays material (component) part num, parent part num, and the issued quantity for all jobs. I’ve used the JobHead and JobMtl tables to do this. I want my end result to be - by material part - the parent part that had the highest quantity issued. So every material part that has an issued quantity should only be listed once with the parent part it was issued to most.
To do this I first used the sum(JobMtl.IssuedQty) expression and group by function for a subquery:
This gave me a sum of all issued quantity of material part numbers by parent part number. Now in the next subquery I want the parent part that had the highest issued qty of each material part. For example I want only rows 1 and 3 in the sum subquery results below.
I can’t figure out how to do this. I was trying to find a way to use the top function for taking the first(highest issued quantity) result of each material part number but have had no luck with research. Any ideas?
That’s the first thing I tried to do. I created another sub query with the sum subquery in it, used group by and max(MtlPart.Calculated_IssuedQty) as my calculated quantity (just to be clear MtlPart.Calculated_IssuedQty = sum(MtlPart.IssuedQty) from the sum subquery). Unfortunately, it didn’t do anything.
I think it doesn’t work because I have both the material part number and the parent part number displayed. If I only have the material part number displayed I get the results I want but then I of course don’t have the parent part number. I’ve tried this and then linking another table that has the parent part number to my main subquery but I don’t know how to get a 1 to 1 relationship between the two because my options for table relationships are so limited.
What do you mean by “it didn’t do anything”? Can you post a screen shot of your results?
My guess would be you have the sum field included in the group by, by don’t fully understand what you are talking about with your description. I need more info.
Can you show me your subquery settings? I’m wondering if you have the subquery set as the top level.
Also, just for clarity, can you make your calculated fields different names? That might help.
EDIT I just noticed that your parent part numbers were not the same, (I thought they were). The query is acting as I would expect. You need the max for each mtl part number right? And you want to see which parent part number goes with that max? Use a windowing function.
create a calculated field (int)
ROW_NUMBER() OVER(PARTITION BY JobMtl.PartNum ORDER BY JobMtl.IssuedQty DESC)
Now create a main query
select the subquery you just created
add table selection to only show row = 1
select *
from (select
[JobMtl].[Company] as [JobMtl_Company],
[JobMtl].[JobNum] as [JobMtl_JobNum],
[JobMtl].[AssemblySeq] as [JobMtl_AssemblySeq],
[JobMtl].[PartNum] as [JobMtl_PartNum],
[JobMtl].[IssuedQty] as [JobMtl_IssuedQty],
(ROW_NUMBER() OVER(PARTITION BY JobMtl.PartNum ORDER BY JobMtl.IssuedQty DESC)) as [Calculated_Row]
from Erp.JobMtl as JobMtl) as JobMtlRowRank
where (JobMtlRowRank.Calculated_Row = 1)
this is all jobmtl ever. You will need to add the business logic; ie. open jobs, open assemblies, etc…
I pulled this subquery into another subquery where I then used Nash’s formula to create the “Row” calculated field but also included Company after the partition by section, thanks to seeing Brandon do this:
I then put this subquery into the final top level subquery and used Table Criteria = 1 constant for the calculated Row field:
