percentage average problem in Access

[Maven4Champ]

Percent_Total Down Time: [Total Down Time]/1440


Sum Of Total Down Time: Sum([SiebelOutageQuery].[Total Down Time])


>=#01/01/2004# And <=#08/02/2004#


I have a somewhat complicated somewhat simple question...

I am running a database that contains server uptime, server downtime and totaldowntime, etc.

I already have one query that creates the minute total of downtime (by figuring from the start of the downtime to the start of the uptime - that gives me my total number of time the server was down).

Now, what I want to do is create another query off of that query that can give a TotalDownTime percentage based off of the total minutes the server was down. Now, figuring that it goes by minutes, I created this:

Percent_Total Down Time: [Total Down Time]/1440

- This essentially divides Total Down Time (which is in minutes already) by 1440 (how many minutes are in a day) to get a percentage value of how long it was down for that day. Now, I also have a second field (Date) which I have basically defined with the following criteria: >=#01/01/2004# And <=#08/02/2004# (essentially giving me a YTD figure percentage).

here is where my problem is at. I can create the above query, but now I obviously need just 1 final percentage which equals my YTD Total Down Time. I had did the above expression, and in the Totals field, did AVG for the Total Down Time and then AVG for Date, to get one single value - but it doesn't appear to be correct.

Is there a better way to get the final percentage total in this query?

 

[Maven4Champ]

Is my question too confusing or ?

 

[Maven4Champ]

I will be in a report...

the problem is, how do I actually figure it correctly?

I have tried different things and it always comes up with a different percentage and I can't tell what is right or wrong?

See what it needs to reflect is this:

Say we are reporting the avg percentage of downtime in the month of May from operating hours of 7am to 7pm when the system is actually being used?

31 days
12 hours a day
it's down the follow times:

22 minutes - May 3rd
1 hr 53 minutes - May 17th
6 minutes - May 24th
34 minutes May 29th

I know how to break down each individual day - I would divide by 720 which is how many minutes are in an actual working day (7am to 7pm) right?

I have gotten that far and I have percentage values for the above right? Well I can't seem to find a way to get an actual sum and/or average of those to say "For the month of May, the system has been down xx.xx% during operating hours of 7am to 7pm."

See what I mean? And another thought comes to mind, even if I got an average of those percentages - would it still be an accurate reflection for the whole month? It's is really only an average percentage of down time, but not average percentage of downtime vs. the entire month...

See what I mean?

 

[Andrew Fergus]

I would have thought the average downtime for the month of May (per your example) is the sum of the downtime minutes (175 minutes) divided by the sum of the total operating hours (31 x 720 = 22,320 minutes) - which gives you a (weighted) average downtime for the month of May of 0.78%. The average uptime was therefore 99.22%. This method can be used for days, weeks, month, quarters and years - any period you want.

You have already worked out the total downtime per a previous post. The total operating minutes = a count of the days from the first day you wish to measure to the last day you wish to measure (end - start + 1) x the average operating minutes per day (assuming it is always 720).

The weighted average gives equal weighting to each operating minute - if the servers were up all day then there was no downtime, so the downtime was 0.00% for those days. IMHO it would be incorrect to exclude the days that had no downtime.

However, if your managers (or you) are looking for the average downtime for the days that the server was actually down (as opposed to every day), then that would be the sum of the downtime minutes (175) divided by the operating minutes on the days there was a downtime (count of downtime days x average operating minutes per day = 4 x 720 = 2,880) - the average downtime for the days when the server was actually down during the month of May is therefore 6.08%.

IMHO this last method is not correct and should not be used - unless it is asked for.

Hope this helps, Andrew.