locked
Calculate total man hours spend on each task RRS feed

  • Question

  • User-257070954 posted

    Hi all

    I had two tables, One is employees attendance table and another one is employees task tables

    Attendance table containing columns like EmpId, workeddate, ActualHours worked in a day etc
    Task Table containing columns like Empid, Workeddate, taskId etc

    From these how i get total hrs spend by all employees in each task

    Consider if one emp work on one task today probably he will work on another task tomorrow

    Below I giving my table structure

    Attendance table
     
    EmpId  workedDate ,   ActualHrsWorked
     
     1001    09-10-2108       10
     1001    10-10-2018       09
    1001     11-10-2018       8
    1002     09 -10-2018      11
    1002     10-10-2018       06
    1003     11 -10-2108      12
     
     
    Task Table 
     
    EmpId workedDate      TaskId
     
    1001    09-10-2108      101
    1001    10-10-2018      102
    1001    11-10-2018      101
    1002    09 -10-2018     101
    1002    10-10-2018      101
    1003    11 -10-2108     102
    Expecting answer
    taskId ActualHrsWorked
    101 35
    102 21
    Monday, October 22, 2018 5:24 AM

All replies

  • User-595703101 posted

    Please try following SQL Select with SUM() aggregation function and Group By clause

    select
    	t.taskId,
    	sum(a.ActualHrsWorked) as ActualHrsWorked
    from employees_task as t
    inner join employees_attendance as a
    	on t.Empid = a.Empid
    	and t.Workeddate = a.Workeddate
    group by t.taskId



    Monday, October 22, 2018 12:58 PM
  • User3690988 posted

    How about this:

    With tblTaskHours(taskID, workedDate, taskHours)
    as
    (SELECT t.taskId, t.Workeddate, sum(a.ActualHours) AS TotalTaskHours
    FROM Attendance a
    JOIN Task t ON t.Empid = a.EmpId and t.Workeddate = a.workeddate
    GROUP BY t.taskId, t.workeddate with rollup)
    
    SELECT taskID, taskHours
    FROM tblTaskHours
    WHERE taskID IS NOT NULL
      AND workedDate IS NULL

    Monday, October 22, 2018 1:20 PM