Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

calculate availability using sql

Posted on 2012-03-17
9
Medium Priority
?
548 Views
Last Modified: 2012-06-27
Hi,
i just need to calculate the percentage that application is available during a given date.  I am basically calculating the scores and availability so i am already calculating the scores but would like to add the availability using this methodology:
i have column called Availability:

Availability
     1
     1
     1
     0
     0
     0
     1
     1
Note, 1 means application is available and 0 means it is not available.  So if i take the Avg of all these 8 entries, then my score will be about 62.5%.  So how would i calculate if i just need to know what was the percentage that the application was up during that day using sql?  thanks
0
Comment
Question by:karinos57
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 7

Expert Comment

by:markmiddlemist
ID: 37733721
If I'm understanding correctly then it should just be

Select 100 * Avg(Cast(Availability as Decimal)) from <Table>

The cast may not actually be needed, but try it both ways
0
 

Author Comment

by:karinos57
ID: 37733736
but this is what i am already doing in order to get the Scores.  Keep in mind 1 means it is available and 0 means it is not available.  In my case, i only need the available times as percentage.  In other words, there was 8 times that we checked if the application is up and we found that 3 of the eight times was down.  so i only need what is percentage was the up time?
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 2000 total points
ID: 37733895
Try:

SELECT   100.00 * SUM(Availability) / COUNT(Availability) = PctAvailable
FROM yourtable;
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:karinos57
ID: 37734035
but this is the same result as:
select Avg(Availability) from mytable
thanks but still not sure how this is done...
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 37735379
The way you have set up the probem, the % of the time that the app was up during the day is the same thing as the Average availability but multiplied times 100 (to turn it from a decimal fraction to a percentage).  If the average availability is 0.75, meaning that 18 of the 24 hours, the app was up (and, therefore, there were 18 "1's" and 6 "0's") then the percentageof time available would be 75%.  That is the definition of % available as well as the definition of the average.
0
 
LVL 9

Expert Comment

by:keyu
ID: 37736474
select (((select count(*) from tablename where Availability=1)*(100))/(select count(*) from tablename)) as mycalcpercentage
0
 

Author Comment

by:karinos57
ID: 37739881
thnaks guys, how could i round up to one decimal using this:
SELECT   100.00 * SUM(Availability) / COUNT(Availability) = PctAvailable
FROM yourtable;
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 37742161
Try the following:

SELECT   INT(((1000.00 * SUM(Availability)) + .5) / COUNT(Availability)) / 10 = PctAvailable
FROM yourtable;
0
 

Author Closing Comment

by:karinos57
ID: 37747510
tx
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question