We did our monthly inventory and found we had some missing material. So, they asked me to run a report that shows us which jobs we have that have no material issued to the job. (Will display what I have below.) After I ran the report they went to look at it in Job tracker and under the Material in the tree the Material piece has a check mark by it. I am told that means it has been issued. After a crazy day yesterday I wanted to reach out for some help.
Not above being way off and need to stat over, just trying to understand somethings.
So here is what I have:
select
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobMtl].[IssuedComplete] as [JobMtl_IssuedComplete],
[JobMtl].[IssuedQty] as [JobMtl_IssuedQty],
[JobOper].[OpCode] as [JobOper_OpCode]
from Erp.JobHead as JobHead
inner join Erp.JobMtl as JobMtl on
JobHead.Company = JobMtl.Company
And
JobHead.JobNum = JobMtl.JobNum
and ( not JobMtl.PartNum like ‘%ink%’ and JobMtl.IssuedQty = 0 )
inner join Erp.JobOper as JobOper on
JobHead.Company = JobOper.Company
And
JobHead.JobNum = JobOper.JobNum
and ( JobOper.OpCode like ‘%print%’ )
where (JobHead.PartDescription like ‘%23 mil pol%’ and JobHead.ReqDueDate >= @start and JobHead.ReqDueDate <= @end)
group by [JobHead].[JobNum],
[JobHead].[PartNum],
[JobMtl].[IssuedComplete],
[JobMtl].[IssuedQty],
[JobOper].[OpCode]
Material Transactions say 17.00. JobMtl.IssuedQty says 0.00. Maybe I am a little confused on what the IssuedQty is referring to. I am guessing in the Part Trans that it shows the most recent input; whether it be an adjustment or issued material, correct?
I see what’s wrong, you are grouping by job head part number, and looking for material part numbers that have 0 issued. The third material on you job isn’t checked, that’s returning your 0 line and making that job show up in the report.
Ungroup the stuff and remove your filters and you will see it. (just filter by this job number.)
I would start with your JobMtl table and only add in the things that you need that aren’t in there. You are grabbing your job header part number which I think is confusing you. Just do a query that looks at the job mtl table and finds all material sequences that aren’t issued. Then when you get that filtered down to what you want, start adding in the other tables you need to filter out other things. For example, I would then bring in the JobAsm table so that I can bring in the JobOp table and filter by operation. After that is working, I would bring in the JobHead table to get your head part number to filter out.
Basically, start with the granular data and work up from there. Don’t use any aggregate functions that require you to group things until you’ve checked what the ungrouped rows are returning first and verify that your filters are working. Then group and sum things. For this specific query, I don’t see a need to group anything. You need to see the individual material line, so if you just work with your filters, there should be no reason to group anything.
Brandon – Would you say that this is the normal progression/approach in constructing BAQs generally (to start granular and work up from there), or does the suggested approach you’ve outlined only apply for this particular case?
I would say it’s always a good approach. As you get better, you may be able to skip some of the testing, but I get caught a lot thinking I know what’s going to work and skipping testing steps only to get burned by it later. I wish there were better ways to test sub queries more easily because it can be a pain to test things as you are adding subs and the only way to test sometimes is to recreate peices in a totally different query.
