Help on Division results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help on Division results

Author

Topic

badpupsd
Starting Member

8 Posts

Posted - 2010-03-09 : 14:24:39

I have:
DECLARE @T3 table
(
Users varchar(45),
Checked numeric(9,0),
Problem numeric(9,0),
PerChecked numeric(9,0),
PerProblem varchar(15)
)
Insert into @T3
SELECT
Users
,Checked
,Problem
,0 as PerChecked
,cast(cast(str(Problem *1.00 / Checked *100.00) as numeric(9,2)) as varchar(10)) + '%' as 'PerProblem'
From
@T2

The first row returns
Checked = 14
Problem = 1

I'm expecting PerProblem to be 7.14% but i can only get 7.00 to display.

All help is greatly appreciated.
Thank you

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-03-09 : 14:48:27

Could you send the declare statements for @t2 as well as some sample data?

Thanks,

Jim

Everyday I learn something that somebody else already knew

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2010-03-09 : 14:57:07

,cast(cast(~~str~~(Problem *1.00 / Checked *100.00) as numeric(9,2)) as varchar(10)) + '%' as 'PerProblem'

badpupsd
Starting Member

8 Posts

Posted - 2010-03-09 : 17:05:06

quote:
Originally posted by jimf

Could you send the declare statements for @t2 as well as some sample data?

Thanks,

Jim

Everyday I learn something that somebody else already knew

@T2 -->

DECLARE @T2 table
(
Users varchar(45),
Checked int,
Problem int
)

Sample Data
user1 14 1
user2 21 2
user3 2 1
user4 2 0
user5 5 3

for
user1 -- i expect 7.14% i get 7.00%
user2 -- i expect 9.52% i get 9.00%
etc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 01:51:03

Why did you use str function?
It is the reason for truncation
See this example

select str(234.54),str(234.54*10),str(234.54*100.4)

Madhivanan

Failing to plan is Planning to fail

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-03-10 : 07:52:18

DECLARE @T2 table
(
Users varchar(45),
Checked int,
Problem int
)

INSERT INTO @T2

SELECT 'user1', 14, 1 UNION
SELECT 'user2', 21, 2 UNION
SELECT 'user3', 2, 1 UNION
SELECT 'user4', 2, 0 UNION
SELECT 'user5', 5, 3

DECLARE @T3 table
(
Users varchar(45),
Checked numeric(9,0),
Problem numeric(9,0),
PerChecked numeric(9,0),
PerProblem varchar(15)
)
Insert into @T3
SELECT
Users
,Checked
,Problem
,0 as PerChecked
--,cast(cast(str(Problem *1.00 / Checked *100.00) as numeric(9,2)) as varchar(10)) + '%' as 'PerProblem'
, CONVERT(varchar(15),CONVERT(numeric(9,2),Problem*100.0E/Checked))+ '%'
From
@T2

Jim

Everyday I learn something that somebody else already knew

badpupsd
Starting Member

8 Posts

Posted - 2010-03-10 : 11:19:49

Thank you All very much !
You helped me get it all sorted.

thanks
again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources