Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rounding off a percentage result Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 2:48 AM This worked for the OP Answer marked as solution
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 3:20 AM
Points: 18, Visits: 140
Hi,

I've a small issue working out how to remove the trailing chars from the end of a percentage calculation.

For instance:

SELECT
CAST(NULLIF(t.SLAHours,0) - t.TotalDownTime as REAL) /
CAST(NULLIF(t.SLAHours,0) as REAL) * 100 as [%Availability]
FROM DBName.dbo.TableName t


This gets me '99.00294' (example)

I may have gone about this totally the wrong way - but I just want to trim the unwanted chars from the end - I've managed to trim some off by using REAL but I only need the basic percentage number.

Both source columns are INT data type

Any suggestions or pointers would be great.

Thanks in Advance.
Post #1566268
Posted Wednesday, April 30, 2014 3:16 AM This worked for the OP Answer marked as solution
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:20 AM
Points: 2,107, Visits: 5,405
If all you need is to round the number to an integer number or to omit the part that is right to the decimal point, then you can use the round function or cast the results as integer. If you need something else, pleas explain.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1566276
Posted Wednesday, April 30, 2014 4:48 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 3,033, Visits: 3,233
How about
SELECT  CONVERT(DECIMAL(4, 2), CAST(NULLIF(t.SLAHours, 0) - t.TotalDownTime AS REAL)
/ CAST(NULLIF(t.SLAHours, 0) AS REAL) * 100) AS [%Availability]
FROM DBName.dbo.TableName t



-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1566305
Posted Wednesday, April 30, 2014 5:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 3:20 AM
Points: 18, Visits: 140
Stuart Davies (4/30/2014)
How about
SELECT  CONVERT(DECIMAL(4, 2), CAST(NULLIF(t.SLAHours, 0) - t.TotalDownTime AS REAL)
/ CAST(NULLIF(t.SLAHours, 0) AS REAL) * 100) AS [%Availability]
FROM DBName.dbo.TableName t



I get the following error:

Msg 8115, Level 16, State 6
Arithmetic Overflow error converting real to data type numeric.

Thanks again for your replies and advance thanks for any further suggestions.

Post #1566331
Posted Wednesday, April 30, 2014 6:05 AM This worked for the OP Answer marked as solution
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 4,171, Visits: 3,201
DonJunville (4/30/2014)
Stuart Davies (4/30/2014)
How about
SELECT  CONVERT(DECIMAL(4, 2), CAST(NULLIF(t.SLAHours, 0) - t.TotalDownTime AS REAL)
/ CAST(NULLIF(t.SLAHours, 0) AS REAL) * 100) AS [%Availability]
FROM DBName.dbo.TableName t



I get the following error:

Msg 8115, Level 16, State 6
Arithmetic Overflow error converting real to data type numeric.

Thanks again for your replies and advance thanks for any further suggestions.


Have you tried increasing the size of your decimal data type? You need to allow enough space for the values in your table.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1566339
Posted Wednesday, April 30, 2014 6:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 3,033, Visits: 3,233
Re-reading you original post, a couple of questions for you.
What are the SLAHours and TotalDownTime values stored as (integer, decimal etc)? and what result are you expecting - 99.00294, 99.00,99 - or something else?

Are you storing these values somewhere or returning to a client?


-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1566348
Posted Wednesday, April 30, 2014 6:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 3:20 AM
Points: 18, Visits: 140
Sorted.

@ Stuart
They are INT values and they are being returned at present via a view.


Thanks for the responses guys. I increased the decimal values from (4,2) to (5,2) and now I have the results I'm after.


You've all been very helpful.

Cheers again.

Post #1566352
Posted Wednesday, April 30, 2014 6:39 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 4,171, Visits: 3,201
Stuart: You hit the nail on the head. I shouldn't have assumed that they were integers, but I did.

DonJunville: You'll be fine until you create a row that 200,000 hours. I know that may sound like an unreasonable value, but your design should allow for the maximum value that the column will support so you don't run into problems later. It'll save you (or someone else) hours of searching to find the problem when it pops up two years in the future.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1566354
Posted Wednesday, April 30, 2014 6:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 3:20 AM
Points: 18, Visits: 140
Ed Wagner (4/30/2014)
DonJunville: You'll be fine until you create a row that 200,000 hours. I know that may sound like an unreasonable value, but your design should allow for the maximum value that the column will support so you don't run into problems later. It'll save you (or someone else) hours of searching to find the problem when it pops up two years in the future.


Duly noted :)

Thanks again to all.
Post #1566358
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse