Trying to create a BAQ with date parameters (filtering)

Kinetic ERP
1

I’m trying to create a BAQ (to be eventually used as the basis for a dashboard) for viewing (what we call) productivity. I have a subquery using LaborHed that gets the total payroll hours. I have another subquery using LaborDtl that gets indirect, direct, and earned hours. But if I want to set a date range, I need to manually set the range on each of the 2 subqueries. I’ve remade the BAQ a few different ways, but have not yet figured out how to get the date parameter(s) to the top level.

Here is the sql:

select 
	[EmpBasic1].[Company] as [EmpBasic1_Company],
	[EmpBasic1].[EmpID] as [EmpBasic1_EmpID],
	[EmpBasic1].[Name] as [EmpBasic1_Name],
	[Header].[Calculated_TotPayHrs] as [Calculated_TotPayHrs],
	[Detail].[Calculated_IndirectSum] as [Calculated_IndirectSum],
	[Detail].[Calculated_DirectSum] as [Calculated_DirectSum],
	[Detail].[Calculated_TotEarned] as [Calculated_TotEarned],
	(CASE WHEN Header.Calculated_TotPayHrs = 0 then 0 ELSE Detail.Calculated_DirectSum / Header.Calculated_TotPayHrs end) as [Calculated_DirectProd],
	(CASE WHEN Header.Calculated_TotPayHrs = 0 then 0 ELSE Detail.Calculated_TotEarned / Header.Calculated_TotPayHrs end) as [Calculated_EarnedProd]
from Erp.EmpBasic as EmpBasic1
inner join  (select 
	[EmpBasic].[Company] as [EmpBasic_Company],
	[EmpBasic].[EmpID] as [EmpBasic_EmpID],
	[EmpBasic].[Name] as [EmpBasic_Name],
	(sum(case when LaborDtl.ExpenseCode = 'Indirect' then LaborDtl.LaborHrs else 0 end)) as [Calculated_IndirectSum],
	(sum(case when LaborDtl.ExpenseCode = 'Direct' then LaborDtl.LaborHrs else 0 end)) as [Calculated_DirectSum],
	(sum(LaborDtl.EarnedHrs)) as [Calculated_TotEarned]
from Erp.EmpBasic as EmpBasic
inner join Erp.LaborDtl as LaborDtl on 
	LaborDtl.Company = EmpBasic.Company
	and LaborDtl.EmployeeNum = EmpBasic.EmpID
group by [EmpBasic].[Company],
	[EmpBasic].[EmpID],
	[EmpBasic].[Name])  as Detail on 
	EmpBasic1.Company = Detail.EmpBasic_Company
	and EmpBasic1.EmpID = Detail.EmpBasic_EmpID
inner join  (select 
	[EmpBasic2].[Company] as [EmpBasic2_Company],
	[EmpBasic2].[EmpID] as [EmpBasic2_EmpID],
	[EmpBasic2].[Name] as [EmpBasic2_Name],
	(sum(LaborHed.PayHours)) as [Calculated_TotPayHrs]
from Erp.EmpBasic as EmpBasic2
inner join Erp.LaborHed as LaborHed on 
	EmpBasic2.Company = LaborHed.Company
	and EmpBasic2.EmpID = LaborHed.EmployeeNum
group by [EmpBasic2].[Company],
	[EmpBasic2].[EmpID],
	[EmpBasic2].[Name])  as Header on 
	EmpBasic1.Company = Header.EmpBasic2_Company
	and EmpBasic1.EmpID = Header.EmpBasic2_EmpID

I’m not sure how to rebuild this, because this is the only way that I get any decent results.

2 

select 
	[LaborHed1].[Company] as [LaborHed1_Company],
	[LaborHed1].[EmployeeNum] as [LaborHed1_EmployeeNum],
	[LaborHed1].[PayrollDate] as [LaborHed1_PayrollDate],
	[TotalPayrollHours].[Calculated_TotPayroll] as [Calculated_TotPayroll],
	[TotalLaborHours].[Calculated_TotLabor] as [Calculated_TotLabor],
	[TotalLaborHours].[Calculated_TotBurden] as [Calculated_TotBurden],
	[TotalLaborHours].[Calculated_TotEarned] as [Calculated_TotEarned]
from Erp.LaborHed as LaborHed1
inner join  (select 
	[LaborHed].[Company] as [LaborHed_Company],
	[LaborHed].[EmployeeNum] as [LaborHed_EmployeeNum],
	[LaborHed].[PayrollDate] as [LaborHed_PayrollDate],
	(sum(LaborHed.PayHours)) as [Calculated_TotPayroll]
from Erp.LaborHed as LaborHed
group by [LaborHed].[Company],
	[LaborHed].[EmployeeNum],
	[LaborHed].[PayrollDate])  as TotalPayrollHours on 
	LaborHed1.Company = TotalPayrollHours.LaborHed_Company
	and LaborHed1.EmployeeNum = TotalPayrollHours.LaborHed_EmployeeNum
	and LaborHed1.PayrollDate = TotalPayrollHours.LaborHed_PayrollDate
inner join  (select 
	[LaborDtl].[Company] as [LaborDtl_Company],
	[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
	[LaborDtl].[PayrollDate] as [LaborDtl_PayrollDate],
	(sum(LaborDtl.LaborHrs)) as [Calculated_TotLabor],
	(sum(LaborDtl.BurdenHrs)) as [Calculated_TotBurden],
	(sum(LaborDtl.EarnedHrs)) as [Calculated_TotEarned]
from Erp.LaborDtl as LaborDtl
group by [LaborDtl].[Company],
	[LaborDtl].[EmployeeNum],
	[LaborDtl].[PayrollDate])  as TotalLaborHours on 
	LaborHed1.Company = TotalLaborHours.LaborDtl_Company
	and LaborHed1.EmployeeNum = TotalLaborHours.LaborDtl_EmployeeNum
	and LaborHed1.PayrollDate = TotalLaborHours.LaborDtl_PayrollDate
where (LaborHed1.PayrollDate >= @StartDate  and LaborHed1.PayrollDate <= @EndDate)

This should do the trick. Here I made the two subqueries including the payroll date. Then I joined them to the top level on the labor hed and just filtered the labor hed for the date.
testtest.baq (37.9 KB)

3

Thank You. I’ll need to study this. This is closer than what I got. Ultimately, I’d like to get each employee’s totals on one line, rather than having one line per day. (if needed, I could use the publish/subscribe info to display each employee’s individual day’s info in a second window.)
Thank You again.

4

In that case, just remove the payroll date from the subs and top level queries. Then your grouping is just on the employee, and the date range that you choose.