Driver OverTime Calculations

[ixl777]

Flow Logic
============
TOW TRUCK DRIVER - EACH DRIVER has TO have 40 HOURS BEFORE overtime kicks IN
ALL TIME IS IN military TIME, ALL Weeks BEGIN ON Sunday
ALL DATETIME's are based on United States Date and Time with 2 daylight savings to include

I SUM-up how many DRIVER HOURS FROM previous entries TO FIND greater than less than 40 FOR that WEEK
AND STORE a logical VALUE TO: llOver40 .T. OR .F.


DRIVER works 0700 TO 1700 REG TIME
DRIVER overtime IS OverTimeStart 1700 TO OverTimeEnd 0700

DRIVER creates an invoice WITH the invoice START TIME AND invoice Ending TIME, AND OTHER INFO

EMP/DRIVER FILE STRUCTURE
============================
DriverName = JOHN DOE
overtime START = 1700
OverTimeEnd = 0700
RegRate = $11.00
OverRate = $17.00

Need guidance OR pesudo CODE TO HELP me thru THIS please
should I convert ALL datetimes INTO seconfs AND THEN DO calculations/Logic?


SOME OF MY Logic - I KNOW I AM MISSING SOMETHING
I can think OF 4 possible cases TO CHECK
1) TIME period IS completely inside overtime
2) TIME period IS bigger than the overtime
3) TIME period starts BEFORE the overtime but ENDS inside the overtime
4) TIME period starts inside the overtime but ENDS AFTER the overtime

SELECT 0
USE invoice ALIAS invoice SHARED
SET ORDER TO NAME
SEEK 'JOHN DOE'

IF ! FOUND()
* someone deleted this driver, throw a flag/message to user
ENDIF

* found employee/driver
* I SUM-UP HOW MANY DRIVER HOURS FROM previous entries TO FIND greater than less than 40 FOR that WEEK
* AND STORE a logical VALUE TO: llOver40 .T. OR .F.
llOver40=.T. &&..force to over 40 hours

* set vars
lnRegHrs=0
lnOverHrs=0

* invoice times
ltInvStartTime=CTOT("2012-01-16T15:00:00") &&..invoice DateTime Start
ltInvEndTime=CTOT("2012-01-17T02:30:00") &&..invoice DateTime End

* emp start/end over times
ltEmpStartOver=CTOT("2012-01-16T17:00:00") &&..time driver starts overtime
ltEmpEndOver=CTOT("2012-01-17T07:00:00") &&..time driver ends overtime

DO CASE

* all overtime periods
* 1-16-12 17:00 1-16-12 15:00 1-17-12 02:30 1-17-12 07:00
CASE ltInvStartTime>=ltEmpStartOver AND ltInvEndTime <= ltEmpEndOver
IF llOver40 &&..if over 40 hours
lnOverHrs=ltInvEndTime-ltInvStartTime
ELSE
lnRegHours=ltInvEndTime-ltInvStartTime
ENDIF
REPLACE reghrs WITH lnRegHrs, OverHrs WITH lnOverHrs
RETURN .T.

* I GET PROGRAMMERS BLOCK HERE AND JUST TOTALLY LOSE IT

* Period bigger than end overtime
* 1-16-12 15:00 1-16-12 17:00 1-17-12 07:00 1-17-12 02:30
* .T. .T.
CASE ltInvStartTime <= ltEmpStartOver AND ltEmpEndOver >= ltInvEndTime
IF llOver40 &&..if over 40 hours
lnRegHrs=ltEmpStartOver-lnInvStartTime
lnOverHrs=ltInvEndTime-ltEmpStartOver
ELSE
lnRegHrs=(ltEmpStartOver-lnInvStartTime)+(ltInvEndTime-ltEmpStartOver)
ENDIF
REPLACE reghrs WITH lnRegHrs, OverHrs WITH lnOverHrs
RETURN .T.

* Period starts before emp start overtime but ends inside overtime period
* 1-16-12 15:00 1-16-12 17:00 1-17-12 02:30 1-17-12 07:00
* .T.
CASE ltInvStartTime <= ltEmpStartOver AND ltInvEndTime <= ltEmpEndOver AND ltInvEndTime >= ltEmpStartOver


