# Get Department where there are no labor detail records

**URL:** <https://www.epiusers.help/t/get-department-where-there-are-no-labor-detail-records/113466>\
**Category:** Kinetic ERP\
**Tags:** baq, e10, bpm\
**Created:** [April 5, 2024, 3:38pm UTC](https://www.epiusers.help/t/get-department-where-there-are-no-labor-detail-records/113466 "2024-04-05T15:38:23Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![NateS](https://www.epiusers.help/user_avatar/www.epiusers.help/nates/32/18519_2.png) [@NateS](https://www.epiusers.help/u/NateS)\
**Post date:** [April 5, 2024, 3:38pm UTC](https://www.epiusers.help/t/get-department-where-there-are-no-labor-detail-records/113466/1 "2024-04-05T15:38:23Z")

</div>

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.

```sql
/*
 * 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.

 ![Capturea634](https://www.epiusers.help/uploads/default/original/3X/4/5/45e0a289b20298e505840493febd2d1a6189c404.jpeg)  
 ![Captureb13351](https://www.epiusers.help/uploads/default/original/3X/5/8/58b9d6ebb2c4d1385d4eec8f2acd69a387169d93.jpeg)  
 ![Capturec3273](https://www.epiusers.help/uploads/default/original/3X/1/1/11f82d7b87e125f84ddf2d31929e89d39819d181.jpeg)  
 ![Captured24528](https://www.epiusers.help/uploads/default/original/3X/8/1/8178688ef7954b9a97faa364d551a36cffc429b4.jpeg)

Thank you for your time!  
Nate

---

<div class="post-metadata">

**Author:** ![kananga](https://www.epiusers.help/user_avatar/www.epiusers.help/kananga/32/80812_2.png) [@kananga](https://www.epiusers.help/u/kananga)\
**Post date:** [April 5, 2024, 4:58pm UTC](https://www.epiusers.help/t/get-department-where-there-are-no-labor-detail-records/113466/2 "2024-04-05T16:58:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![NateS](https://www.epiusers.help/user_avatar/www.epiusers.help/nates/32/18519_2.png) [@NateS](https://www.epiusers.help/u/NateS)\
**Post date:** [April 5, 2024, 4:59pm UTC](https://www.epiusers.help/t/get-department-where-there-are-no-labor-detail-records/113466/3 "2024-04-05T16:59:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![kananga](https://www.epiusers.help/user_avatar/www.epiusers.help/kananga/32/80812_2.png) [@kananga](https://www.epiusers.help/u/kananga)\
**Post date:** [April 5, 2024, 5:11pm UTC](https://www.epiusers.help/t/get-department-where-there-are-no-labor-detail-records/113466/4 "2024-04-05T17:11:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![NateS](https://www.epiusers.help/user_avatar/www.epiusers.help/nates/32/18519_2.png) [@NateS](https://www.epiusers.help/u/NateS)\
**Post date:** [April 5, 2024, 6:24pm UTC](https://www.epiusers.help/t/get-department-where-there-are-no-labor-detail-records/113466/5 "2024-04-05T18:24:24Z")

</div>

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!
