jollarvia jollarvia - 3 years ago 40
SQL Question

SQL Server Having clause not filtering as expected with float

I have a fairly simple subquery here. The intention is to identify employees with less than 40 hours logged for a weeks worth dataset. But every once in a while an employee with an apparent 40 hours will sneak into the view. The data behind it uses float with high precision and I try my best to first round each individual entry to two decimal places then sum all entries up grouped by employee. I even tried < 39.999 and I still get an employee with 40 hours. In fact, if taking the data as is, this employee actually has 40.0000002980232 - more than 40 so I suspect this issue has nothing to do with rounding errors and more to do with syntax. Can anyone figure out why this statement keeps bringing in rows that violate the HAVING clause?

create table #thisweek ([hours] float, time_user varchar(100))

insert into #thisweek values
(1.58000004291534, 'john.doe'),
(4.32000017166138, 'john.doe'),
(0.620000004768372, 'john.doe'),
(1, 'john.doe'),
(0.680000007152557, 'john.doe'),
(2, 'john.doe'),
(2, 'john.doe'),
(3, 'john.doe'),
(0.790000021457672, 'john.doe'),
(3, 'john.doe'),
(2, 'john.doe'),
(1, 'john.doe'),
(3.32999992370605, 'john.doe'),
(2, 'john.doe'),
(4.42000007629395, 'john.doe'),
(1.33000004291534, 'john.doe'),
(0.579999983310699, 'john.doe'),
(3.29999995231628, 'john.doe'),
(2.1800000667572, 'john.doe'),
(0.620000004768372, 'john.doe'),
(0.25, 'john.doe')

select sum(hours) from #thisweek group by time_user
/* 40.0000002980232 */
/* Test: Nothing should come up since employee John Doe has 40 hours */
select sum(Round(hours,2)) hours /*<--- this is the same value as below*/, time_user

from

#thisweek

group by time_user
having sum(Round(hours,2)) < 40 /* how is 40 < 40? */

Answer Source

The tools are lying to you when they return returns '40'. Convert style 2 returns 16 digits:

SELECT CONVERT(VARCHAR(100), SUM(ROUND(hours, 2)), 2) FROM #thisWeek

3.999999999999999e+001

When you round before summing you are accumulating error versus the original numbers. Some will round up, some will round down. In this case the round down error is larger than round up error, enough to push the sum of the rounded numbers below 40, but it was hard to see because the too was rounding the final result.

I am not convinced that the issue is more rounding down than rounding up, since other people are reporting correct behavior if the rounded value is cast to a decimal prior to summing. In any case the having is working "correctly" but you could not see that due to the display tools rounding the result to an even 40.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download