Comparing Time Overlap

[JCScoobyRS]

I have a query that gets me all Labor Transactions for an employee for a certain day. I take the difference between the start and stop times to get the total time for that transaction. This works great until the user is doing multiple things at once. If I were to have this data:

CLOCK_IN                    CLOCK_OUT                    TOTAL
-----------------------------------------------------------------------------
3.83                              13.45                              9.62
3.83                              13.47                              9.64
10.00                            16.47                              6.47
-----------------------------------------------------------------------------
                                                                           25.73

Those are the only 3 Labor Transactions for this particular employee. If the times didn't overlap, it would work perfect as you'd get a good distinction of how long the person was actually working but since the times overlap, I am getting bad data. Can someone give me an idea of how to approach this? Since all three times overlap, I theoretically could just take the largest TOTAL time and that would be that but what if you had this:

CLOCK_IN                    CLOCK_OUT                    TOTAL
-----------------------------------------------------------------------------
3.83                              13.45                              9.62
3.83                              13.47                              9.64
10.00                            16.47                              6.47
17.25                            18.00                              0.75
-----------------------------------------------------------------------------
                                                                           26.48

? Can someone help me out? Thanks, Jeremy

[vbcodec]

CLOCK_IN CLOCK_OUT TOTAL
-----------------------------------------------------------------------------
3.83 14.35 9.62
3.83 14.37 9.64
10.00 16.47 6.47
-----------------------------------------------------------------------------
25.73


can you tell us what kind of timing format is 3.83 and why the total of 3.83 + 14.35 is getting 9.62 ? and what kind of system is it going to be exactly. I can't really get how you want numbers to work for you

S. Mohammad Najafi

[JCScoobyRS]

Must had been a typo. I transposed a few numbers so instead of having 13.45, I had 14.35. Sorry about that. Got any idea now?

P.S. - I changed the original post to suit.

[vbcodec]

Hi again,
I'm so sorry but I really couldn't understand the situation to think about solution, you're having check-in and check-out for each employee which gives me the idea that they start working at this stime and checkout (stop working), you're counting the different check in-out time and getting total,

but where's the problem happening exactly? and what kind of company is it ? what I can guess is they want to calculate the salary for each employee


sorry I couldn't understand
S. Mohammad Najafi

[JCScoobyRS]

I'm doing this on a transaction basis. If you look at my example, the user is working on two jobs at once so if I were to see how long he was logged into jobs, he'd end up with 26+ hours instead of the 9+ hours. I don't need to know how much time he's put to jobs. I need to know how many hours out of the time we are paying him is he actually working on jobs. My approach works great for users not working on multiple jobs at once but if the user is working on more than one job at a time, he/she gets credit for working for more hours than he/she should. Let's say out of an 8 hour day, the user is clocked into jobs for 6 hours. Well, if the user was working on one job at a time, I would get the difference in the clockin and clockout times for each transaction, add them up and you'd get 6 hours. Well, if this user was working on multiple jobs at once for 4 hours, the user would end up with 10 hours. Does this make better sense?

[vbcodec]

well, what I make sense to me is how can an employee work on mutiple work, definately human cannot focus on two task at the same time and there will be stop / start time for switching with jobs, but as I asked what kind of job is it? and what is company doing? how do you measure that employee work or not? and how do you detect which job he's up to? is it based on computer login system?

if you want to determine working time of users you have to keep track of what he didn't do or his free times. lets say I am an employee and this is my working timeline

you have to do some categorization for your tables , Sessions, Task, check in, check out, working time.

SessionID = 1

Date 1/1/2004
check in = 8:00 am <<< When I come to company

TaskID = 1
Job Name = Duty A
Start=8:00am
End= 1:00pm

TaskID = 2
Job Name = Duty B
start=10:00 am
end=2:00 pm

CheckOUT = 6:00 pm << When I left company


well I haven't work from 2:00 pm until 6:00 pm so it will be 4 hours and my check-in was 8:00 means I was online for 10 hours and I was only working for 6 hours and for 3 hours I had been working on two project and for 3 hours I was working on one task only...

is it what you want ?

Hope I could be any help
S. Mohammad Najafi

PS : more description, better result for your answer

[JCScoobyRS]

They can work on two same jobs at once if the part they are building is used in both jobs. I guess I could find out how much time was overlapping and subtract it from the total so in your example, the user was on the job for 10 hours and overlapped for 3 hours leaving you with 7 hours. How can I do that programmatically?

[vbcodec]

ok,
lets get our data shape

we have session table looks like this

SessionID = <autonumber>
CheckIN = <Time format>
CheckOUT = <time format>
Date = <date format>

and we have Task Table :

SessionID = <number relational with sessionID>
TaskID = <autonumber>
StartTime = <Time>
EndTime = <End>