* Period starts after overtime starts and goes beyond overtime
CASE ltInvStartTime >= ltEmpStartOver AND ltInvEndTime >= ltEmpEndOver AND ltInvStartTime <= ltEmpEndOver

ENDCASE
RETURN

Thanks,
Randy

 

[JRB-Bldr]

Since you say...

OverTimeSTART = 1700
OverTimeEnd = 0700

I assume that you need to compute each individual day's OverTime.

That being the case, then you just need to determine how many Seconds are outside of the 'standard pay' time brackets on a day-by-day basis.

There is a LOT about your code above that I am not clear on, but in general, you could do something like...

* --- All in Seconds of Day ---
nStartRegHrs = (7 * 3600)
nEndRegHrs = (17 * 3600)

dPayPeriodStart = <some date>
dPayPeriodEnd = <another date>
nDays = dPayPeriodEnd - dPayPeriodStart
FOR DayCntr = 0 TO nDays
   dThisDay = dPayPeriodStart + DayCntr

   SELECT DriverStartEnd
   SCAN FOR Date = dThisDay
      * - calculate each driver's standard & OT time - 
      cDriver = DriverStartEnd.Driver

      * --- All values in Seconds of Day ---
      nStartDay = DriverStartEnd.StartTime
      nStartingOT = MAX(nStartRegHrs - nStartDay,0)

      nEndDay = DriverStartEnd.EndTime 
      nEndingOT = MAX(nEndDay - nEndRegHrs,0)  

      nRegTime = nEndDay - nStartDay 
      nDayOT = nStartingOT + nEndingOT

      * --- Store Values Away For This Driver ---
      * --- Could convert Seconds to Hr.Min.Sec ---
      * ------ or do it elsewhere ---
      <do whatever>
   ENDSCAN   
ENDFOR

* --- Now just accumulate the results for the Pay Period ---

NOTE - I just jotted down the above code, I have not tested it so there could very likely be typos, etc.

Good Luck,
JRB-Bldr

 

[IlyaRabyy]

Not countering colleague's JRB's suggestion, may I ask you, colleague Randy, if it is, indeed, so imperative to calculate the overtime for each day, and only from 17:00 to 07:00 to boot?

What I would try is to calculate (SELECT statement with SUM(EndTime - StartTime) and GROUP BY driver ID) total work-hours time for each driver for each week, and see if it's over 40 hrs.: after all, the OT time is valid if it's over 40 hrs during the work week, AFAIK, isn't it? (At least, it is so in the state of California... but I think it's by the federal law.)

HTH.

Regards,

Ilya

 

[JRB-Bldr]

What Ilya says is true.

Some payroll systems need to calculate OverTime on an over-all Weekly basis where the StartTime and EndTime is not important.

Other payroll systems need to calculate OverTime for each individual day and accumulate the individual day's totals for the over-all Pay Period.

I was going by an assumption that since it seemed that StartTime and EndTime for each day was stated, that you needed to calculate OT on a day-by-day basis. You need to clarify that for us.

Also in my somewhat simplistic code above I assumed that no individual was going to Start a specific Day's hours on the Previous Day nor End the specific Day's hours on the following Day.

Some more clarification would help us give you better advice.

Good Luck,
JRB-Bldr

 

[ixl777]

Thank all of you for your replies, suggestions and comments!

Background of Problem
=======================
A Tow Truck Driver can work around the clock filling out
invoices for each tow.
Each driver has to have 40 hours before overtime kicks in
ALL TIME IS IN military TIME, ALL Weeks BEGIN ON Sunday
ALL DATETIME's are based on United States Date and Time with 2 daylight savings to include

I need to figure out if each invoice InvoiceStartTime and InvoiceEndTime is inside Driver's StartOverTime and EndOverTime and save how much RegTime and OverTime went on each invoice.

example:

A driver's Invoice Start Time is 15:00 to 02:30
So from 15:00 to 17:00 is regtime
and 17:00 to 02:30 is overtime

PROBLEM: Logic to figure out if each invoice InvoiceStartTime and InvoiceEndTime is inside Driver's StartOverTime and EndOverTime
periods.

To start
I sum-up how many driver hours and save to varibles and also
a logical varible: llOver40=.T. or llOver40=.F.

