The missing .01 (rounding time)

[Countymnca]

Hello,
I have a tough one that I cant seem to solve. Below are the details:

We have a 3rd party vendor SQL app that does time and attendance (among other things) and our employees track their time via project codes depending on what they are working on. The problem I am having is that when an employee works on a project for 20 minutes, their time is shown as .33. (We use 1 minute rounding). If they have several different 20 minute segments on different projects, their time for their 8 hours day could be shown as:

2.33
2.33
3.33

Each time calculation is rounded to 2 places, so the .0033333 for each is lost. The total for the day is not calculated, but our accounting section looks at the above entries and reports that .01 is missing for the day.

My thought to address this is to create a query to check the results table for employees and days that total to 7.99 and then run an update query to update the first row to add .01 to whatever it has. That would change the timesheet report to displaying:

2.34
2.33
3.33

Is there another way? Other ideas?

Thanks

 

[bborissov]

Please, post some example data and desired result from it.


Borislav Borissov
VFP9 SP2, SQL Server

 

[MakeItSo]

Your trouble starts earlier:
let's say someone only has two 20 minute stints a day. These would add up to x.66 - whereas correctly rounded to 2 decimals it should actually be x.67.
So your 0.1 goes missing on the second entry already.

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[SkipVought]

Hi,

All the time & attendance systems I've used, make it the employee's responsibility to accurately enter their time. Thus the employee would have to accommodate the lack of precision for the day to enter a total of 8 hours, which means that one entry would need to be entered as .34 for 3 20 minute (.33) entries.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[gk53]

you should calculate time in minutes in this case it will be not 2.33 + 2.33 + 2.33 = 6.99 but 140 + 140 + 140 = 420 min or 420/60 = 7 hours

 

[tcsbiz]

Is the rounding happening on screens and reports? If so, that is the nature of the time and attendance beast. My users know the rounding exits and they deal with it. As long as the app is saving the raw time in minutes, the data should be OK when it transfers to payroll, which is what really matters.

Tom

 

[Countymnca]

Below is an example of what the timesheet looks like: The total time for each project on each day is summed and displayed on one row. There is no daily total for the day, displayed on the timesheet or in the data.

Wed Thur Fri Total
Project 1 2.5 2.33 3.0 7.33
Project 2 3.0 2.33 2.33 7.33
Project 3 3.0 2.33 3.0 8.33

In the above example, the day of Thur would be the problem as it only totals to 6.99. The raw start and end times is stored in the system and the above values are calculated when the timesheet is created or exported.

I have been explaining that this is due to the repeating decimals, but with 4,000 employees, that is a losing battle. The only time this is a problem it seems is when there are 3 examples of .3333333 on the same day as it does round to .67 when it is .666666. Those days add to 1.00 (.67+.33), so there isnt a display issue.

 

[SkipVought]

The raw start and end times is stored in the system and the above values are calculated [highlight #FCE94F]when the timesheet is created or exported[/highlight].

Well THAT would be an extremely important question to discover the answer to, because if the truncation accursed on the export, then you at least have a possibility of getting better precision using some other export method perhaps.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Olaf Doschke]

No matter what you do, you have a mismatch of the decimal system and the time units. Add one decimal place and then .001 will be missing. It's very clear this is a rounding issue, so why bother about it at all?
Time attendance system would round to next five minutes, so you have multiples of 5 minutes or 1/12 of an hour, which in decimal has infinite decimal places: 0.08333333... Nobody ever will report something like 0.01 hour, that's 36 seconds. Who would measure that? Maybe if you measure the time by a stop clock, which some systems really offer.

Displaying the times in hh:mm format would get rid of the issue, wouldn't it? Summation of times is easily done:

--Drop table #temp

Create Table #temp (hoursworked time)
insert into #temp values ('2:20')
insert into #temp values ('2:20')
insert into #temp values ('2:20')

Select Cast( dateadd(MINUTE, sum(datediff(MINUTE, 0, hoursworked)),0) as Time) as Total from #temp

 

[Olaf Doschke]

The forum engine cut off the code language I specified: T-SQL (SQL Server 2008 or higher). The Time type would need to exist for that code demo.

Bye, Olaf.

 

[Countymnca]

I understand this is the nature of dealing with time and rounding. This is a display/human problem. The darn timesheet doesnt even total the hours by the day, but people still look at it and then report a bug when .33+.33+.33 doesnt add to 1.00.

I also think it is a timesheet report item and will focus my work there.

Thanks for the feedback and ideas

 

[gmmastros]

As others have pointed out, working with dates instead of fractional numbers would resolve your problem. On a whim, I thought, what would happen if the data was converted to a date, and then summed on the minutes. The query gets a bit ugly, but also seems to work. Here is how I tested it...

Declare @Temp Table(Project VarChar(100), Wed Decimal(10,2), Thur Decimal(10,2), Fri Decimal(10,2), Total Decimal(10,2))

Insert Into @Temp Values('Project 1', 2.5, 2.33, 3.0, 7.33)
Insert Into @Temp Values('Project 2', 3.0, 2.33, 2.33, 7.33)
Insert Into @Temp Values('Project 3', 3.0, 2.33, 3.0, 8.33)

Select  Sum(Thur) As RoundedWeird,
        Sum([!]Round([/!]Thur[!] * 60, 0)[/!]) / 60 As RoundedBetter
From    @Temp

This query returned 6.99 for the straight up sum, but returned 7.00 for the "RoundedBetter" expression.

Converting from hours to minutes is a simple multiplication. For 20 minutes, the real value is 0.3 (repeating) but is entered as 0.33 in your data. If you multiply 0.33 * 60, you get 19.8 minutes. When rounding this to the nearest whole number, you would get 20 minutes. So... summing the rounded minutes may effectively solve your problem. You should probably test this very thoroughly because it will likely affect payroll.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom