I am needing a simple, but seemingly difficult, baq with the joboper table. Basically, I want the job number and then three calc fields. Let’s call them A, B, C. The calc fields are case when JobOper.Opcode = A and JobOper.opcomplete = false then true else false end.
Now, all that is the easy part. What I am needing this to have one job number for all three. Right now, if I do the calc fields, even with them Grouped, I still get up to 4 lines. The first one has all three blank, then the next three lines are the same job number but one line for each of the calc fields. I’ve considered putting them into one calc filed, but I need to identify to the end user if each of these ops are completed, but only on one line.
This is not a real query being executed, but a simplified version for general vision.
Executing it with any other tool may produce a different result.
*/
select
[JobOper].[JobNum] as [JobOper_JobNum],
(case when joboper.opcode = ‘kit’ and JobOper.OpComplete = 1 then 1 else 0 end) as [Calculated_kit],
(case when joboper.opcode = ‘fcut’ and JobOper.OpComplete = 1 then 1 else 0 end) as [Calculated_fcut]
from Erp.JobOper as JobOper
where (JobOper.OpCode = ‘kit’ or JobOper.OpCode = ‘fcut’)
group by [JobOper].[JobNum],
(case when joboper.opcode = ‘kit’ and JobOper.OpComplete = 1 then 1 else 0 end),
(case when joboper.opcode = ‘fcut’ and JobOper.OpComplete = 1 then 1 else 0 end)
You don’t want to include the calculated fields in the GROUP BY. Put a MAX aggregate on the calculated fields because I assume you want it to return 1 if any of the ops are true.