DRIVER works 0700 to 1700 Reg Time
DRIVER overtime OverTimeStart 1700 to OverTimeEnd 0700

DRIVER creates an invoice with the invoice start time and
invoice ending time, and other info

INVOICE FILE STRUCTURE
======================
INVOICE# DATE DRIVER TIMESTART TIMEFINISH
12345567 01/18/12 JOHN 15:00 02:30
32675547 01/18/12 JOHN 02:50 05:30
13434453 01/18/12 JOHN 06:00 07:30

DRIVER FILE STRUCTURE
============================
DriverName StartOverTime OverTimeEnd
JOHN 1700 0700

Need guidance or pesudo code to help me thru Logic to figure out if each invoice InvoiceStartTime and InvoiceEndTime is inside Driver's StartOverTime and EndOverTime periods.

Should I convert all datetimes into seconds and then do calculations/Logic? Don't I need the datetime to determine if
next next, past midnite...right?

I can think OF 4 possible cases TO CHECK for.
TIME Period = invoice start and finish times within driver startovertime and endovertime
=========================================================
1) TIME period is completely inside overtime
2) TIME period is bigger than the overtime
3) TIME period starts before the overtime but ends inside the overtime
4) TIME period starts inside the overtime but ends after the overtime

* set vars
lnRegHrs=0
lnOverHrs=0

* invoice times
ltInvStartTime=CTOT("2012-01-16T15:00:00") &&..invoice DateTime Start
ltInvEndTime=CTOT("2012-01-17T02:30:00") &&..invoice DateTime End

* emp start/end over times
ltEmpStartOver=CTOT("2012-01-16T17:00:00") &&..time invoice starts overtime
ltEmpEndOver=CTOT("2012-01-17T07:00:00") &&..time invoice ends overtime

DO CASE

* this one i figured out
CASE ltInvStartTime >= ltEmpStartOver AND ltInvEndTime <= ltEmpEndOver
IF llOver40 &&..if over 40 hours
lnOverHrs=ltInvEndTime-ltInvStartTime
ELSE
lnRegHours=ltInvEndTime-ltInvStartTime
ENDIF
REPLACE reghrs WITH lnRegHrs, OverHrs WITH lnOverHrs
RETURN .T.

* I LOSE IT RIGHT HERE

CASE ltInvStartTime >= ltEmpStartOver AND ltInvEndTime <= ltEmpEndOver
IF llOver40 &&..if over 40 hours
lnOverHrs=ltInvEndTime-ltInvStartTime
ELSE
lnRegHours=ltInvEndTime-ltInvStartTime
ENDIF
REPLACE reghrs WITH lnRegHrs, OverHrs WITH lnOverHrs
RETURN .T.

etc.

I hope this describes my delima better !
Thanks......Randy

 

[JRB-Bldr]

You are providing a LOT of information about how the data is to be used, etc., but only a little of that information is applicable to your issue here.

Lets simplify things and just focus on only the basics for now.

You are NOT clarifying the primary (at least to me) question...
* Is OverTime to be calculated on a Day-by-Day basis (meaning ANYTHING outside of the daily 7:00 am to 5:00 pm time 'window')?

You say: Logic to figure out if each invoice InvoiceStartTime and InvoiceEndTime is inside Driver's StartOverTime and EndOverTime
periods.
Since each StartOverTime & EndOverTime is applicable to each individual day this seems to imply Day-by-Day calculations are necessary.

But you go on to say: Each driver has to have 40 hours before overtime kicks in This possibly contradicts what you have said about calculation on StartOverTime & EndOverTime periods.

Obviously if needed to do so, you can always calculate the OT on a Day-by-Day basis and merge those hours into the Regular Hours if the over-all hours are less than 40.

* Or is OverTime to be calculated on the Over-all Week (meaning ANY time over 40 Hours)?

Good Luck,
JRB-Bldr

 

[ixl777]

jrbbldr,
Yes you are right!
OverTime to be calculated on the Over-all Week (meaning ANY time over 40 Hours)?
The only thing I am trying to figure is:

if each invoice InvoiceStartTime and InvoiceEndTime is inside Driver's StartOverTime and EndOverTime periods.

please forget wheather or not driver hit 40 hours.

