LOGIC ANALYSIS HELP!

[dexterdg]

I need help to come up with a logic to filter this out.

Scenario
I need to sum up the login-logout's total number of hours per agents.
On a regular case with multiple login-logout, the formula will be the first login vs the last logout based on the login date.
Twist
Now the problem is on night shifts,
for example an agent has a schedule of 10pm to 6am.
In his session, he logged in at 10pm(day 1), but somewhere along his session time he got disconnected at 2am(day 2) in the morning.
So he will login again (2am) and logout at 6am.

If I would be making reports on the login-logout of the agents who logged in on day 1,
the said agent will only have four(4) hours (10pm-2am).
And with the report to be generated the other day he will have a login time of 2am(the continuation of the previous log) up to 6am(time end of the second day at work). Which will only make four(4) hours of working time.

I hope im explaining myself clearly here, so my question is how will I be able to determine that the "n'th" time of his login-logout is still part of the previous login-logout?

 

[zelgar]

What you'll need to do is evaluate the data on an adjusted date, instead of the actual date. You'll need to add code to determine the adjusted date based on the start time:

If Time_Start < 6 then Date = Date -1

You may wish to add some wiggle room in case someone arrives earlier for the first shift, but this should be a starting point.

 

[SkipVought]

Hi,

So are you aggregating the HOURS logged on in a day or the number of log-ons in a day? The former, it seems.

So on that scenario, the agent would have 2 hours on day 1 and 6 hours on day 2, correct?

I'm sitting in a hotel room with my iPad so don't have my resources at hand. But it's calculated machine spindle cut durations from stays start/end log file: same deal.

Draw out the various scenarios where the Agent Start & Ends are covered relative to the day. Then write formulas for each duration ...

If [Agent End] >= [Day Start] Then
If [Agent Start] <= [Day End] Then
'Calc duration
Else

End If
Else

End If

 

[SkipVought]

So here's a bit more of what I was referring to last evening

[pre]

Dim AS as Date 'Agent Start
Dim AE as Date 'Agent End
Dim RS as Date 'Duration Start
Dim RE as Date 'Duration End
DS = Date 'Day Start
DE = Date + 1 'Next Day Start

Various scenarios
DS DE
AS---------AE
AS-------------------------AE
AS---------------------------------------
AS----AE
AS--------------------AE
AS------------------------------

Outside limits for Date
If (AE Is Null Or AE >= DS) And AS < DE


End If

So now
If the AS < DS then assign DS to RS, otherwise assign AS to RS
If the AE > DE Or AE Is NULL then assigh DE to RE otherwise assign AE to RE
[/pre]

So there's the pieces for you to put together.

 

[dexterdg]

To : zelgar & SkipVought

Wow, Im amazed by your ideas. I'll be putting that into application and inform you on the result.

Thanks,
Dexterdg