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:
>

Group Totals - Maybe Simple * Please help!

I am having a lot of difficulties in grouping totals.
Could someone please help!
EXAMPLE:
Group1 123
Group2 456
Group3 789Try using the SUM(<field_name>) function in the footer of the group. The
placement of this is going to depend on if you are using a table or lists to
do your grouping. You can play with your grouping to only show the totals if
you do not want the detail.
"Terry" wrote:
> I am having a lot of difficulties in grouping totals.
> Could someone please help!
> EXAMPLE:
> Group1 123
> Group2 456
> Group3 789
>|||Thank you.
However, are there indepth technical notes, white papers or links on how to
create mailing lists (columns) within a report?
Our China office need assistance in this matter and I have no idea what to do.
I have tried setting the report column setting to 3 and changing the width
of the report to no avail.
Any help would be greatly appreciated.
"TBraun" wrote:
> Try using the SUM(<field_name>) function in the footer of the group. The
> placement of this is going to depend on if you are using a table or lists to
> do your grouping. You can play with your grouping to only show the totals if
> you do not want the detail.
> "Terry" wrote:
> > I am having a lot of difficulties in grouping totals.
> >
> > Could someone please help!
> >
> > EXAMPLE:
> >
> > Group1 123
> > Group2 456
> > Group3 789
> >
> >|||Terry,
I have created mailing lables for some of our clients and unfortunately I
have not found any good documentation on decent procedures. I cretaed a
mailing list using a single column table with 5 detail rows. My query
returns the fields I need and formatting was a huge issue. I set the report
width and height to the size of the labels we were using. I maxed out the
column to be a little bit less than the actual report itself. I found that
sometimes if you run stuff right up to the edge on the width a little
variance will mess up the report. Make sure you set your cells to not be
able to grow. This will also mess up the formatting. Put a page break at
the end of each one so each lable prints on it's own page. We export to pdf
first then print them on a lable printer.
The same mailing list was created for sheets of labels on normal 8.5 x 11
sheets. For that I created basically the same thing but made it multiple
columns and then had to make small adjustments to place the elements on the
sheets properly.
I wish I could be of more assistance to you but that is what I have. If you
have more specific questions let me know and I will do what I can.
Tim
"Terry" wrote:
> Thank you.
> However, are there indepth technical notes, white papers or links on how to
> create mailing lists (columns) within a report?
> Our China office need assistance in this matter and I have no idea what to do.
> I have tried setting the report column setting to 3 and changing the width
> of the report to no avail.
> Any help would be greatly appreciated.
> "TBraun" wrote:
> > Try using the SUM(<field_name>) function in the footer of the group. The
> > placement of this is going to depend on if you are using a table or lists to
> > do your grouping. You can play with your grouping to only show the totals if
> > you do not want the detail.
> >
> > "Terry" wrote:
> >
> > > I am having a lot of difficulties in grouping totals.
> > >
> > > Could someone please help!
> > >
> > > EXAMPLE:
> > >
> > > Group1 123
> > > Group2 456
> > > Group3 789
> > >
> > >|||Do you have an example .RDL file available or could you provide basic
instructions on this matter?
"TBraun" wrote:
> Terry,
> I have created mailing lables for some of our clients and unfortunately I
> have not found any good documentation on decent procedures. I cretaed a
> mailing list using a single column table with 5 detail rows. My query
> returns the fields I need and formatting was a huge issue. I set the report
> width and height to the size of the labels we were using. I maxed out the
> column to be a little bit less than the actual report itself. I found that
> sometimes if you run stuff right up to the edge on the width a little
> variance will mess up the report. Make sure you set your cells to not be
> able to grow. This will also mess up the formatting. Put a page break at
> the end of each one so each lable prints on it's own page. We export to pdf
> first then print them on a lable printer.
> The same mailing list was created for sheets of labels on normal 8.5 x 11
> sheets. For that I created basically the same thing but made it multiple
> columns and then had to make small adjustments to place the elements on the
> sheets properly.
> I wish I could be of more assistance to you but that is what I have. If you
> have more specific questions let me know and I will do what I can.
> Tim
> "Terry" wrote:
> > Thank you.
> >
> > However, are there indepth technical notes, white papers or links on how to
> > create mailing lists (columns) within a report?
> >
> > Our China office need assistance in this matter and I have no idea what to do.
> >
> > I have tried setting the report column setting to 3 and changing the width
> > of the report to no avail.
> >
> > Any help would be greatly appreciated.
> >
> > "TBraun" wrote:
> >
> > > Try using the SUM(<field_name>) function in the footer of the group. The
> > > placement of this is going to depend on if you are using a table or lists to
> > > do your grouping. You can play with your grouping to only show the totals if
> > > you do not want the detail.
> > >
> > > "Terry" wrote:
> > >
> > > > I am having a lot of difficulties in grouping totals.
> > > >
> > > > Could someone please help!
> > > >
> > > > EXAMPLE:
> > > >
> > > > Group1 123
> > > > Group2 456
> > > > Group3 789
> > > >
> > > >