CODEWITHSUNDEEP
sqlms-access
Steele1224
Steele1224

Reputation: 13

SQL Query calculates total time worked by user

I am trying to create a SQL query that can calculate total time Grouped BY USER.

For example: if John Smith does one job for 30 minutes and a different job for 45 minutes creating two records in my database. I need a result that would display 

USER         Time Worked Total     Date
John Smith           75            XX/XX/XXXX

What I have is this.

USER         Time Worked Total     Date
John Smith           45            XX/XX/XXXX
John Smith           30            XX/XX/XXXX

I can calculate the number of minutes per job done but not the total Query Code:

SELECT DISTINCT 
  Shipping_Table.Employee, 
  Shipping_Table.Date, 
  DateDiff("n",Shipping_Table.Start_Time,Shipping_Table.End_Time) AS MinutesWorked    
FROM Shipping_Table, Employees    
WHERE Shipping_Table.Date=[Forms]![Time Query]![DateCalc]

I am using a form so the USER can just pick a date and check to see how many hours his employees are working. The way the database table is setup the time entries are a Date/Time variable.

Upvotes: 1

Views: 320

Answers (1)

xQbert
xQbert

Reputation: 35333

You're close... just wrap your minutesWorked calculation in a Sum() function and add a group by, and with the group by, you don't need the distinct.

SELECT Shipping_Table.Employee, Shipping_Table.Date, 
      suM(DateDiff("n",Shipping_Table.Start_Time,Shipping_Table.End_Time)) AS MinutesWorked
    FROM Shipping_Table, Employees
    WHERE Shipping_Table.Date=[Forms]![Time Query]![DateCalc]
    GROUP BY Shipping_Table.Employee, Shipping_Table.Date

Note: your join is missing how shipping_table and employees relate which results in a Cartesian product which can lead to unexpected results.

you should really define how the tables relate to eachother (likely on employeeID and/or date)

Upvotes: 2

Related Questions