I have a requirement to gather all Jobs in a BAQ and check if the group of jobs had any testing applied to it per Sales order line.
Our Job number appears like following
172681-1-1
172681-1-2
172681-1-3
Its made up of First 6 characters refer to the sales order number, the next 2 characters are the sales order line, and then following 2 are the Sales Order release
I need to check each group of jobs which are all related to the same Sales order line and if all the Jobs ‘Job Code’ field do not contains a * then display the Sales Order Line as Not tested.
What have you got so far? To relate the jobs to the order table, you need to use the job prod table (assuming the jobs are make direct and linked properly, which I would assume they are given the standard job numbers). The job prod table will allow you to cross reference from job to order and line (and release too if you needed it)
I have made 2 subqueries -
Jobs - one to collect all jobs where Job code doesnt contain a * and grouped by Sales Order Line (calculated trimmed field from job num)
OrderDtl to collect all Order lines where Max test days > 3 and grouped by Sales Order Line (calculated field convert(varchar, OrderDtl.OrderNum) + ‘-’ + convert(varchar,OrderDtl.OrderLine)) to build the Sales Order Line
Then I have joined both subqueries together by the calculated Sales Order Lines
Problem is im seeing Sales Order lines where the jobs did have a *
What do you have on your Jobs query? You can create a sum field and use a case when clause inside that .
Sum ( case when JobCode like ‘%*%’ then 1 else 0 end) and on the top level query set your where clause to filter base on the calculated field equals to zero. Also I think if you use JobProd to link jobs to the order detail will be much faster than a calculated field, then you can link JobProd to JobHead.
