Get Department where there are no labor detail records

, ,

Good morning,
I have a Productivity UBAQ that pulls timeclock information and tries to show it along with Epicor time data. I can’t figure out how to get the BAQ to pull all the departments, regardless of whether there is a labor detail record or not. For example, our Drivers are a department in Epicor, but they never clock into Epicor, only our timeclock software. I still need the BAQ to return the drivers department so that I can pull down my timeclock hours even when we don’t have any Epicor labor data for that department.

Since my BAQ is really a UBAQ with a BPM to access the API, I will just post the SQL syntax. I assume I have the joins mixed up. If you really need me to, I can post the BAQ with the BPM part removed. Let me know what is the best way.

/*
 * 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 
	[zDates].[Calculated_zDate] as [Calculated_zDate],
	[EffHours].[JCDept2_Description] as [JCDept2_Description],
	[EffHours].[JCDept2_JCDept] as [JCDept2_JCDept],
	[EffHours].[LaborDtl_PayrollDate] as [LaborDtl_PayrollDate],
	(0) as [Calculated_pulled],
	(@StartDate) as [Calculated_sDate],
	(@EndDate) as [Calculated_eDate],
	(0) as [Calculated_TCHrs]
from  (select 
	(ROW_NUMBER() OVER(ORDER BY ZDataField.SysRevID ASC)) as [Calculated_Row_Num],
	(dateadd(day, Row_Num, '2/29/2024')) as [Calculated_zDate]
from Ice.ZDataField as ZDataField)  as zDates
left outer join  (select 
	[JCDept2].[JCDept] as [JCDept2_JCDept],
	[JCDept2].[Description] as [JCDept2_Description],
	[EarnedHours].[LaborDtl_PayrollDate] as [LaborDtl_PayrollDate]
from  (select 
	[LaborHed].[Company] as [LaborHed_Company],
	[JCDept1].[Description] as [JCDept1_Description],
	[JCDept1].[JCDept] as [JCDept1_JCDept],
	[LaborDtl1].[PayrollDate] as [LaborDtl1_PayrollDate],
	(sum(LaborDtl1.LaborHrs)) as [Calculated_LaborHours]
from Erp.LaborDtl as LaborDtl1
inner join Erp.LaborHed as LaborHed on 
	LaborHed.Company = LaborDtl1.Company
	and LaborHed.LaborHedSeq = LaborDtl1.LaborHedSeq
inner join Erp.EmpBasic as EmpBasic on 
	EmpBasic.Company = LaborHed.Company
	and EmpBasic.EmpID = LaborHed.EmployeeNum
inner join Erp.JCDept as JCDept1 on 
	LaborDtl1.Company = JCDept1.Company
	and LaborDtl1.JCDept = JCDept1.JCDept
group by [LaborHed].[Company],
	[JCDept1].[Description],
	[JCDept1].[JCDept],
	[LaborDtl1].[PayrollDate])  as DeptEff
inner join  (select 
	[JCDept].[Description] as [JCDept_Description],
	[LaborDtl].[PayrollDate] as [LaborDtl_PayrollDate],
	(sum(LaborDtl.EarnedHrs)) as [Calculated_ProdEH]
from Erp.LaborDtl as LaborDtl
inner join Erp.JCDept as JCDept on 
	LaborDtl.Company = JCDept.Company
	and LaborDtl.JCDept = JCDept.JCDept
group by [JCDept].[Description],
	[LaborDtl].[PayrollDate])  as EarnedHours on 
	DeptEff.JCDept1_Description = EarnedHours.JCDept_Description
	and DeptEff.LaborDtl1_PayrollDate = EarnedHours.LaborDtl_PayrollDate
right outer join Erp.JCDept as JCDept2 on 
	JCDept2.JCDept = DeptEff.JCDept1_JCDept)  as EffHours on 
	zDates.Calculated_zDate = EffHours.LaborDtl_PayrollDate
where (zDates.Calculated_zDate >= @StartDate  and zDates.Calculated_zDate <= @EndDate)

EDIT: Added Screenshots for context.




Thank you for your time!
Nate

Here’s one - inner join Erp.LaborHed
Also the inner join on your EarnedHours subquery.

I tried converting those inner joins to outter joins to the JCDept Table, but it did not change the output.

Ah, I missed that everything’s nested then joined on the date values. I’d cross apply* the departments on the date, then outer join the labor outside all the nesting.

* I wish cross/outer apply worked in BAQ. You can join departments on dates where 1=1 in this case.

1 Like

I decided to simplify it and rework it from the ground up. It is always the best approach. In the end, my BAQ only pulls the timeclock hours. I can use those numbers as they are for now. Thanks!