Hello I am trying to make a BAQ to show all open job’s where OP10 is complete but OP20 is not so this can be provided to the team that do OP20 “welding” is this possible.
1 Like
That should be pretty straight forward. JobOper is the table you’ll want to filter with. OprSeq is the op #. Join JobHead to JobAsmbl to JobOper. They should hook up automatically when you place them. This will return all assemblies. If you only have 1 assembly per job you can use AssemblySeq =0 in your filter. Finally, group by job number. You can also include any relevant job head info and group by that also.
1 Like
/*
- Disclaimer!!!
- 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].[Company] as [JobOper_Company],
[JobOper].[JobNum] as [JobOper_JobNum],
[JobHead1].[PartNum] as [JobHead1_PartNum],
[JobOper].[OpCode] as [JobOper_OpCode],
[JobOper].[OprSeq] as [JobOper_OprSeq],
[JobOper].[JobComplete] as [JobOper_JobComplete],
[Inner].[Calculated_Stock] as [Calculated_Stock]
from Erp.JobOper as JobOper
inner join (select
[JobOperInner].[Company] as [JobOperInner_Company],
[JobOperInner].[JobNum] as [JobOperInner_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobOperInner].[OpCode] as [JobOperInner_OpCode],
[JobOperInner].[OprSeq] as [JobOperInner_OprSeq],
[JobOperInner].[OpComplete] as [JobOperInner_OpComplete],
(sum(PartWhse.OnHandQty- PartWhse.DemandQty)) as [Calculated_Stock]
from Erp.JobOper as JobOperInner
inner join Erp.JobHead as JobHead on
JobOperInner.Company = JobHead.Company
and JobOperInner.JobNum = JobHead.JobNum
inner join Erp.PartWhse as PartWhse on
JobHead.Company = PartWhse.Company
and JobHead.PartNum = PartWhse.PartNum
where (JobOperInner.OpCode = ‘MACH’ and JobOperInner.OpComplete = 1)
group by [JobOperInner].[Company],
[JobOperInner].[JobNum],
[JobHead].[PartNum],
[JobOperInner].[OpCode],
[JobOperInner].[OprSeq],
[JobOperInner].[OpComplete]) as Inner on
JobOper.JobNum = Inner.JobOperInner_JobNum
and JobOper.Company = Inner.JobOperInner_Company
inner join Erp.JobHead as JobHead1 on
JobOper.Company = JobHead1.Company
and JobOper.JobNum = JobHead1.JobNum
where (JobOper.OpCode = ‘WELD’)
group by [JobOper].[Company],
[JobOper].[JobNum],
[JobHead1].[PartNum],
[JobOper].[OpCode],
[JobOper].[OprSeq],
[JobOper].[JobComplete],
[Inner].[Calculated_Stock]
order by Inner.Calculated_Stock
This seems to be showing just WELD despite it being completed
Can you send a few screenshots of the BAQ Designer? I think I’m following in the SQL but it looks like you might be grouping by too many things and/or I am misunderstanding or there’s a miscommunication of what you should be displaying.
It sounds like you are trying to replicate the functionality that is in the Dispatch report, or the display in MES that shows the same data.
We have three categories that show in the dispatch:
- Current Work (work that is 100% ready)
- Available work (work that has partial completions in the previous operation)
- Expected work (work that has no completions in the previous operation)
I have created a BAQ in the past that emulated this, but it was not an easy task. You have to analyze the previous step to find out what is complete. The report does this with code… and doing it with a Query is “fun”. (sorry, I dont have a copy of what I did… but it involved sub-queries and combining data from those subs to get the info I needed.).
One way we used to do this in the “old days” is with a bit of, well, bit math. In a subquery, create a calculated integer field and assign 1 if op 10 is complete and assign 2 if op 20 is complete. In binary, it would look like this:
01
10
Subtotal those fields. If the total is 2, then Op 20 is complete, If the total is 1, then Op 10 is complete. If the total is 3, then both operations are complete.
1 Like
Here is a query we use that replicates the MES / Dispatch that Tim was talking about. We use it in a dashboard with two other query’s that show outstanding material and all operation status. Query has one UD field on JobHead in Sub Query1 that will need to be removed to run.
WorkQueueSLC.baq (43.0 KB)
SELECT tbl.*, (SELECT MAX(tbl_t.OprSeq)
FROM Erp.JobOper tbl_t
WHERE tbl_t.JobNum = tbl.JobNum
AND tbl_t.OpComplete = ‘True’) as [Max_Job_Num]
FROM Erp.JobHead tbl WHERE JobClosed = 0 AND JobReleased = N’True’ AND JobComplete = N’False’
This query will return the last (not all) operation complete, in SSRS.
(and all columns)
There are 10 types of people in this world.
Those that understand binary, and those that don’t.
3 Likes
…should practice safe hex.
… ba dum tss
1 Like
Wow… bringing back memories… (STORY TIME): back when I was working as a programmer with a very limited system. every value you needed to store required at least one byte, even if true/false in our DB… I had an absolute maximum of 512 bytes before doing tons of magic to expand, but most of the time, i only had 256 or even 128 bytes per record… but sometimes I needed to have 8 boolean values, and I figured that I could turn a single byte field into 8 booleans by simply building the binary values into the 8 bits of a byte and storing it away. Of course, every time i wanted to use the value, i had to parse it back out again, but this saved me much needed/valuable disc space. Remember that in those days, I only had 32 mb per logical disk, and the physical disk only had 200mb… so everything was at a premium.
(End of Story Time)