SQL procedure to show how many hours has worker worked

0
votes
+-----------+-------------------------------+-------+
| Worker ID | Time(MM/DD/YYYY Hour:Min:Sec) | InOut |
+-----------+-------------------------------+-------+
|        1  |  12/04/2017 10:00:00          | In    |
|        2  |  12/04/2017 10:00:00          | In    |
|        2  |  12/04/2017 18:40:02          | Out   |
|        3  |  12/04/2017 10:00:00          | In    |
|        1  |  12/04/2017 12:01:00          | Out   |
|        3  |  12/04/2017 19:40:05          | Out   |
+-----------+-------------------------------+-------+

Hi! I have problem with my project and I thought some of you would help me. I have table like that. It is simple table that indicates worker getting in and out of company. I need to do procedure which would take ID and number of day as In parameters and it would show how many hours and minutes that worker has worked that day. Thanks for help.

3
sqloracleplsql
You probably want to split Time into two columns, Date, and Time. Then GROUP BY Worker ID and Date, then calculate (SUM(Time) WHERE InOut = IN) - (SUM(Time) WHERE InOut = "Out") - Mako212
@Mako - noooooo. Please don't ever do that. If I'm having to do date operations with SQL data, I definitely don't want to have to assemble two separate components together before performing the operation. If the date is a single column, I can just call DATEDIFF; if the date is two columns, I have to call DATEDIFF twice, and then add the values together. Heck, there's isn't even a good way of telling whether one row is earlier/later than another row if date and time are split into separate columns. - Kevin

3 Answers

0
votes

Yeah, I had to do a number of queries like this at my old job. Here's the approach I used, and it worked out pretty well:

For each "Out" record, get the MAX(TIME) on "In" records with a time earlier than the OUT record

Does that make sense? You're basically joining the table against itself, looking for the record that represents the "clock in" time for any particular "clock out" time.

So here's the backbone:

select
*
, (
        SELECT MAX(tim) from @tempTable subQ
        where subQ.id = main.id
        and subQ.tim <= main.tim
        and subQ.InOut = 'In'
  ) as correspondingInTime
from @tempTable main
where InOut = 'Out'

... from here, you can get the data you need. Either by manipulating the query above, or using it as a subquery itself (which is my favored way of doing it) - something like:

select id as workerID, sum(DATEDIFF(s, correspondingInTime, tim)) as totalSecondsWorked
from
(
    select
    *
    , (
            SELECT MAX(tim) from @tempTable subQ
            where subQ.id = main.id
            and subQ.tim <= main.tim
            and subQ.InOut = 'In'
      ) correspondingInTime
    from @tempTable main
    where InOut = 'Out'
) mainQuery
group by id

EDIT: Remove the 'as' before correspondingInTime, because oracle doesn't allow 'as' in table aliasing.

0
votes

Maybe something similar to 

select sum( time1 - prev_time1 ) from ( 
    select InOut, time1, 
  prev(time1) over (partition by worker_id order by time1) prev_time1, 
  prev(InOut) over (partition by worker_id order by time1) prev_inOut
from MyTABLE
where TimeColumn between trunc(:date1) and trunc( :date1 + 1 )
and workerId = :workerId
) t1
where InOut = 'Out' and prev_InOut = 'In'

would go. :workerId and :date1 are variables to constrain to one date and one worker as required.

0
votes

I'm fairly certain Oracle allows you to use CROSS APPLY these days.

SELECT [Worker ID], yt.Time - ca.Time
FROM YourTable yt
CROSS APPLY (SELECT MAX(Time) AS Time
             FROM YourTable
             WHERE [Worker ID] = yt.[Worker ID] AND Time < yt.Time AND InOut = 'In') ca
WHERE yt.InOut = 'Out'