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