BAQ Sum by Day of Week

hmwillett · October 13, 2017, 7:55pm

I have this BAQ I’m trying to write that has odd behavior for users that have more than one clock in entry per day.
It is doubling their hours. The subquery that’s pulling the hours per weekday is fine, but when it’s summarized at the top level, it doubles them. I have the top query set as distinct and it only shows one record if ran alone.

Hours without the formula summarized; take Monday, for example–it’s showing 11.06 for that day, which correctly shows the sum of the two entries the user logged for the day (6.38 and 4.68). All of the rows, but one are 0.00, so the sum should be 11.06.

Non summarized formula at the top level (grouped)

Now when I summarize those calculated fields at the top level, suddenly I’m getting duplication.

Here’s the SQL view of the query:

select distinct
    (convert(int,LaborHed.EmployeeNum)) as [Calculated_EmpID],
    [EmpBasic].[LastName] as [EmpBasic_LastName],
    [EmpBasic].[FirstName] as [EmpBasic_FirstName],
    [EmpBasic].[JCDept] as [EmpBasic_JCDept],
    [JCDept].[Description] as [JCDept_Description],
    (SUM(Days1.Calculated_Sunday)) as [Calculated_SumSunday],
    (SUM(Days1.Calculated_Monday)) as [Calculated_SumMonday],
    (SUM(Days1.Calculated_Tuesday)) as [Calculated_SumTuesday],
    (SUM(Days1.Calculated_Wednesday)) as [Calculated_SumWednesday],
    (SUM(Days1.Calculated_Thursday)) as [Calculated_SumThursday],
    (SUM(Days1.Calculated_Friday)) as [Calculated_SumFriday],
    (SUM(Days1.Calculated_Saturday)) as [Calculated_SumSaturday],
    (SumSunday+SumMonday+SumTuesday+SumWednesday+SumThursday+SumFriday+SumSaturday) as 
    [Calculated_Weekly]

from Erp.LaborHed as LaborHed
inner join Erp.EmpBasic as EmpBasic on 
    LaborHed.Company = EmpBasic.Company
And
    LaborHed.EmployeeNum = EmpBasic.EmpID
    inner join Erp.JCDept as JCDept on 
    EmpBasic.Company = JCDept.Company
And
    EmpBasic.JCDept = JCDept.JCDept

inner join  (select distinct
	[Days].[EmployeeNum] as [Days_EmployeeNum],
	[Days].[PayrollDate] as [Days_PayrollDate],
	(SUM((case when datepart(weekday,Days.PayrollDate)=1 then (Days.PayHours) else 0 end))) as [Calculated_Sunday],
	(SUM((case when  datepart(weekday,Days.PayrollDate)=2 then (Days.PayHours) else 0 end))) as [Calculated_Monday],
	(SUM(case when  datepart(weekday,Days.PayrollDate)=3 then (Days.PayHours) else 0 end)) as [Calculated_Tuesday],
	(SUM(case when  datepart(weekday,Days.PayrollDate)=4 then (Days.PayHours) else 0 end)) as [Calculated_Wednesday],
	(SUM(case when  datepart(weekday,Days.PayrollDate)=5 then (Days.PayHours) else 0 end)) as [Calculated_Thursday],
	(SUM(case when  datepart(weekday,Days.PayrollDate)=6 then (Days.PayHours) else 0 end)) as [Calculated_Friday],
	(SUM(case when  datepart(weekday,Days.PayrollDate)=7 then (Days.PayHours) else 0 end)) as [Calculated_Saturday]
from Erp.LaborHed as Days
group by [Days].[EmployeeNum],
	[Days].[PayrollDate])  as Days1 on 
	LaborHed.EmployeeNum = Days1.Days_EmployeeNum
And
	LaborHed.PayrollDate = Days1.Days_PayrollDate
 where (LaborHed.EmployeeNum = '1657')
 and Days1.Days_PayrollDate >= '10/1/2017'  and Days1.Days_PayrollDate <= '10/7/2017'

group by (convert(int,LaborHed.EmployeeNum)),
	[EmpBasic].[LastName],
	[EmpBasic].[FirstName],
	[EmpBasic].[JCDept],
	[JCDept].[Description]

kjarman84 · October 13, 2017, 8:49pm

I had a similar issue when attempting to calculate out monthly shipments by part number. I think the issue is needing an extra group by clause at one of the mid level summaries. I’m trying to compare apples to apples on my query, but mine is much more of a mess than yours

