• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

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

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
AlexPonnath
Asked:
AlexPonnath
  • 8
  • 5
1 Solution
 
Anthony PerkinsCommented:
How about concatenating a % to the available?
0
 
AlexPonnathAuthor Commented:
how would i do that ???
0
 
Anthony PerkinsCommented:
Select capacity, used, avail, CAST(avail as varchar(20)) + '%'
From TableName
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
AlexPonnathAuthor Commented:
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
 
Anthony PerkinsCommented:
Select capacity, used, avail, avail * 100 / capacity
from TableName
0
 
Anthony PerkinsCommented:
This may be more accurate:

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

Should read:
from TableName
0
 
AlexPonnathAuthor Commented:
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
 
Anthony PerkinsCommented:
How many decimal places do you need?
0
 
AlexPonnathAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
 
AlexPonnathAuthor Commented:
that did the job...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now