Working on a BAQ to show how many estimate hours are scheduled for a department, but it’s not that simple.
Our Jobs contain multiple operations for a department with only the last operation Time&Qty, the previous are Backflush.
I can create a subQuery to get this data, but the part that I’m trying to get around is we can use the same department multiple times on a job.
Eg
Dept 1 op1 Backflush .1hrs
Dept 1 op2 Backflush .1hrs
Dept 1 op3 Time&Qty .3hrs
Dept 2 op1 Time&Qty .2hrs
Dept 3 op1 Time&Qty .25hrs
Dept 1 op4 Time&Qty 1.5hrs
I’d like to see 4 buckets like this:
Dept 1 .5hrs
Dept 2 .2hrs
Dept 3 .25hrs
Dept 1 1.5hrs
I can’t seem to find a table that would link the Backflushed Op Seq to the T&Q op Seq. If there is something like this it would be the easiest, but if there isn’t, how could I stop the suwquery from combining all the Dept 1 Est hrs?
Are you only trying to group the backflush operations? Or all of the operations per dept if they are in a row?
For example, if we tack on another, non backflush operation to the end like so
Dept 1 op1 Backflush .1hrs
Dept 1 op2 Backflush .1hrs
Dept 1 op3 Time&Qty .3hrs
Dept 2 op1 Time&Qty .2hrs
Dept 3 op1 Time&Qty .25hrs
Dept 1 op4 Time&Qty 1.5hrs
Dept 1 op5 Time&Qty 1.5hrs
Should that end up like this
Dept 1 .5hrs
Dept 2 .2hrs
Dept 3 .25hrs
Dept 1 3hrs
or like this?
Dept 1 .5hrs
Dept 2 .2hrs
Dept 3 .25hrs
Dept 1 1.5hrs
Dept 1 1.5hrs
I’m trying to figure out a SQL way to do it, but haven’t got anything (yet). Worse case scenario, I would run a post processing BPM on the get list (so make it an updateable BAQ) and I could do it with C#.
What we are trying to do is create a Production Variance report because we are actually posting QTY only. A few years ago we discovered that Epicor double dipped when it came to Actual costs.
As an example when we had Time and Qty set, for Dept 1 The user would post against Op3 (since the other two are backflushed). They would post their total time, say .5 hrs, but Epicor would say the actual time for the backflush ops was at Std, so it would looke like they spent .7 hrs on the op and create a variance that wasn’t real, so we switched over to Qty only.
The good thing is Epicor still saves the Act Labor times, so this is why we are creating this BAQ to show actual Variances.
I think a CTE might be in order then. Since CTE’s can loop through, you can link the current operation to the previous operation (or next I suppose depending on how you set it up), then add things up as you go.
I think if you start with all operations that aren’t backflush, then join to “previous operation” that is backflush, and keep looping through that in the case of more than one backflush operation in a row, you should end up with what you are looking for.
I do this with a case statement in a subquery by Company and JobNum
SUM((Case When JobOpDtl.ResourceGrpID like 'ENG%'
or JobOpDtl.ResourceGrpID like 'CORE%'
or JobOpDtl.ResourceGrpID like 'HART%'
or JobOpDtl.ResourceGrpID like '%CUT%'
or JobOpDtl.ResourceGrpID like 'VST%'
Then JobOper.EstProdHours else 0 end))
SUM((Case When JobOpDtl.ResourceGrpID like 'MOLD%'
Then JobOper.EstProdHours else 0 end))
The trick is to only add up the prior backflush ops, but stop once I hit a different department (or resourcegroup ID) or another Qty only op.
I’m thinking I may be able to set a flag if I hit a different Dept or another Quantity op and have that flag as part of the case… Thanks, I think I’m going to try this. I’ll let you know.
I took another wack at this with a routine I use for job hours. I only showed the Time entries in the baq then sum the ops between.
You will obviously have to adapt the hrs query to your environment, but the top query sums the entire schedule into the list. Then goes thru the results and sums the ops between.
I only have a few jobs that are in any way close to your scenario, but the sums were good on those when I compared to job tracker.
ok, so it’s a bit clunky, and not very elegant, but, I have something that I believe will work.
Here’s SQL if you want to look at that.
/*
* 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
[infoLevel].[JobOper_JobNum] as [JobOper_JobNum],
[infoLevel].[JobOper_AssemblySeq] as [JobOper_AssemblySeq],
[infoLevel].[JobOper_OprSeq] as [JobOper_OprSeq],
[infoLevel].[JobOper_OpCode] as [JobOper_OpCode],
[infoLevel].[JobOper_LaborEntryMethod] as [JobOper_LaborEntryMethod],
[infoLevel].[Calculated_RowToSkip] as [Calculated_RowToSkip],
(case when infoLevel.JobOper_LaborEntryMethod = 'B' then
lead(infoLevel.JobOper_OprSeq, infoLevel.Calculated_RowToSkip
) over (partition by infoLevel.JobOper_JobNum order by infoLevel.JobOper_OprSeq)
else infoLevel.JobOper_OprSeq
end) as [Calculated_RollUpOp01]
from (select
[JobOper].[JobNum] as [JobOper_JobNum],
[JobOper].[AssemblySeq] as [JobOper_AssemblySeq],
[JobOper].[OprSeq] as [JobOper_OprSeq],
[JobOper].[OpCode] as [JobOper_OpCode],
[JobOper].[LaborEntryMethod] as [JobOper_LaborEntryMethod],
(case
when lead(JobOper.LaborEntryMethod,1) over (partition by JobOper.JobNum order by JobOper.OprSeq) = 'T' then 1
when lead(JobOper.LaborEntryMethod,2) over (partition by JobOper.JobNum order by JobOper.OprSeq) = 'T' then 2
when lead(JobOper.LaborEntryMethod,3) over (partition by JobOper.JobNum order by JobOper.OprSeq) = 'T' then 3
when lead(JobOper.LaborEntryMethod,4) over (partition by JobOper.JobNum order by JobOper.OprSeq) = 'T' then 4
when lead(JobOper.LaborEntryMethod,5) over (partition by JobOper.JobNum order by JobOper.OprSeq) = 'T' then 5
else 1
end) as [Calculated_RowToSkip]
from Erp.JobOper as JobOper) as infoLevel
But basically, in a subquery, you figure out how many rows you need to skip using the lead function. I haven’t figured out how to let it reference itself in order to keep going, so I had to copy it a bunch of times. If you need more, just copy paste and change the numbers. I can’t imagine you needing more than 5.
I just saw this reply, sorry! Let me take a closer look at what you just did and I’ll reply back. Just wanted to let you know I just saw this. I appreciate you taking the time to write that up!
