Rounding issue

Question

Rounding issue

Lord Slaagh

SSCertifiable

Points: 5741
More actions
May 19, 2014 at 11:01 am

#293470

Hello,
I know this is kind of a basic question, but I am stuck. I am trying to get the percentage of 2 numbers. I believe this is correct, but my result set Looks like this... 100.0171 and I need it to look like this... .17. Could someone have a look at my code and help out please? I am calculating space on my DB.
Thanks in advance
cast((sum([SpaceUsedMB]) + sum([FreeSpaceMB])/1024)/(sum([SpaceUsedMB]))*100 as float)
The are no problems, only solutions. --John Lennon

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

dmandosql (5/19/2014)
Hello,
I know this is kind of a basic question, but I am stuck. I am trying to get the percentage of 2 numbers. I believe this is correct, but my result set Looks like this... 100.0171 and I need it to look like this... .17. Could someone have a look at my code and help out please? I am calculating space on my DB.
Thanks in advance
cast((sum([SpaceUsedMB]) + sum([FreeSpaceMB])/1024)/(sum([SpaceUsedMB]))*100 as float)

Not sure what the actual question is here. Are you asking how to round to 2 decimal places or is the calculation incorrect?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lord Slaagh SSCertifiable Points: 5741 More actions · Answer 2

Thanks for your response. I basically need to find the percentage capacity of the of the DB's. So I have SpaceUsed and FreeSpace. Now I assume the calculation would be...

(SpaceUsed + FreeSpace)/(FreeSpace)*100

I hope this is clear enough. SpaceUsed = 567050.500000, FreeSpace = 710.312500. And obviously adding both would give you the capacity.

The are no problems, only solutions. --John Lennon

Robert.J.Blackburn SSC Journeyman Points: 75 More actions · Answer 3

If the numerator and denominator are in MB, you don't need to divide by 1024.

PercentUsed = Used/Total * 100

= cast(SpacedUsed as Float) / (SpacedUsed + FreeSpace) * 100

If you want 2 decimal places use Round(cast(SpacedUsed as Float) / (SpacedUsed + FreeSpace) * 100, 2)

Lord Slaagh SSCertifiable Points: 5741 More actions · Answer 4

Worked perfectly. Thanks again, new job 🙂

The are no problems, only solutions. --John Lennon

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

You might want to look at the results. The calculation you provided will not correctly determine the amount of disc space used. This calculation is (FreeSpace / TotalSize) / 100.

Let's look at the following:

declare @SpaceUsed numeric(12, 4) = 567050.5000

, @FreeSpace numeric(12, 4) = 710.312500

select @FreeSpace / (@SpaceUsed + @FreeSpace) * 100

, @SpaceUsed / @SpaceUsed + @FreeSpace * 100

The second result is what you marked as your answer. This returns 71,032%. I don't think that is quite right. However, the first column returns .0125% which is correct.

Let's make the numbers really easy to do in our head for some rational testing.

set @SpaceUsed = 100

set @FreeSpace = 200