Solved

calculate availability using sql

Posted on 2012-03-17
9
527 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
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
 

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
poor performance from  MySQL stored procedure 6 23
TSQL previous 5 26
Replace Dates in query 14 21
Alter a column in sql 34 16
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now