title: Hours and Average Hours Worked per Day, by Department
body: <p>I'm trying to get an estimate of how many hours people worked during a set period of time. I want to show this by department and by what area they were working in. Right now I have this:</p>
  
  <pre><code>SELECT M.MemberDepartmentID,T.TaskName,
      COUNT(DATEDIFF(HOUR, TT.StartTime, TT.EndTime)) 'Hours',
      AVG(DATEDIFF(HOUR, TT.StartTime, TT.EndTime)) Average
  FROM Member.TaskTracking TT
      LEFT OUTER JOIN Member.Task T
          ON TT.TaskID=T.TaskID
      JOIN dbo.tblMember M
          ON TT.MemberID=M.MemberID
  WHERE M.FullTime=1
      AND M.EmployeeSalary=1
      AND (TT.StartTime &gt;= '2013-10-01'
      AND TT.EndTime &lt; '2013-11-01')
  GROUP BY M.MemberDepartmentID,T.TaskName
  ORDER BY M.MemberDepartmentID,T.TaskName
  </code></pre>
  
  <p>I don't know how to confirm if it's correct, but some are definitely showing averages of zero even if there were hours worked. And some averages are way higher than the hours worked. For instance, here are some of my results:</p>
  
  <pre><code>MemberDepartmentID  TaskName        Hours   Average
  ---------------------------------------------------
  1                   Packing         25      0
  1                   Picking         6       0
  1                   PreScanning     38      7
  4                   Picking         2       104
  </code></pre>
  
  <p>Suggestions?</p>