Regular and overtime calculations 1

[HowardHammerman]

My client is a plumber and his employees fill out work tickets when they are on a job. All this is entered into an SQL database. The table I am working with has one record per ticket with start datetime and enddatime fields. The task is to calculate regular hours and overtime hours from each ticket. Regular time starts at 8am Monday to Friday and ends at 4pm Monday to Friday. All other days and times are overtime. A ticket can start in regular time and extend into overtime and in that case it needs to be split. Likewise, a ticket can start in overtime and end in regular time. And a ticket can start one day and extend into the next day. Obviously, a ticket started on Saturday or Sunday is OT but can extend into regular time.

I think the trick is to calculate total time:
datedif("h",{table.start},{table.end}) and then subtract either regular time or OT from it.

The solution that solves this in fewer than 40 lines of code wins a prize.

Thanks in advance

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[jmd0252]

So according to your information any time worked outside of the 8 to 4 during a weekday is over time. No Exceptions.

 

[Madawc]

I'd do it as two separate calculations, one for regular time and one for overtime. Set up the rules and then display against the data.

So if they work 12 to 6, regular calcuates 9 to 16:00, 7, and overtime 16:00 to 18:00, 2. Display and get it right.

Once you have these values at detail line, they can simply be totalled and calculated on.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

Howard,

If a ticket starts on Friday morning and ends on Tuesday afternoon, does this mean people are working nonstop around the clock? A start datetime and an end datetime don't seem sufficient for calculating hours worked when the ticket crosses days.

-LB

 

[jmd0252]

great question LB. I suspect there is more information that is needed before coming up with a solution..

 

[RTag]

Are you looking for a solution in crystal reports or in SQL Server? How you are going to handle Holidays?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[HowardHammerman]

Here are answers to the above questions:
Yes, if a ticket starts on Friday and ends on Tuesday the people are working non-stop.

Don't worry about holidays. Let's get the standard calcs right.

Yes, it is done ONLY in Crystal. Not SQL server.

Yes, any time worked outside of the 8 to 4 during a weekday is over time.

Yes, doing this as two separate calculations is the way to go. Basicaly, we need to find the total REGULAR hours. Then OT is simply the total hours less the regular hours.



Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[lbass]

Try this:

//{@regtime}:
datevar dst := date({table.startdt});
datevar dend := date({table.enddt});
datetimevar wkst := datetime(0,0,0,0,0,0);
datetimevar wkend := datetime(0,0,0,0,0,0);
timevar tst := time({table.startdt});
timevar tend := time({table.enddt});

if dayofweek(dst) in 2 to 6 and
tst in time(8,0,0) to time(16,0,0) then
wkst := datetime(dst,tst);

if dayofweek(dst) in 2 to 6 and
tst < time(8,0,0) then
wkst := datetime(dst,time(8,0,0));

if dayofweek(dst) in 2 to 5 and
tst > time(16,0,0) then
wkst := datetime(dst+1,time(8,0,0));

if dayofweek(dst) = 7 then
wkst := datetime(dst+2,time(8,0,0));

if dayofweek(dst) = 1 then
wkst := datetime(dst+1,time(8,0,0));

if dayofweek(dend) in 2 to 6 and
tend in time(8,0,0) to time(16,0,0) then
wkend := datetime(dend,tend);

if dayofweek(dend) in 2 to 6 and
tend > time(16,0,0) then
wkend := datetime(dend,time(16,0,0));

if dayofweek(dend) in 3 to 6 and
tend < time(8,0,0) then
wkend := datetime(dend-1,time(16,0,0));

if dayofweek(dend) = 7 then
wkend := datetime(dend-1,time(16,0,0));

if dayofweek(dend) = 1 then
wkend := datetime(dend-2,time(16,0,0));

datediff("h",wkst,wkend)

-LB

 

[RTag]

This can be done with one SELECT query in SQLserver. Is this a quiz or real world report ?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[HowardHammerman]

