My user would like to run my BAQ using the ClockInDate. I’m assuming the only table that this field appears in the LaborDtl table.
I’m trying to link this “ClockInDate” to the Top Level. The Top Level table is JobHead. All other subqueries are joined to JobHead by PartNum. Since the LaborDtl DOES NOT contain the PartNum field I have linked it to the JobAsmbl table joining the two using Company, JobNum and AssemblySeq fields.
The subquery works “on its own” but when added to the Top Level I get zero records. I’m sorry this isn’t working “on its own”…
So what you are saying is that Subquery should have the LaborDtl and JobOper/JobOpDtl tables but the JobOper Table would be the one “linked” to the JobHead Table in the Top Level - correct?
I’ve been working on a BAQ that reports the following:
Total number of Estimated set up hours
Total number of Actual set up hours
Total number of Estimated set up’s
Total number of Actual set up’s
Total number of times that jobs were run
Etc.
But now the user who will be using this BAQ wants to be able to run a report for perhaps 1st Quarter, 2nd Quarter, and other time frames.
Job Prod, JobHead and JobOper has all that info. not sure you need labordtl at all. calculated field on
sum(JobOper.ActProdHours) to get actuals.
Ive used Labordtl as a subquery to find things like the first time a job was clocked to
You have a start date on the job head. Now that’s based on the schedule, and not by when someone actually start the job…
This is where the crux of your problem is though. You need to define what it means to “Be between these dates”. Do you want to include the whole job information? Or just the job information for the labor that is collected between those date? If it’s the whole job, what milestone defines the date to filter off of? The scheduled start date? The finished date? The closed date? the date the first person clocked into the job? The date the part was received to inventory?
I know it’s nitpicky, and the end user probably 1.) doesn’t even realize that their definition is vague and 2.) probably doesn’t even care.
But nonetheless someone has to decide that distinction to be able to make the BAQ.
As with all of these, imagine that you have each table printed out on a piece of paper and you have to decide which data to take the where the very specific and repeatable cutoffs/joins are. Once you have the logical design, then you can start looking at making the code/BAQ to turn that into something useful.
Break it down into smaller and smaller pieces, then join them together to the whole.
Do a sub query with JobHead and labordtl grouped by company and JobNum then do a calculated field that is the MIN(laborDtl.ClockInDate) to get first labor date.
Oddly, if you wanted last labor date that Epicor saves on each JobOper
Just the first date that the first set-up was started within a date range…so we could have three jobs for the same part, two are closed one is open and we want to see the data for the set-up’s for that part during that time frame.
Does that help?
I have taken the subqueries apart - but even when I try to run this all by it’s own it doesn’t play fair when added to the Top Level.
to get the first time a job was started I used a subquery left joining back on job head to get all jobs. im doing this to compare an actual start (labor) to a planned start (jobhead)
The labor dtl has the job number already in it, so no real reason to bring in the job header table on that subquery. Just laborDtl table, with Company and Job grouped, and the calculated field that @Craig showed there. Although I would probably use the laborDtl.ClockinDate, but that’s probably splitting hairs.
I have tried every which way i can think of to link the LaborDtl table to my upper level which is using JobHead. At this point they are linked with Company and Job grouped, and the calculated field that @Craig showed, I am still receiving zero records.
Any insight would be much appreciated - If I can’t use ClockInDate is there ANY OTHER Date field that represents when a Job was ACTUALLY started?
I have pulled the following code out of the Top Level BAQ and obviously have tried running it on its own and still receive the zero results.
select
[JobHead].[JobNum] as [JobHead_JobNum],
(count( LaborDtl.LaborType )) as [Calculated_TotalNumberofSU],
(min( LaborDtl.ClockInDate )) as [Calculated_FirstLaborDate]
from Erp.JobHead as JobHead
left outer join Erp.LaborDtl as LaborDtl on
LaborDtl.Company = JobHead.Company
and LaborDtl.JobNum = JobHead.JobNum
and ( LaborDtl.LaborType = 'S' )
where (JobHead.PartNum = @PartNum)
group by [JobHead].[JobNum]
having (min( LaborDtl.ClockInDate )) = @StartDate
In advance i thank you all for your VERY generous help!
Judy,
I get results using the BAQ below. As a test what if you remove the having subquery criteria (having (MIN(LaborDtl.ClockInDate)) = @StartDate), do you get results then?