I give you algorithm of this that comes into my mind'

1. first we get online time from checkin checkout
2. we find total working time
3. we consider the maximum task during a overlap is two means employee is not working on more than two job at same time which makes it easier to find result
4.we find overlap times... means we search record by record that the end time of each task is after or before the start time for next task
5. we add all overlaps together which will result the overlap time

lets say for that example

Start = 8:00 am
end = 6:00 pm

TaskID = 1
Job Name = Duty A
Start=8:00am
End= 1:00pm

TaskID = 2
Job Name = Duty B
start=10:00 am
end=2:00 pm

1. online time = 10 hours

2.1 find first Start time = 8:00 am and task ID 1,
2.2 When is it finishing? > 1:00 pm
2.2 Is the next Task ID Start time have gap? no, it already begin
2.3 if there's gap we add the gap time to OFF TIME
2.4 if there's no more task find the gap from end of last task until Check out> means 2:00 pm until 6:00 pm
2.5 add the last gap to OFF time = 4 hours + 0 hours
2.6 ON TIME substract OFF TIME will be the WORKING TIME > 10 - 4 = 6 hours


3.

4. First Job ? Task ID=1 , End Time = 1:00 PM
4.2 Next Task after it ? Task ID = 2
4.3 Start and END Time for TaskID 2 ? 10:00 pm until 2:00PM
4.4 if end time Task ID(1) is before Start Time then find the overlap > TaskID(1).EndTime(1:00PM) substract TASKID(2).StartTIME(10:00AM) = 3:00 Hours

4.5 loop 4.1 until 4.4 for all tasks calculate all overlap

5.0 TOTAL WORKING TIME = 6:00 hours and total overlap is 3:00


I hope it could help you
S. Mohammad Najafi

[vb_dba]

Are you using Access, SQL Server, Oracle, etc. as your database engine? Also, are you wanting to run this for one employee at a time or for all employees?

[JCScoobyRS]

Oracle and one employee at a time. Here is my query:

SELECT SUM(LX_HRS) LaborSum FROM LX WHERE LX_DATE = TO_DATE ('" & LXDATE & "','DD-MON-YYYY') " & _
    "AND LX_EMKEY = '" & EMKEY & "' AND LX_WCKEY <> 'SHOP-TIME'"

As you can see there is a problem if the user has LX records that have overlapping times. Thanks, Jeremy

[vb_dba]

I'm more familiar with SQL Server, so I may not be of too much help. Can you write procedures in Oracle and then run the procedure passing the employee id and it return the number of hours worked? If so, I may be able to get you some pseudo code you can work off of.

[JCScoobyRS]

Yes you can. I can run stored procedures. Thanks for your help, Jeremy

[vb_dba]

OK, lets try this for starters -

declare 3 variables (MinCheckIn, MinCheckOut, MaxCheckOut) as date/time datatype.

Set MinCheckIn = Earliest CheckIn for the employee
Set MinCheckOut = Checkout for same transaction As MinCheckIn
Set MaxCheckOut = Max(CheckOut) Where CheckIn Between MinCheckIn And MinCheckOut And CheckOut > MinCheckOut
If MaxCheckOut Is Null (no records found) Set MaxCheckOut = MinCheckOut

Now MinCheckIn and MaxCheckOut should hold the overlapping time values.

So If we had:
CheckIn CheckOut
----------------------------------
8:00 16:00
12:00 14:00
13:00 17:00

MinCheckIn would be 8:00 and MaxCheckOut would be 17:00.

I've got to run right now. I'll look at it more in the morning. Play with the idea above and if it confuses you, let me know and I'll work with you in the morning.

[vbcodec]

if you couldn't get the algorithm please post the sample of your project and I will fix it with your variables in there.

S. Mohammad Najafi

[JCScoobyRS]

Originally posted by vb_dba
OK, lets try this for starters -

declare 3 variables (MinCheckIn, MinCheckOut, MaxCheckOut) as date/time datatype.

Set MinCheckIn = Earliest CheckIn for the employee
Set MinCheckOut = Checkout for same transaction As MinCheckIn
Set MaxCheckOut = Max(CheckOut) Where CheckIn Between MinCheckIn And MinCheckOut And CheckOut > MinCheckOut
If MaxCheckOut Is Null (no records found) Set MaxCheckOut = MinCheckOut

Now MinCheckIn and MaxCheckOut should hold the overlapping time values.

So If we had:
CheckIn CheckOut
----------------------------------
8:00 16:00
12:00 14:00
13:00 17:00

MinCheckIn would be 8:00 and MaxCheckOut would be 17:00.

I've got to run right now. I'll look at it more in the morning. Play with the idea above and if it confuses you, let me know and I'll work with you in the morning.

Where do we go from here? I thought I had it working but I was wrong. Thanks, Jeremy