Solved

# How can i calculate % avail in a query...

Posted on 2005-05-06
201 Views
i have a query which returns 3 columns

capacity, used, avail

i would like to add a 4th one which calculates the % avail so the return should look like this

100   10  90    90%
100   18  92    92%

and so on, how can i do this...
0
Question by:AlexPonnath

LVL 75

Expert Comment

How about concatenating a % to the available?
0

Author Comment

how would i do that ???
0

LVL 75

Expert Comment

Select capacity, used, avail, CAST(avail as varchar(20)) + '%'
From TableName
0

Author Comment

now i get it put a % behind the avil...

that does not work since i just used a example which was simple as far as nbr goes

lets say if the data looks like this it wont work...

1000   10  990     99 %
500      5  495     99 %
0

LVL 75

Expert Comment

Select capacity, used, avail, avail * 100 / capacity
from TableName
0

LVL 75

Expert Comment

This may be more accurate:

Select capacity, used, avail, CAST(CAST(avail As decimal(18, 4)) * 100 / capacity As integer)
from @Temp
0

LVL 75

Expert Comment

This:
from @Temp

from TableName
0

Author Comment

It works somewat the problem i have is with the acuracy of the calculation.

look at this data i have

1000  as capacity
987 as avail
the query returns 98%

but if i do this via calculator i get 98.7..

how can i fix that ?
0

LVL 75

Expert Comment

How many decimal places do you need?
0

Author Comment

2 will do..

here is the code i use for the percent field in my query

cast(cast(sum(case when t2.status = 'Open' then 1 else 0 end)As decimal(18, 4)) * 100 / 1000 As integer)  as [Percent],
0

LVL 75

Expert Comment

Change this:
Select capacity, used, avail, CAST(CAST(avail As decimal(18, 4)) * 100 / capacity As integer)

To:
Select capacity, used, avail, CAST(CAST(avail As decimal(18, 2)) * 100 / capacity As decimal(18, 2))
0

LVL 75

Accepted Solution

Change this:
cast(cast(sum(case when t2.status = 'Open' then 1 else 0 end)As decimal(18, 4)) * 100 / 1000 As integer)  as [Percent],

To:
cast(cast(sum(case when t2.status = 'Open' then 1 else 0 end)As decimal(18, 4)) * 100 / 1000 As decimal(18, 2))  as [Percent],
0

Author Comment

that did the job...
0

## Featured Post

### Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.