Count Total days worked

AccessNewb01

New member
Local time
Today, 00:42
Joined
Jul 13, 2017
Messages
4
All -

I have a query where I want to see how many days out of each month an employee has worked out of possible working days. My Table (Days_Worked) is as shown

TEAMID EMPLID LastName LoadDate WOStatus
73 1 Doe 01/01/2017 Completed
73 1 Doe 01/02/2017 FollowUP
73 1 Doe 01/05/2017 Completed
59 2 Jane 01/07/2017 InComplete
59 2 Jane 01/20/2017 Completed
59 2 Jane 02/05/2017 FollowUP


I need the query to count the days an employee had a WOStatus of Completed or FollowUP and than grouped by Month. If we can add all possible workdays to the query, that would be ideal as well. (Monday-Friday) only.

For Example, the above query should return grouped my employee ID.

TEAMID EMPLID LastName Month Days_Worked
73 1 Doe January 2
59 2 Jane February 2

Thank you for any help you're able to assist with. I've tried for hours!
 
The SQL Code is this so far. All the counting and grouping I've attempted to get total days worked as well as total available working days has been unsuccessful.

Code:
SELECT dbo_tblTeam.TeamID, dbo_tblEmployeePositions.EMPLID, dbo_tblEmployeePositions.LastName, dbo_tblEmployeePositions.FirstName, dbo_tblBillingProduction.LoadDate, dbo_tblBillingProduction.WOStatus
FROM dbo_tblBillingProduction INNER JOIN (dbo_tblTeam INNER JOIN dbo_tblEmployeePositions ON dbo_tblTeam.TeamID = dbo_tblEmployeePositions.TeamID) ON dbo_tblBillingProduction.EmployeeID = dbo_tblEmployeePositions.EMPLID
GROUP BY dbo_tblTeam.TeamID, dbo_tblEmployeePositions.EMPLID, dbo_tblEmployeePositions.LastName, dbo_tblEmployeePositions.FirstName, dbo_tblBillingProduction.LoadDate, dbo_tblBillingProduction.WOStatus
HAVING (((dbo_tblTeam.TeamID)=159 Or (dbo_tblTeam.TeamID)=83) AND ((dbo_tblBillingProduction.WOStatus)="FollowUP")) OR (((dbo_tblBillingProduction.WOStatus)="Completed"))
ORDER BY dbo_tblEmployeePositions.LastName;
 

Users who are viewing this thread

Back
Top Bottom