How do I find the amount of hours worked for each employee from this table?

Question

I am trying to figure out how to count the hours worked for each employee on each day but the numbers returned are way off. The part I need is taken from one table that is show in this picture. Table

It shows one employee's activity for a single day (there are many more records, I just chose to show one day). Most employees will take a lunch and that is why they have 2 punch ins and 2 punch outs.

Another table is joined with it but isn't important for now.

The InOut column shows whether they were punching in or out. In = 1 and Out = 0.

I have tried the code below. The datediff just doesn't seem to return the right number.

select sr_name, 
cast(punchintime as date) as workdate,
ROUND(sum(cast(datediff(minute,punchintime, punchouttime) as real)/60),2) as hoursworked
from
(
select sr_name,
punchintime = punchdatetime,
punchouttime = ISNULL((select top 1 pc2.punchdatetime 
from punchclock pc2 where pc2.punchdatetime > pc.punchdatetime and pc.servrepid = pc2.servrepid and pc2.inout = 0 order by pc2.punchdatetime), getdate())

from punchclock pc
join servicereps sr on pc.servrepid = sr.servrepid
where cast(pc.punchdatetime as date) >= '2019-01-01'
group by sr_name, punchdatetime, pc.ServRepID
) x

group by sr_name, cast(punchintime as date), cast(punchouttime - punchintime as time)

The results should be around 8 hours instead of the 20-30 hours this query returns.

Adam Yan Adam Yan · Accepted Answer · 2019-06-26T21:03:32

Your are always picking the last punchout time of the day with your query.

You should join TABLE punchclock (AS punchin where inout = 1) to itself (AS punchout on punchout.row_number = punchin.row_number + 1) with row_number added in a CTE, using

row_number() over (partition by repID, year, month, day order by punchdatetime asc)

Check this post for more details about row_number()

Then the remaining will be just

SUM(datediff(punchin.punchdatetime, punchout.punchdatetime))

How do I find the amount of hours worked for each employee from this table?

1 Answers