Help with number formatting

Question

Post reply

Help with number formatting

grasspicker61

SSC Enthusiast

Points: 106
More actions
July 28, 2010 at 1:50 pm

#387455

/*Calculated percentage of Response Entered / Total Entered for Cleared Rate*/
CAST(SUM(CASE WHEN STAT_PRODUCTIVITY.Action != 'Folder'
and STAT_PRODUCTIVITY.Action != 'Assigned' THEN Number
ELSE 0 END) AS DECIMAL (18,2))/CAST(SUM (Number) AS DECIMAL (18,2))
AS 'Cleared'
END
This script results in a field result of 0.66743119266055045871
Any idea where my code is incorrect?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 1

What's your desired result?

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

grasspicker61 SSC Enthusiast Points: 106 More actions · Answer 2

What I am looking for is no more than 2 decimal places, ideally this would be converted to a percentage.

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 3

You need to CAST the result not each part of the equation.

CAST( number1/number2 AS DECIMAL(18,2)

Should work better. Casting each part separately only guarantees that the numbers in the equation have 2 decimals. The result could be any number of decimals.

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 4

Yep, cause for example:

If you had 18.642372121 / 37.33121

What you're doing is

18.64 / 37.33 = 0.49933029.....

But what you want is the end result as 2 digits, not the entire math.. in fact, your results could be off by converting these to 2 digits..

SELECT CAST(18.642372121 / 37.33121 AS DECIMAL(18,2))

Would be 0.50 instead of the 0.49933029 from previously

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

grasspicker61 SSC Enthusiast Points: 106 More actions · Answer 5

CAST(SUM(CASE WHEN STAT_PRODUCTIVITY.Action != 'Folder'

and STAT_PRODUCTIVITY.Action != 'Assigned' THEN Number

ELSE 0 END) AS DECIMAL)/CAST(SUM (Number) AS DECIMAL (10,2))

AS 'Cleared'

My result still comes out as 0.66743119266 (11 decimal places @ (10,2)

The column for STAT_PRODUCTIVITY.Action is an INT, but that value cannot be changed, is that the issue here? If I change the format to (4,2) the resulting data changes to 0.6674311 (7 decimal places @ (4,2). I am perplexed at this point, not to mention a novice at SQL, any help will be greatly appreciated. 😎

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 6

Only cast the overall result.

Try this:

CAST(

SUM(CASE WHEN STAT_PRODUCTIVITY.Action != 'Folder'

and STAT_PRODUCTIVITY.Action != 'Assigned' THEN Number

ELSE 0 END)/SUM (Number)

AS DECIMAL (10,2)) AS 'Cleared'

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 7

You're still converting the numbers you're performing the arithmetic on

try this - does it work better for you?

SELECT

CAST

(

(SUM

(

CASE WHEN STAT_PRODUCTIVITY.Action != 'Folder'

AND STAT_PRODUCTIVITY.Action != 'Assigned'

THEN Number ELSE 0 END

) /

SUM(Number))

AS DECIMAL (10,2)

)

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