Edit: now that I’m looking a bit closer, you don’t have any subqueries. What you’ll want to do is make that an innersubquery and do another summary on each of the fields, grouped by EmpID, Name, Department, Description, and also the Weekly field.

tkoch · October 13, 2017, 9:09pm

Try this. I set a pivot on my subquery in the top level query.

select 
[EmpBasic].[EmpID] as [EmpBasic_EmpID],
[EmpBasic].[Name] as [EmpBasic_Name],
[SubQuery2].[Mon] as [SubQuery2_Mon],
[SubQuery2].[Tue] as [SubQuery2_Tue],
[SubQuery2].[Wed] as [SubQuery2_Wed],
[SubQuery2].[Thu] as [SubQuery2_Thu],
[SubQuery2].[Fri] as [SubQuery2_Fri],
[SubQuery2].[Sat] as [SubQuery2_Sat],
[SubQuery2].[Sun] as [SubQuery2_Sun]

from Erp.EmpBasic as EmpBasic
inner join (select
[LaborHed1].[Company] as [LaborHed1_Company],
[LaborHed1].[EmployeeNum] as [LaborHed1_EmployeeNum],
(case
when datepart(weekday, LaborHed1.ClockInDate) = 1 then ‘Sun’
when datepart(weekday, LaborHed1.ClockInDate) = 2 then ‘Mon’
when datepart(weekday, LaborHed1.ClockInDate) = 3 then ‘Tue’
when datepart(weekday, LaborHed1.ClockInDate) = 4 then ‘Wed’
when datepart(weekday, LaborHed1.ClockInDate) = 5 then ‘Thu’
when datepart(weekday, LaborHed1.ClockInDate) = 6 then ‘Fri’
when datepart(weekday, LaborHed1.ClockInDate) = 7 then ‘Sat’
end) as [Calculated_Day],
(sum(LaborHed1.PayHours)) as [Calculated_Hours]
from Erp.LaborHed as LaborHed1
where (LaborHed1.ClockInDate >= ‘10/02/2017’ and LaborHed1.ClockInDate <= ‘10/08/2017’)
group by [LaborHed1].[Company],
[LaborHed1].[EmployeeNum],
(case
when datepart(weekday, LaborHed1.ClockInDate) = 1 then ‘Sun’
when datepart(weekday, LaborHed1.ClockInDate) = 2 then ‘Mon’
when datepart(weekday, LaborHed1.ClockInDate) = 3 then ‘Tue’
when datepart(weekday, LaborHed1.ClockInDate) = 4 then ‘Wed’
when datepart(weekday, LaborHed1.ClockInDate) = 5 then ‘Thu’
when datepart(weekday, LaborHed1.ClockInDate) = 6 then ‘Fri’
when datepart(weekday, LaborHed1.ClockInDate) = 7 then ‘Sat’
end)) SubQuery2_src
PIVOT
(
sum(Calculated_Hours)
FOR Calculated_Day in ([Mon],[ Tue],[ Wed],[ Thu],[ Fri],[ Sat],[ Sun]) )
as SubQuery2 on
SubQuery2.LaborHed1_Company = EmpBasic.Company
And
SubQuery2.LaborHed1_EmployeeNum = EmpBasic.EmpID

hmwillett · October 16, 2017, 12:25pm

Thanks @tkoch, that looks like it works.

hmwillett · October 16, 2017, 12:36pm

@tkoch, is it possible to sum those pivot values to get a weekly total?
When I tried, it came up blank.

tkoch · October 16, 2017, 12:55pm

You want each day as well as the weekly total?

Sum each of the day columns together in the top level query.

hmwillett · October 16, 2017, 12:58pm

I tried that, but the “Weekly” column shows blank when testing.

Except for one person, who apparently has all of the hours, lol.

tkoch · October 16, 2017, 1:39pm

Have to check for null’s from the subquery.