R-Tag, this is NOT a quiz. I wish it were.

Lbass, thank you for your help. It works in most cases.
When the start and end is in OT then there is a negative number but that is OK since I am calculating regular time. I will test for the negative and if true set it to zero.

However, when start is 12/10/2011 11:00:00 and
when end is 12/10/2011 13:00:00 The formula yields -64

when start is 12/13/2011 18:00:00 and end is 12/15/2011 11:00:00 the formula yields 27 when the correct number is 11. 8 for 12/14 and 3 for 12/15.




Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[RTag]

Howard if this can resolve your issue here is the full example:

http://r-tag.com/OvertimeCalculation.aspx

It is one SQLServer query. You can use a command and still keep all the logic inside the report.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[HowardHammerman]

Rtag, I don't have access to their SQL server.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[lbass]

I developed that formula quickly and in retrospect I see that it doesn't really address the jobs that cross days. I'll have to put more thought into it, but can't do it right now.

Please try R-Tag's solution and let us know how that works.

-LB

 

[RTag]

Howard, It is not necessary to access their SQLserver. You can create a command in Crystal reports and paste the query there.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[HowardHammerman]

How does one do that?

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[RTag]

Somebody may post a better link. Quick search in Google ("SQL Command in crystal reports") returned this:

http://m.youtube.com/#/watch?desktop_uri=/watch?v=BHNHsIUbcn8&v=BHNHsIUbcn8&gl=US

I run the search from my phone and the link may be pointing to the mobile version.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[lbass]

Try this--a combination of my earlier suggestion and a formula from Ken Hamady's Common Formulas (#13) faq:

datevar dst := date({table.startdt});
datevar dend := date({table.enddt});
datetimevar wkst := datetime(0,0,0,0,0,0);
datetimevar wkend := datetime(0,0,0,0,0,0);
timevar tst := time({table.startdt});
timevar tend := time({table.enddt});
numbervar days := 0;
numbervar bhrs := 0;

if dayofweek(dst) in 2 to 6 and
tst in time(8,0,0) to time(16,0,0) then
wkst := datetime(dst,tst);

if dayofweek(dst) in 2 to 6 and
tst < time(8,0,0) then
wkst := datetime(dst,time(8,0,0));

if dayofweek(dst) in 2 to 5 and
tst > time(16,0,0) then
wkst := datetime(dst+1,time(8,0,0));

if dayofweek(dst) = 7 then
wkst := datetime(dst+2,time(8,0,0));

if dayofweek(dst) = 1 then
wkst := datetime(dst+1,time(8,0,0));

if dayofweek(dend) in 2 to 6 and
tend in time(8,0,0) to time(16,0,0) then
wkend := datetime(dend,tend);

if dayofweek(dend) in 2 to 6 and
tend > time(16,0,0) then
wkend := datetime(dend,time(16,0,0));

if dayofweek(dend) in 3 to 6 and
tend < time(8,0,0) then
wkend := datetime(dend-1,time(16,0,0));

if dayofweek(dend) = 7 then
wkend := datetime(dend-1,time(16,0,0));

if dayofweek(dend) = 1 then
wkend := datetime(dend-2,time(16,0,0));

days := datediff("d",wkst,wkend)+1-
datediff("ww",wkst,wkend,crSaturday)-
datediff("ww",wkst,wkend,crSunday);

TimeVar SetStart := TimeValue( "8:00");// The start your work day
TimeVar SetEnd := TimeValue("16:00");// The end your work day
bhrs := Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - time(wkend)) / 3600)
- ((time(wkst) - SetStart) / 3600);
if bhrs < 0 then
bhrs := 0 else
bhrs := bhrs;

-LB

 

[HowardHammerman]

Lbass,

Thank you for your excellent work. I found out from the client that a ticket would never go more than overnight. So a span of two days would not happen. I adapted your earlier suggestion.

There was a problem with a time field which I will address in another post.

Thank you so much.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com