CODEWITHSUNDEEP
sqlsql-servert-sql
Lancelot
Lancelot

Reputation: 3

Summing Hours Worked Based On Two Unique Identifiers

I want to sum up the total hours worked in a given two week period (pay period) for employees in the company. I have a view that pulls a column for unique employee identifiers [CODE_USER], a column for uniquely identified pay types (Regular, Overtime, Holiday, Vacation, etc.) [Code], a column for total hours worked [Hours], and a column for each day of the workweek [Day].

As it stands right now, the [Hours] column shows total hours worked on a per day basis for each unique employee (based on the unique pay type, such as regular hours or overtime hours worked).

I need to combine all hours worked over a two week period for each employee [CODE_USER], for each pay type [CODE] into a summarized column named 'Hours'.

An ideal end result would look something like the following, given employee ID worked 80 regular hours, and 20 overtime hours over the course of two weeks (E1 equals Regular hours, E2 equals Overtime hours):

CODE_USER   Code    Hours   
  125       E1       80.00  
  125       E2       20.00

The closest I think I have gotten to solving it would be the following code, however it does not SUM hours worked for a unique CODE_USER for the two week period, it lists the hours worked for each day during the two week period as a collection of rows for that employee. For example, the following code shows 18 rows for the employee ID 125, the employee worked 10 full 8.00 hour days during the time period marked by E1 (regular), and there were 8 times where the employee worked overtime hours marked by E2 (overtime).

CODE:

SELECT [CODE_USER], 
   [Code],
   SUM(Hours) AS Hours,
   [Day]
FROM [LookUp].[dbo].[Daily_Hours_Worked]
WHERE [Day] >= '20191007' AND [Day] < '20191019'
AND [CODE_USER] LIKE '%125%'
GROUP BY [CODE_USER], [Code], [Hours], [Day]
ORDER BY [CODE_USER], [Day] DESC;

RESULTS:

CODE_USER   Code    Hours   Day
  125       E1       8.00   2019-10-18 00:00:00.000
  125       E2       0.70   2019-10-18 00:00:00.000
  125       E1       8.00   2019-10-17 00:00:00.000
  125       E2       1.65   2019-10-17 00:00:00.000
  125       E1       8.00   2019-10-16 00:00:00.000
  125       E2       1.15   2019-10-16 00:00:00.000
  125       E1       8.00   2019-10-15 00:00:00.000
  125       E2       0.97   2019-10-15 00:00:00.000
  125       E1       8.00   2019-10-14 00:00:00.000
  125       E2       1.99   2019-10-14 00:00:00.000
  125       E1       8.00   2019-10-11 00:00:00.000
  125       E2       0.12   2019-10-11 00:00:00.000
  125       E1       8.00   2019-10-10 00:00:00.000
  125       E2       0.05   2019-10-10 00:00:00.000
  125       E1       8.00   2019-10-09 00:00:00.000
  125       E2       0.10   2019-10-09 00:00:00.000
  125       E1       7.99   2019-10-08 00:00:00.000
  125       E1       7.99   2019-10-07 00:00:00.000

EXPECTED RESULTS:

I want to see a SUM of E1, E2, etc., for the input pay period (2 week period) for each unique Employee ID [CODE_USER] in the table. The end result should be two rows for each employee with Regular Time (E1) and Overtime (E2) that SUMs that employee's hours worked for each category over the given time period.

Upvotes: 0

Views: 142

Answers (1)

Caius Jard
Caius Jard

Reputation: 74660

Is it not simply that you should remove the day from the grouping and the specific employee from the where clause?

SELECT [CODE_USER], 
   [Code],
   SUM(Hours) AS Hours
FROM [LookUp].[dbo].[Daily_Hours_Worked]
WHERE [Day] >= '20191007' AND [Day] < '20191019'
GROUP BY [CODE_USER], [Code]
ORDER BY [CODE_USER]

You don't need to group by hours; you're summing it. Situations where you should group by a column that you're also aggregating are rare

I'm confused as to why you say two weeks but the dates in your where clause are not two weeks apart; what if someone works on a weekend? I've left this part, just wanted to raise it as it seems odd that you'd do 12 days ie include only every other weekend (if the job is run once a fortnight)

Upvotes: 2

Related Questions