Solved

calculate availability using sql

Posted on 2012-03-17
9
538 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 500 total points
ID: 37733895
Try:

SELECT   100.00 * SUM(Availability) / COUNT(Availability) = PctAvailable
FROM yourtable;
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 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