if ltInvStartTime >= ltEmpStartOver AND ltInvEndTime <= ltEmpEndOver
* do somthing
endif

if ltInvStartTime >= ltEmpStartOver AND ltInvEndTime >= ltEmpEndOver
* do somthing
endif

thanks...Randy

 

[JRB-Bldr]

OverTime to be calculated on the Over-all Week (meaning ANY time over 40 Hours)

If this is true, then you do not need to concern yourself at all with the daily StartOverTime and EndOverTime periods for each day.

You need only focus on the difference:
OverTime = ((Actual Time Worked) - (40 Hours))

If this is NOT what you want, then you are not correct with your statement:
OverTime to be calculated on the Over-all Week (meaning ANY time over 40 Hours)

Instead it would need to use Day-by-Day OT Calculations
And then

   < calculate day-by-day nRegHrs & nOTHrs>
   < accumulate week's totals >
   IF (nRegHrs4Wk + nOTHrs4Wk) <= 40
      nRegHrs4Wk = nRegHrs4Wk + nOTHrs4Wk
      nOTHrs4Wk = 0
   ELSE
      < Use nRegHrs4Wk & nOTHrs4Wk separately as needed >
   ENDIF

Note - I did look into what Ilya suggests above about using a SQL Query to accumulate the week's time rather than a SCAN/ENDSCAN and it does look like a good alternative approach.

Good Luck,
JRB-Bldr

 

[ixl777]

Thanks jrbbldr, However, I am trying to find how many reg hours
and OT hours each driver has on each invoice.

i think i got it!
am i missing or overlooked something ?

*************
* set Reg and OverTime hours
lnRegHrs=iif(lnEmpTotHrs>40,40,lnEmpTotHrs)
lnOverHrs=iif(lnEmpTotHrs>40,lnEmpTotHrs-40,0)

* invoice times - used for testing
ltInvStartTime=CTOT("2012-01-16T15:00:00") &&..invoice DateTime Start 1500-0230
ltInvEndTime=CTOT("2012-01-17T02:30:00") &&..invoice DateTime End

* invoice times - used for testing
*!* ltInvStartTime=CTOT("2012-01-16T15:00:00") &&..invoice DateTime Start 1500-1800
*!* ltInvEndTime=CTOT("2012-01-17T18:30:00") &&..invoice DateTime End

* emp start/end over times - same all the time
ltEmpStartOver=CTOT("2012-01-16T17:00:00") &&..time driver starts overtime
ltEmpEndOver=CTOT("2012-01-17T07:00:00") &&..time driver ends overtime

* 1: Invoice starts before start of overtime, Invoice ends after start of overtime and before end of over time
* 2: Invoice starts before start of overtime Invoice ends after end of overtime
* 3: Invoice starts after start of overtime and before end of overtime and Invoice ends after end of over time
* 4: Invoice starts after start of overtime and before end of overtime Invoice ends before end of over time
* 5: Invoice start/end times are within the overtime period
* 6: Invoice start/end times are before overtime period

DO CASE

* 5: Invoice start/end times are within the overtime period
* all within overtime periods
CASE ltInvStartTime >= ltEmpStartOver AND ltInvEndTime <= ltEmpEndOver
IF llOver40 &&..if over 40 hours
lnOverHrs=((ltInvEndTime-ltInvStartTime)/3600)
ELSE
lnRegHrs=((ltInvEndTime-ltInvStartTime)/3600)
ENDIF

* 6: Invoice start/end times are before overtime period
* inv start/ends before overtime started
CASE ltInvStartTime < ltEmpStartOver AND ltInvEndTime < ltEmpStartOver
IF llOver40 &&..if over 40 hours
lnOverHrs=((ltInvEndTime-ltInvStartTime)/3600)
ELSE
lnRegHrs=((ltInvEndTime-ltInvStartTime)/3600)
ENDIF

