Calculating man-hours per hour

• Simon Liddle

  SSCrazy

  Points: 2580

  More actions

  January 8, 2009 at 2:06 am

  #72323

  Hi all,

  I need to produce a report which shows the number of man-hours (in minutes) worked for each hour of the day. In other words, if two people have worked between 9am and 10am, then in my report, the hour 9 would show 120 minutes. If one person worked from 11am to 11.35am, then the hour 11 would show 35 minutes. I have some working code, but wondered if there are better alternatives - it took a lot of head-scratching for me to get to this point! 😛

  I have a StaffTimeRecord table which shows the time the worker clocked in and clocked out as well as other information, but for the purpose of this post, I have simplified it to the 2 columns I am interested in. My tally table is called Tally, with one column called n - I haven't created that in the following code snippet.

  CREATE TABLE #StaffTimeRecord

  (StartTime DATETIME,

  EndTime DATETIME)

  GO

  INSERT INTO #StaffTimeRecord

  SELECT '2009-01-02 07:42', '2009-01-02 15:49'

  UNION ALL

  SELECT '2009-01-02 03:12', '2009-01-02 19:20'

  GO

  SELECT TOP 24 t.n,

  SUM(

  DATEDIFF(mi,

  CASE

  WHEN s.StartTime > (DATEADD(mi, n * 60,DATEADD(dd, DATEDIFF(dd, 0, StartTime), 0)))

  THEN

  s.StartTime

  ELSE

  DATEADD(mi, n * 60,DATEADD(dd, DATEDIFF(dd, 0, StartTime), 0))

  END

  ,

  CASE

  WHEN s.EndTime < (DATEADD(mi, (n + 1) * 60,DATEADD(dd, DATEDIFF(dd, 0, StartTime), 0)))

  THEN

  s.EndTime

  ELSE

  DATEADD(mi, (n + 1) * 60,DATEADD(dd, DATEDIFF(dd, 0, StartTime), 0))

  END

  )

  ) TotalMinutes

  FROM

  tally t

  INNER JOIN

  #StaffTimeRecord s

  ON

  n >= DATEPART(hh, s.StartTime)

  AND

  n <= DATEPART(hh, s.EndTime)

  GROUP BY n

  GO

  DROP TABLE #StaffTimeRecord

  I hope this makes sense, and would be interested in seeing alternative ways of handling this scenario.

  Simon

• Johan Bijnens

  SSC Guru

  Points: 134873

  More actions

  January 8, 2009 at 5:30 am

  #923215

  did you try this ?

  CREATE TABLE #StaffTimeRecord

  (

  StartTime DATETIME

  , EndTime DATETIME

  )

  GO

  set nocount on

  INSERT INTO #StaffTimeRecord

  SELECT '2009-01-02 07:42'

  , '2009-01-02 15:49'

  UNION ALL

  SELECT '2009-01-02 03:12'

  , '2009-01-02 19:20'

  UNION ALL

  SELECT '2009-01-02 00:02'

  , '2009-01-02 01:20'

  UNION ALL

  SELECT '2009-01-02 22:02'

  , '2009-01-02 22:20'

  GO

  Select *

  from #StaffTimeRecord

  order by starttime, endtime

  -- use t.n - 1 unless your tally table starts with 0 (zero)

  SELECT TOP 24

  t.n - 1 as [Hour]

  , isnull(SUM(case when DATEPART(hh, s.StartTime) = t.n -1

  then ((t.n - 1) * 60) - DATEDIFF(mi,DATEADD(dd, DATEDIFF(dd, 0, StartTime), 0), s.StartTime)

  else 0

  end

  + case when DATEPART(hh, s.EndTime) = t.n -1

  then DATEPART(mi, s.EndTime)

  when DATEPART(hh, s.EndTime) > t.n -1

  then 60

  else 0

  end

  ),0) TotalMinutes

  , count(*) as NEmployees

  FROM tally t

  left JOIN #StaffTimeRecord s

  ON n >= DATEPART(hh, s.StartTime) + 1

  AND n <= DATEPART(hh, s.EndTime) + 1

  GROUP BY n

  GO

  DROP TABLE #StaffTimeRecord

  go

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Ken McKelvey

  SSCoach

  Points: 18484

  More actions

  January 8, 2009 at 9:46 am

  #923471

  Or you could have the hour as a datetime to cope with multiple days. Something like:

  SELECT D.HourStart

  &nbsp&nbsp&nbsp&nbsp,SUM

  &nbsp&nbsp&nbsp&nbsp(

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspCASE

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN D.HourStart >= S.StartTime AND D.HourEnd < S.EndTime

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspTHEN 60

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN D.HourStart &lt S.StartTime AND D.HourEnd > S.EndTime

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspTHEN 60 - DATEDIFF(mi, D.HourStart, S.StartTime) - DATEDIFF(mi, S.EndTime, D.HourEnd)

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN D.HourStart < S.StartTime

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspTHEN 60 - DATEDIFF(mi, D.HourStart, S.StartTime)

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspELSE 60 - DATEDIFF(mi, S.EndTime, D.HourEnd)

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspEND

  &nbsp&nbsp&nbsp&nbsp) AS TotalMinutes

  FROM

  (

  &nbsp&nbsp&nbsp&nbspSELECT DATEADD(hour, T.N -1, D1.StaffDate)

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,DATEADD(hour, T.N, D1.StaffDate)

  &nbsp&nbsp&nbsp&nbspFROM Tally T

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspCROSS JOIN

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp(

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT DATEADD(day, DATEDIFF(day, 0, StartTime), 0)

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM #StaffTimeRecord

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspUNION

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT DATEADD(day, DATEDIFF(day, 0, EndTime), 0)

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM #StaffTimeRecord

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp) D1 (StaffDate)

  &nbsp&nbsp&nbsp&nbspWHERE T.N < 25

  ) D (HourStart, HourEnd)

  &nbsp&nbsp&nbsp&nbspJOIN #StaffTimeRecord S

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspON D.HourEnd > S.StartTime

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspAND D.HourStart <= S.EndTime

  GROUP BY D.HourStart

  ORDER BY HourStart