select 
[EmpBasic].[EmpID] as [EmpBasic_EmpID],
[EmpBasic].[Name] as [EmpBasic_Name],
(case  
 when SubQuery2.Sun IS NULL then  0.00
 else SubQuery2.Sun

end) as [Calculated_Sun],
(case
when SubQuery2.Mon IS NULL then 0.00
else SubQuery2.Mon
end) as [Calculated_Mon],
(case
when SubQuery2.Tue IS NULL then 0.00
else SubQuery2.Tue
end) as [Calculated_Tue],
(case
when SubQuery2.Wed IS NULL then 0.00
else SubQuery2.Wed
end) as [Calculated_Wed],
(case
when SubQuery2.Thu IS NULL then 0.00
else SubQuery2.Thu
end) as [Calculated_Thu],
(case
when SubQuery2.Fri IS NULL then 0.00
else SubQuery2.Fri
end) as [Calculated_Fri],
(case
when SubQuery2.Sat IS NULL then 0.00
else SubQuery2.Sat
end) as [Calculated_Sat],
(Sun+Mon+Tue+Wed+Thu+Fri+Sat) as [Calculated_Total]
from Erp.EmpBasic as EmpBasic
inner join (select
[LaborHed].[Company] as [LaborHed_Company],
[LaborHed].[EmployeeNum] as [LaborHed_EmployeeNum],
(case
when datepart(weekday,LaborHed.ClockInDate) = 1 then ‘Sun’
when datepart(weekday,LaborHed.ClockInDate) = 2 then ‘Mon’
when datepart(weekday,LaborHed.ClockInDate) = 3 then ‘Tue’
when datepart(weekday,LaborHed.ClockInDate) = 4 then ‘Wed’
when datepart(weekday,LaborHed.ClockInDate) = 5 then ‘Thu’
when datepart(weekday,LaborHed.ClockInDate) = 6 then ‘Fri’
when datepart(weekday,LaborHed.ClockInDate) = 7 then ‘Sat’
end) as [Calculated_Day],
(sum(LaborHed.PayHours)) as [Calculated_Hours]
from Erp.LaborHed as LaborHed
where (LaborHed.ClockInDate >= ‘10/09/2017’ and LaborHed.ClockInDate <= ‘10/15/2017’)
group by [LaborHed].[Company],
[LaborHed].[EmployeeNum],
(case
when datepart(weekday,LaborHed.ClockInDate) = 1 then ‘Sun’
when datepart(weekday,LaborHed.ClockInDate) = 2 then ‘Mon’
when datepart(weekday,LaborHed.ClockInDate) = 3 then ‘Tue’
when datepart(weekday,LaborHed.ClockInDate) = 4 then ‘Wed’
when datepart(weekday,LaborHed.ClockInDate) = 5 then ‘Thu’
when datepart(weekday,LaborHed.ClockInDate) = 6 then ‘Fri’
when datepart(weekday,LaborHed.ClockInDate) = 7 then ‘Sat’
end)) SubQuery2_src
PIVOT
(
sum(Calculated_Hours)
FOR Calculated_Day in ([Sun],[ Mon],[ Tue],[ Wed],[ Thu],[ Fri],[ Sat]) )
as SubQuery2 on
SubQuery2.LaborHed_Company = EmpBasic.Company
And
SubQuery2.LaborHed_EmployeeNum = EmpBasic.EmpID

hmwillett · October 16, 2017, 1:50pm

That doesn’t appear to be summarizing the pivot.

 select distinct
	(convert(int,LaborHed.EmployeeNum)) as [Calculated_EmpID],
	[EmpBasic].[LastName] as [EmpBasic_LastName],
	[EmpBasic].[FirstName] as [EmpBasic_FirstName],
	[EmpBasic].[JCDept] as [EmpBasic_JCDept],
	[JCDept].[Description] as [JCDept_Description],
	((case when Days1.Sun IS NULL then 0.00 else Days1.Sun end)) as [Calculated_Sun],
	((case when Days1.Mon IS NULL then 0.00 else Days1.Mon end)) as [Calculated_Mon],
	((case when Days1.Tue IS NULL then 0.00 else Days1.Tue end)) as [Calculated_Tue],
	((case when Days1.Wed IS NULL then 0.00 else Days1.Wed end)) as [Calculated_Wed],
	((case when Days1.Thu IS NULL then 0.00 else Days1.Thu end)) as [Calculated_Thu],
	((case when Days1.Fri IS NULL then 0.00 else Days1.Fri end)) as [Calculated_Fri],
	((case when Days1.Sat IS NULL then 0.00 else Days1.Sat end)) as [Calculated_Sat],
	(SUM(Sun+Mon+Tue+Wed+Thu+Fri+Sat)) as [Calculated_Weekly]
from Erp.LaborHed as LaborHed
inner join Erp.EmpBasic as EmpBasic on 
	LaborHed.Company = EmpBasic.Company
