Grouping accuracy

Hi,
This is a problem which is causing a few difficulties. We have a time
recording system which just tracks what people are working on. The system
basically keeps tracking of how much time is spent by a person on a certain
type of work.
The problem is when we group the data by different categories, the total
usually never matches exactly. The time spent on work is stored as an intege
r
in the table representing "minutes" e.g. 450 mintues is the equivalent of 1
day (we work a 7.5 hour day".
Run this is Query Analyser and you can see what I mean. You will see that
the the 2 query results returned will differ in their total "days" by 0.01.
The total "days" in query 1 return 3.91 where as it is 3.9 in query 2.
What can I do to make this fully accurate? Should I use a different datatype
in the table?
--Create a Test Table, it will be dropped later
create table timeRecords (person char(5),Workdate datetime,timeInMins
integer,worktype varchar(10))
--Insert Some Test Data
insert timeRecords
values('jackp','2005-10-01',225,'Client')
insert timeRecords
values('jackp','2005-10-01',225,'R&D')
insert timeRecords
values('jackp','2005-10-01',60,'Sales')
insert timeRecords
values('peter','2005-10-01',225,'Client')
insert timeRecords
values('peter','2005-10-01',180,'R&D')
insert timeRecords
values('peter','2005-10-01',160,'Sales')
insert timeRecords
values('jackp','2005-10-01',225,'Client')
insert timeRecords
values('jackp','2005-10-01',120,'R&D')
insert timeRecords
values('jackp','2005-10-01',60,'Sales')
insert timeRecords
values('peter','2005-10-01',80,'Client')
insert timeRecords
values('peter','2005-10-01',180,'R&D')
insert timeRecords
values('peter','2005-10-01',15,'Sales')
--Group by Person
select person,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
decimal(9,2))
From timeRecords
group by person
--Group by WorkType
select worktype,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
decimal(9,2))
From timeRecords
group by worktype
--Drop the table
drop table timeRecordsIt's because of round-off. When I changed the decimal casts to decimal (9,
4), the first totalled 3.9000, while the second came to 3.9001.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"NH" <NH@.discussions.microsoft.com> wrote in message
news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
> Hi,
> This is a problem which is causing a few difficulties. We have a time
> recording system which just tracks what people are working on. The system
> basically keeps tracking of how much time is spent by a person on a
> certain
> type of work.
> The problem is when we group the data by different categories, the total
> usually never matches exactly. The time spent on work is stored as an
> integer
> in the table representing "minutes" e.g. 450 mintues is the equivalent of
> 1
> day (we work a 7.5 hour day".
> Run this is Query Analyser and you can see what I mean. You will see that
> the the 2 query results returned will differ in their total "days" by
> 0.01.
> The total "days" in query 1 return 3.91 where as it is 3.9 in query 2.
> What can I do to make this fully accurate? Should I use a different
> datatype
> in the table?
> --Create a Test Table, it will be dropped later
> create table timeRecords (person char(5),Workdate datetime,timeInMins
> integer,worktype varchar(10))
> --Insert Some Test Data
> insert timeRecords
> values('jackp','2005-10-01',225,'Client')
> insert timeRecords
> values('jackp','2005-10-01',225,'R&D')
> insert timeRecords
> values('jackp','2005-10-01',60,'Sales')
> insert timeRecords
> values('peter','2005-10-01',225,'Client')
> insert timeRecords
> values('peter','2005-10-01',180,'R&D')
> insert timeRecords
> values('peter','2005-10-01',160,'Sales')
> insert timeRecords
> values('jackp','2005-10-01',225,'Client')
> insert timeRecords
> values('jackp','2005-10-01',120,'R&D')
> insert timeRecords
> values('jackp','2005-10-01',60,'Sales')
> insert timeRecords
> values('peter','2005-10-01',80,'Client')
> insert timeRecords
> values('peter','2005-10-01',180,'R&D')
> insert timeRecords
> values('peter','2005-10-01',15,'Sales')
> --Group by Person
> select person,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
> decimal(9,2))
> From timeRecords
> group by person
> --Group by WorkType
> select worktype,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
> decimal(9,2))
> From timeRecords
> group by worktype
> --Drop the table
> drop table timeRecords|||"NH" <NH@.discussions.microsoft.com> wrote in message
news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
> Hi,
> This is a problem which is causing a few difficulties. We have a time
> recording system which just tracks what people are working on. The system
> basically keeps tracking of how much time is spent by a person on a
> certain
> type of work.
> The problem is when we group the data by different categories, the total
> usually never matches exactly. The time spent on work is stored as an
> integer
> in the table representing "minutes" e.g. 450 mintues is the equivalent of
> 1
> day (we work a 7.5 hour day".
> Run this is Query Analyser and you can see what I mean. You will see that
> the the 2 query results returned will differ in their total "days" by
> 0.01.
> The total "days" in query 1 return 3.91 where as it is 3.9 in query 2.
> What can I do to make this fully accurate? Should I use a different
> datatype
> in the table?
>
It's not your datatype that is at issue. Decimals (and Numeric) store data
in an exact format. Unlike floats and reals, which are a close
approximation. The problem is that by default SQL Server uses a ROUND UP
strategy when performing computations. If you switch everything to decimal
(9,6) you should see better numbers pop up.
You will see:
jackp 2.033333
peter 1.866667
--
3.900000
Client 1.677778
R&D 1.566667
Sales .6555556
--
3.900001
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||thanks for the reply.
So there is no way really of making this fully accurate?
I understant it is a rounding issue, I thought maybe there was something I
was overlooking or some way of doing it better so there are no rounding
effects.
"Tom Moreau" wrote:

> It's because of round-off. When I changed the decimal casts to decimal (9
,
> 4), the first totalled 3.9000, while the second came to 3.9001.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
>
>|||The numbers need to be reported to 2 decimal places, I guess we will live
with it. I knew the cause of it was rounding up but I wasnt sure if there wa
s
a best practice way of doing this. Sounds like there isn't much we can do
about it.
"Rick Sawtell" wrote:

> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
> It's not your datatype that is at issue. Decimals (and Numeric) store dat
a
> in an exact format. Unlike floats and reals, which are a close
> approximation. The problem is that by default SQL Server uses a ROUND UP
> strategy when performing computations. If you switch everything to decim
al
> (9,6) you should see better numbers pop up.
> You will see:
> jackp 2.033333
> peter 1.866667
> --
> 3.900000
> Client 1.677778
> R&D 1.566667
> Sales .6555556
> --
> 3.900001
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||The problem is that the numbers work out to repeating decimals. It's just
as likely to be 0.001 too low as .001 too low. If you use WITH ROLLUP, the
total is 3.90:
select worktype,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
decimal(9,2))
From timeRecords
group by worktype
with rollup
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"NH" <NH@.discussions.microsoft.com> wrote in message
news:B0DAEC4B-EBF1-4F92-812D-D15852E959E4@.microsoft.com...
> thanks for the reply.
> So there is no way really of making this fully accurate?
> I understant it is a rounding issue, I thought maybe there was something I
> was overlooking or some way of doing it better so there are no rounding
> effects.
> "Tom Moreau" wrote:
>