How to Query Access for downtime on a 16 Hr Clock 2

[wsj81383]

I need to be able to report downtime on our machines using a 16 hour clock; Ie: from 6am to 10pm. I have the data for the open and close dates of the calls. For instance if a call was opened on 8/15/03 at 4 am and closed on 8/20/03 at 9 pm the only downtime counted against it would be for the hours each day between 6am and 10 pm. this is easy to calculate for a single day but I'm having trouble writing a query to cover multiple days. Any ideas?

 

[bkpchs237]

wsj81383,

I misread your question first, so I may be providing you with more than you need, but hopefully it'll help you on your way if my logic is straight. I was calculating total uptime when I noticed you wanted total downtime.

You have to approach this problem from three separate standpoints: 1) the total downtime on the first day based on the start time of the first day; 2) the total downtime on the last day based on the end time of the last day; plus 3) the total amount of days difference to begin with.

First, I created a table, called Table1, (sorry got lazy) with two fields in it called starttime and endtime, with data types of date/time. This table should be self-explanatory.

Then I created two queries with the following sql statements:
(the first called qryTimeSetup):
SELECT Table1.starttime, Table1.endtime, Int([endtime])-Int([starttime]) AS Days, [starttime]-Int([starttime]) AS Start, [endtime]-Int([endtime]) AS End, IIf([start]<=1/4,&quot;early&quot;,IIf([start]<11/12,&quot;middle&quot;,&quot;late&quot) AS StartCompare, IIf([end]<=1/4,&quot;early&quot;,IIf([end]<11/12,&quot;middle&quot;,&quot;late&quot) AS EndCompare
FROM Table1;

(the second called qryTimeFinals):
SELECT IIf([startcompare]=&quot;early&quot;,(1/4-[start]),IIf([startcompare]=&quot;middle&quot;,11/12,(1-[start]))) AS totalstart, IIf([endcompare]=&quot;early&quot;,[end],IIf([endcompare]=&quot;middle&quot;,1/12,[end]-11/12)) AS totalend, Format(IIf([days]=0,[endtime]-[starttime],[days]+[totalstart]+[totalend]),&quot;0.0000000&quot*1 AS totaluptime, IIf([startcompare]=&quot;early&quot;,2/3,IIf([startcompare]=&quot;middle&quot;,11/12-[start],0)) AS totaldowntimestart, IIf([endcompare]=&quot;early&quot;,0,IIf([endcompare]=&quot;middle&quot;,[end]-1/4,2/3)) AS totaldowntimeend, Format(IIf(([days]=0 And ([startcompare]=&quot;early&quot; And [endcompare]=&quot;early&quot) Or ([days]=0 And ([startcompare]=&quot;late&quot; And [endcompare]=&quot;late&quot),0,IIf([days]=0 And ([startcompare]=&quot;early&quot; And [endcompare]=&quot;middle&quot,[endtime]-(Int([endtime])+1/4),IIf([days]=0 And ([startcompare]=&quot;middle&quot; And [endcompare]=&quot;middle&quot,[endtime]-[starttime],IIf([days]=0 And ([startcompare]=&quot;middle&quot; And [endcompare]=&quot;late&quot,(Int([starttime])+11/12)-[starttime],IIf([days]=0 And ([startcompare]=&quot;early&quot; And [endcompare]=&quot;late&quot,2/3,[days]+[totalstart]+[totalend]))))),&quot;0.0000000&quot*1 AS totaldowntime
FROM qryTimeSetup;


The first query provides a total number of days, extracts the start and end times and performs a comparison on them to split them into the three categories: 1) early: before 6am; 2) middle: between 6am and 10pm; and 3) late: after 10 pm.

The second query then calculates the total times that items were up or down based on the possible scenarios of the first query (i.e. early/early, early/middle, early/late, middle/early, etc.) and spits out the total in number of days and the portion of days in decimal format. Hopefully you can convert the times thereafter.

I setup the database in this format just in case you decide in the future to change your &quot;clock&quot; to some other time system. You would edit the clock times by replacing the references to the start time of 1/4 (6:00 AM) and end time of 11/12 (10:00 PM) accordingly.

Hope this helps.

 

[TonyJollans]

Hi wsj81383,

This is an awkward calculation, and it depends on how you want the result formatting. The following, I believe, is strictly correct and SHOULD give the result in total hours and minutes BUT it doesn't always work because of minute differences between floating point and integer arithmetic.

It doesn't matter what 16 hours in a day your 'active' period is and if the period changes then the '16's need changing to the length of the new period. It does assume that the start and end times fall within the active period. It also assumes that the active period does not span midnight.

I supply it as an example only. DO NOT USE IT AS IS.

Int((([dtEnd]-[dtStart])*24)-((Int([dtEnd])-Int([dtStart]))*(24-16))) & Format((([dtEnd]-[dtStart])-((Int([dtEnd])-Int([dtStart]))*(24-16)/24)),":nn")

I will endeavour to tweak it to get round the problem and post back later.

Enjoy,
Tony

 

[wsj81383]

Thank you for your reply. Your logic is exactly what I was considering. Figuring out the query for the middle days was where I was stumped. I will try this and see what I can do. Calculating uptime is fine, Thanks for yor help.

 

[SkipVought]

I generalized this using
DayStart,
DayEnd and
HrsPerDay = DayEnd - DayStart

ElapsedHrs = (INT(End - Start) - 1) * HrsPerDay + _
 ( _
   (DayEnd - (Start - INT(Start))) + _
   (End - INT(End) - DayStart) _
 )*24

first line is elapsed whole days in hours
second line is time on the first day in days
third line is tim on last day in days
fourth line converts days to hours.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com