* 1500 1700 1800 1700 1800 0700
* 1: Invoice starts before start of overtime, Invoice ends after start of overtime and before end of over time
* inv start before overtime started but ended after overtime started and ended before overtime ended
CASE ltInvStartTime < ltEmpStartOver AND ltInvEndTime > ltEmpStartOver AND ltInvEndTime <= ltEmpEndOver
IF llOver40 &&..if over 40 hours
lnOverHrs=((ltEmpStartOver-ltInvStartTime)/3600) &&..hrs before overtimestart
lnOverHrs=((lnOverHrs+(ltInvEndTime-ltEmpStartOver))/3600) &&..hrs before overtimestart
ELSE
lnRegHrs=((ltEmpStartOver-ltInvStartTime)/3600) &&..hrs before overtimestart
IF lnRegHrs>=40 &&..did driver hit 40 hours?
lnOverHrs=((lnRegHrs+(ltInvEndTime-ltEmpStartOver))/3600) &&..hrs before overtimestart
ELSE
lnRegHrs=((lnRegHrs+(ltInvEndTime-ltEmpStartOver))/3600) &&..hrs before overtimestart
ENDIF
ENDIF

* 1700 1700 0900 0700
* 2: Invoice starts before start of overtime Invoice ends after end of overtime
* inv start before overtime and ended after overtime
CASE ltInvStartTime < ltEmpStartOver AND ltInvEndTime > ltEmpEndOver
IF llOver40 &&..if over 40 hours
lnOverHrs=((ltEmpStartOver-ltInvStartTime)/3600) &&..hrs before overtimestart
lnOverHrs=((lnOverHrs+(ltInvEndTime-ltEmpStartOver))/3600) &&..hrs after overtimestart
ELSE
lnRegHrs=(ltEmpStartOver-ltInvStartTime) &&..hrs before overtimestart
IF lnRegHrs>=40 &&..did driver hit 40 hours?
lnOverHrs=((lnRegHrs+(ltInvEndTime-ltEmpStartOver))/3600) &&..hrs before overtimestart
ELSE
lnRegHrs=((lnRegHrs+(ltInvEndTime-ltEmpStartOver))/3600) &&..hrs before overtimestart
ENDIF
ENDIF

* 1800 1700 1800 0700 0800 0700
* 3: Invoice starts after start of overtime and before end of overtime and Invoice ends after end of over time
* inv start after overtime started and ended after overtime ended
CASE ltInvStartTime >= ltEmpStartOver AND ltInvStartTime <= ltEmpEndOver AND ltInvEndTime > ltEmpEndOver
IF llOver40 &&..if over 40 hours
lnOverHrs=((ltInvEndTime-ltInvStartTime)/3600) &&..hrs before overtimestart
ELSE
lnRegHrs=((ltInvEndTime-ltInvStartTime)/3600) &&..hrs before overtimestart
ENDIF

* 1800 1700 1700 1700
* 4: Invoice starts after start of overtime and before end of overtime Invoice ends before end of over time
* inv start after overtime started and less than overtimeend but ended before overtime ended
CASE ltInvStartTime >= ltEmpStartOver AND ltInvStartTime <= ltEmpEndOver AND ltInvEndTime <= ltEmpEndOver
IF llOver40 &&..if over 40 hours
lnOverHrs=((ltInvEndTime-ltInvStartTime)/3600) &&..hrs before overtimestart
ELSE
lnRegHrs=((ltInvEndTime-ltInvStartTime)/3600) &&..hrs before overtimestart
ENDIF

ENDCASE

? lnRegHrs
? lnOverHrs

RETURN

 

[JRB-Bldr]

I am trying to find how many reg hours and OT hours each driver has on each invoice.

It doesn't matter if this is OT for an Invoice or Payroll or something totally different.

Either the Driver worked OT or not.

And that OT is either calculated on a day-by-day basis where each day has a StartRegHrs and an EndRegHrs time of day.

Or OT is calculated based on an over-all time-period (pay week, etc.) where you care about 40 Hrs/week and the StartRegHrs and an EndRegHrs time of day has no application.

If you have Drivers who could possibly create multiple Invoices per day and OT needs to be calculated for each separate Invoice throughout any given day based on whether they began or ended outside of the StartRegHrs and an EndRegHrs time of day, then you would use a variation of the Day-by-Day calculation and the 40 Hr 'threshold' would not be taken into consideration.

Unfortunately I can't make heads-or-tails of your code the way you are showing it above so I cannot tell if you are missing something or not.
The best way for you to tell yourself will be for you to make a test table with LOTS of variations and test them all.

Regardless, I hope you did indeed find your working solution.

Good Luck,
JRB-Bldr