470,814 Members | 1,179 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,814 developers. It's quick & easy.

New to SQL and having issues creating a report to calc OT hours and dollars

4 Nibble
I am tasked with tracking Overtime hours worked by Department/Employee. Our work week is 35 hours, first 5 additional is Straight Time, any additional is OT (at time and a half). Tables collect Week of Date, Employee, # of OT Hours and Department. I initially attempted to use a running sum in the report and can't get it to work correctly. Now I've gone to SQL and trying to Total the # of OT Hours by Dept/Employee/Week in the Query, then do the calc for ST and then OT. I can't get the SUM of OTHours to add up the total # of hours by Dept/Employee/Week, it simply repeated the # of OT hours for each record. Here is what I have for SQL, partially based on information I found dating back to 2010 (which is giving me errors now). Not sure how to make this work and would appreciate any direction. I also attached the report I had from using Running Sums, just couldn't get the Sums to work if there was more than ONE employee in a Dept.

SELECT EmployeeData.EmployeeName, EmployeeData.[Pay Rate], EmployeeData.OT, EmployeeData.[Salary Type], EmployeeDataOT.OTProgramArea, EmployeeDataOT.WeekOfDate, EmployeeDataOT.DateEntry, EmployeeDataOT.OTHours, [SubMH].[EmployeeTotalOT], IIf(([SubMH].[EmployeeTotalOT]>=5),(5*[Pay Rate]),[SubMH].[EmployeeTotalOT][Pay Rate]) AS STDollars, IIf(([Salary Type]="Hourly") And ([SubMH].[EmployeeTotalOT]>=5),(([SubMH].[EmployeeTotalOT]-5)[OT]),Null) AS OTDollars
SELECT [OTProgramArea]
,Sum(EmployeeDataOT.OTHours) AS EmployeeTotalOT
FROM [EmployeeDataOT]
GROUP BY EmployeeDataOT.OTProgramArea, EmployeeData.EmployeeName, EmployeeDataOT.WeekOfDate
) AS [subMH]
ON EmployeeDataOT (EmployeeData.EmployeeName = EmployeeDataOT.EmployeeNameOT) AND (EmployeeData.DepartmentUnit = EmployeeDataOT.DepartmentLookup)
HAVING (((EmployeeDataOT.OTHours)>0) AND ((EmployeeDataOT.DateEntry) Between [Week of Start Date] And [Week of End Date]))
ORDER BY EmployeeDataOT.OTProgramArea, EmployeeDataOT.OTProgramArea, EmployeeData.EmployeeName, EmployeeDataOT.WeekOfDate, EmployeeDataOT.DateEntry DESC;
Jan 31 '21 #1
4 2642
4 Nibble
I think I cleaned up some of what I had cobbled together from that post I found in 2010. Still can't get the query to run. Initially I got: 'You have written a subquery that can return more than one field without using the 'EXISTS' reserved word in the main query's 'FROM' clause. Revise the 'SELECT' statement of the subquery to request only one field' So I moved the other 3 fields I'm grouping by in the subquery to the main query, leaving only the SUM field. Now I get the prompts to run the query, but get no results and a pop up that 'Only one record can be returned for this query'. Still not sure what I'm doing wrong... although I think it may be something in the two FROM clauses I'm using. My revised SQL is below... I would appreciate any feedback.

SELECT EmployeeDataOT.OTProgramArea, EmployeeData.EmployeeName, EmployeeDataOT.WeekOfDate, EmployeeDataOT.DateEntry, [Week Of Start Date] AS Expr1, [Week of End Date] AS Expr2, EmployeeData.[Pay Rate], EmployeeData.[Salary Type], EmployeeData.OT, EmployeeDataOT.OTHours, [EDOT].[EmployeeTotalOT], IIf(([EDOT].[EmployeeTotalOT]>=5),(5*[Pay Rate]),[EDOT].[EmployeeTotalOT]*[Pay Rate]) AS STDollars, IIf(([Salary Type]="Hourly") And ([EDOT].[EmployeeTotalOT]>=5),(([EDOT].[EmployeeTotalOT]-5)*[OT]),Null) AS OTDollars
FROM EmployeeData INNER JOIN EmployeeDataOT ON (EmployeeData.DepartmentUnit = EmployeeDataOT.DepartmentLookup) AND (EmployeeData.EmployeeName = EmployeeDataOT.EmployeeNameOT)
WHERE (((EmployeeDataOT.DateEntry) Between [Week Of Start Date] And [Week of End Date])) AND OTHours>0
ORDER BY EmployeeDataOT.DateEntry

(SELECT Sum([OTHours]) AS EmployeeTotalOT
FROM EmployeeData AS ED INNER JOIN [EmployeeDataOT] AS EDOT ON ED.EmployeeName = EDOT.EmployeeNameOT AND ED.DepartmentUnit = EDOT.DepartmentLookup
GROUP BY EDOT.OTProgramArea, ED.EmployeeName, EDOT.WeekOfDate);
Feb 3 '21 #2
4 Nibble
What I have is based off this post:

If the subquery can only return ONE record, not sure how the report in the other post works. It seems like I'm trying to generate the same type of report.

Still need some help...
Feb 3 '21 #3
4 Nibble
Solved it by not including the date entry and othours as fields in the report, I can do without the individual detail in this report, just really need the totals by week, employee, program and grand total. I was able to do the SUM of othours in the query, as well as the calcs for ST$ and OT$, all without delving into the sql or using any subquery.

Thanks for all the help!
Feb 6 '21 #4
357 Expert Mod 256MB
Sorry that nobody answered your conversation with yourself.
Glad to see you worked out the solution.
Feb 8 '21 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Greg | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.