And
	LaborHed.EmployeeNum = EmpBasic.EmpID

inner join Erp.JCDept as JCDept on 
	EmpBasic.Company = JCDept.Company
And
	EmpBasic.JCDept = JCDept.JCDept

inner join  (select distinct
	[Days].[Company] as [Days_Company],
	[Days].[EmployeeNum] as [Days_EmployeeNum],
	(case  
 when  datepart(weekday,Days.PayrollDate)=1 then 'Sun'
 when  datepart(weekday,Days.PayrollDate)=2 then 'Mon'
 		when  datepart(weekday,Days.PayrollDate)=3 then 'Tue'
 		when  datepart(weekday,Days.PayrollDate)=4 then 'Wed'
 		when  datepart(weekday,Days.PayrollDate)=5 then 'Thu'
 		when  datepart(weekday,Days.PayrollDate)=6 then 'Fri'
 		when  datepart(weekday,Days.PayrollDate)=7 then 'Sat'
 end) as [Calculated_Day],
	(SUM(Days.PayHours)) as [Calculated_Hours]
from Erp.LaborHed as Days
 where (Days.PayrollDate >= '10/1/2017'  and Days.PayrollDate <= '10/7/2017')
group by [Days].[Company],
	[Days].[EmployeeNum],
	(case  
 when  datepart(weekday,Days.PayrollDate)=1 then 'Sun'
 when  datepart(weekday,Days.PayrollDate)=2 then 'Mon'
 		when  datepart(weekday,Days.PayrollDate)=3 then 'Tue'
 		when  datepart(weekday,Days.PayrollDate)=4 then 'Wed'
 		when  datepart(weekday,Days.PayrollDate)=5 then 'Thu'
 		when  datepart(weekday,Days.PayrollDate)=6 then 'Fri'
 		when  datepart(weekday,Days.PayrollDate)=7 then 'Sat'
 end))  Days1_src 
PIVOT
(
SUM(Calculated_Hours)
FOR  Calculated_Day in ([Sun],[ Mon],[ Tue],[ Wed],[ Thu],[ Fri],[ Sat])   )
 as Days1 on 
	LaborHed.EmployeeNum = Days1.Days_EmployeeNum
And
	LaborHed.Company = Days1.Days_Company

group by (convert(int,LaborHed.EmployeeNum)),
	[EmpBasic].[LastName],
	[EmpBasic].[FirstName],
	[EmpBasic].[JCDept],
	[JCDept].[Description],
	((case when Days1.Sun IS NULL then 0.00 else Days1.Sun end)),
	((case when Days1.Mon IS NULL then 0.00 else Days1.Mon end)),
	((case when Days1.Tue IS NULL then 0.00 else Days1.Tue end)),
	((case when Days1.Wed IS NULL then 0.00 else Days1.Wed end)),
	((case when Days1.Thu IS NULL then 0.00 else Days1.Thu end)),
	((case when Days1.Fri IS NULL then 0.00 else Days1.Fri end)),
	((case when Days1.Sat IS NULL then 0.00 else Days1.Sat end))

hmwillett · October 16, 2017, 1:54pm

Nevermind, that was my bad.
I was summarizing the summary, lol.
It looks great. Thanks again @tkoch!

A.Baeisa · November 19, 2021, 1:43pm

is it possible to upload the BAQ here please ?

hmwillett · November 19, 2021, 1:45pm

@A.Baeisa I left that company. @Randy can you?

Randy · November 19, 2021, 2:14pm

Ah that monster BAQ.

HRPayrollExportPivot.baq (56.5 KB)

A.Baeisa · November 19, 2021, 2:25pm

many thanks both of you @Randy and @hmwillett

Topic		Replies	Views
Need Help Creating BAQ to List Pay Hours vs Labor Hours Epicor ERP 10 e10 , baq	8	4005	September 19, 2017
BAQ with PIVOT using conditional Aggregate Epicor ERP 10	16	8017	May 14, 2018
BAQ Sum JobOper Act Hors Epicor ERP 10 baq	5	528	November 2, 2022
Trying to create a BAQ with date parameters (filtering) Kinetic ERP	3	788	September 8, 2023
Weekly to Bi-Weekly Payroll BAQ Issue Epicor ERP 10 baq , payroll , bi-weekly	4	726	January 20, 2020

BAQ Sum by Day of Week

Related Topics