April 30, 2014 at 2:48 am
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.
April 30, 2014 at 3:16 am
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/
April 30, 2014 at 4:48 am
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 [/url]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
April 30, 2014 at 5:53 am
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.
April 30, 2014 at 6:05 am
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.
April 30, 2014 at 6:26 am
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 [/url]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
April 30, 2014 at 6:31 am
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.
April 30, 2014 at 6:39 am
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.
April 30, 2014 at 6:45 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply