Grizzle
asked on
A Challenge for all Code Masters
Greetings all,
I've got a little problem.
I'm building a database to generate monthly reports on Server availability and Disk Usage.
My problem is this, I need to calculate the Downtime for our Servers, and how much of the Downtime is during Working Hours (7:00am to 3:30pm). That's not so bad but when you figure in that the Server could be down for multiple days (encompasing multiple periods of Working Hours) it gets difficult.
Could one of you Code Geniuses figure out something for me??
The Table involved is called tblRestarts and contains the fields StartTime, FinishTime, StartDate, FinishDate and RestartID.
Any Help would be much appreciated.
Thanks
Grizzle
PS. I've thought about having one record for each day the Server is down but would prefer not to if possible...
I've got a little problem.
I'm building a database to generate monthly reports on Server availability and Disk Usage.
My problem is this, I need to calculate the Downtime for our Servers, and how much of the Downtime is during Working Hours (7:00am to 3:30pm). That's not so bad but when you figure in that the Server could be down for multiple days (encompasing multiple periods of Working Hours) it gets difficult.
Could one of you Code Geniuses figure out something for me??
The Table involved is called tblRestarts and contains the fields StartTime, FinishTime, StartDate, FinishDate and RestartID.
Any Help would be much appreciated.
Thanks
Grizzle
PS. I've thought about having one record for each day the Server is down but would prefer not to if possible...
ASKER
Believer,
That sort of thing sounds like it might have potential, I'd be interested to see how it all hangs together.
As for the comment about combining StartTime and StartDate etc, I've thought about it and decided that it would allow more flexibility if they were separate, but hell I'll listen to any arguments either way
Grizzle
That sort of thing sounds like it might have potential, I'd be interested to see how it all hangs together.
As for the comment about combining StartTime and StartDate etc, I've thought about it and decided that it would allow more flexibility if they were separate, but hell I'll listen to any arguments either way
Grizzle
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
I thought about this problem (night and day), and came to the conclusion that the only way you can do it, is to loop throug all the records, and determine the downtime (to save it into another table).
I could not find a "SQL method" to determine this at one throw, and usually i do find solutions in SQL
Sorry
I could not find a "SQL method" to determine this at one throw, and usually i do find solutions in SQL
Sorry
Hi there. Try this. This will return a string formatted difference:
Call it with:
workinghours("15:00","05/0
....and you get...
0 days, 2 hours, 30 minutes
Sorry there is no validation on the dates as I have just knowed this up.
Public Function WorkingHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As String
' constants that indicate the working day
Const WORKING_DAY_START = "07:00"
Const WORKING_DAY_END = "15:30"
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
intMinutes = 0
intHours = 0
intDays = 0
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
intDays = intDays + 1
End If
Next
intDays = intDays - 1
' work out the number of hours, bearing in mind we alreayd have the number of days
If StartDate <> EndDate Then ' we spread over days
' workout hours on first day
intHours = intHours + DateDiff("h", StartTime, WORKING_DAY_END)
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) Mod 60
' workout hours on last day
intHours = intHours + DateDiff("h", WORKING_DAY_START, EndTime)
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) Mod 60
' any days in between?
If intDays > 2 Then
intHours = intHours + ((intDays - 1) * DateDiff("h", WORKING_DAY_START, WORKING_DAY_END))
intMinutes = intMinutes + ((intDays - 1) * (DateDiff("n", WORKING_DAY_START, WORKING_DAY_END) Mod 60))
End If
Else ' we are on the same day
intHours = DateDiff("h", StartTime, EndTime)
intMinutes = DateDiff("n", StartTime, EndTime) Mod 60
If intMinutes > 0 And intHours > 1 Then
intHours = intHours - 1
End If
End If
' finally, tidy up the result
If intHours >= DateDiff("h", WORKING_DAY_START, WORKING_DAY_END) Then ' add to the days
intDays = intDays + (intHours / DateDiff("h", WORKING_DAY_START, WORKING_DAY_END))
intHours = intHours Mod DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
End If
If intMinutes >= 60 Then ' add to the days
intHours = intHours + (intMinutes / 60)
intMinutes = intMinutes Mod 60
End If
WorkingHours = CStr(intDays) & " days, " & CStr(intHours) & " hours, " & CStr(intMinutes) & " minutes"
End Function
Good Luck
Simon
Call it with:
workinghours("15:00","05/0
....and you get...
0 days, 2 hours, 30 minutes
Sorry there is no validation on the dates as I have just knowed this up.
Public Function WorkingHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As String
' constants that indicate the working day
Const WORKING_DAY_START = "07:00"
Const WORKING_DAY_END = "15:30"
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
intMinutes = 0
intHours = 0
intDays = 0
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
intDays = intDays + 1
End If
Next
intDays = intDays - 1
' work out the number of hours, bearing in mind we alreayd have the number of days
If StartDate <> EndDate Then ' we spread over days
' workout hours on first day
intHours = intHours + DateDiff("h", StartTime, WORKING_DAY_END)
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) Mod 60
' workout hours on last day
intHours = intHours + DateDiff("h", WORKING_DAY_START, EndTime)
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) Mod 60
' any days in between?
If intDays > 2 Then
intHours = intHours + ((intDays - 1) * DateDiff("h", WORKING_DAY_START, WORKING_DAY_END))
intMinutes = intMinutes + ((intDays - 1) * (DateDiff("n", WORKING_DAY_START, WORKING_DAY_END) Mod 60))
End If
Else ' we are on the same day
intHours = DateDiff("h", StartTime, EndTime)
intMinutes = DateDiff("n", StartTime, EndTime) Mod 60
If intMinutes > 0 And intHours > 1 Then
intHours = intHours - 1
End If
End If
' finally, tidy up the result
If intHours >= DateDiff("h", WORKING_DAY_START, WORKING_DAY_END) Then ' add to the days
intDays = intDays + (intHours / DateDiff("h", WORKING_DAY_START, WORKING_DAY_END))
intHours = intHours Mod DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
End If
If intMinutes >= 60 Then ' add to the days
intHours = intHours + (intMinutes / 60)
intMinutes = intMinutes Mod 60
End If
WorkingHours = CStr(intDays) & " days, " & CStr(intHours) & " hours, " & CStr(intMinutes) & " minutes"
End Function
Good Luck
Simon
Whoops - Optimised version - works the same, just a bit easier to read..
Public Function WorkingHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As Long
' constants that indicate the working day
Const WORKING_DAY_START = "07:00"
Const WORKING_DAY_END = "15:30"
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim HOURS_IN_DAY As Integer
Dim MINUTES_IN_DAY As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
intMinutes = 0
intHours = 0
intDays = 0
HOURS_IN_DAY = DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
MINUTES_IN_DAY = DateDiff("m", WORKING_DAY_START, WORKING_DAY_END)
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
intDays = intDays + 1
End If
Next
intDays = intDays - 1
' work out the number of hours, bearing in mind we alreayd have the number of days
If StartDate <> EndDate Then ' we spread over days
' workout hours on first day
intHours = intHours + DateDiff("h", StartTime, WORKING_DAY_END)
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) Mod 60
' workout hours on last day
intHours = intHours + DateDiff("h", WORKING_DAY_START, EndTime)
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) Mod 60
' any days in between?
If intDays > 2 Then
intHours = intHours + ((intDays - 1) * HOURS_IN_DAY)
intMinutes = intMinutes + ((intDays - 1) * (MINUTES_IN_DAY Mod 60))
End If
Else ' we are on the same day
intHours = DateDiff("h", StartTime, EndTime)
intMinutes = DateDiff("n", StartTime, EndTime) Mod 60
If intMinutes > 0 And intHours > 1 Then
intHours = intHours - 1
End If
End If
' finally, tidy up the result
If intHours >= HOURS_IN_DAY Then ' add to the days
intDays = intDays + (intHours / HOURS_IN_DAY)
intHours = intHours Mod HOURS_IN_DAY
End If
If intMinutes >= 60 Then ' add to the days
intHours = intHours + (intMinutes / 60)
intMinutes = intMinutes Mod 60
End If
WorkingHours = CStr(intDays) & " days, " & CStr(intHours) & " hours, " & CStr(intMinutes) & " minutes"
End Function
Public Function WorkingHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As Long
' constants that indicate the working day
Const WORKING_DAY_START = "07:00"
Const WORKING_DAY_END = "15:30"
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim HOURS_IN_DAY As Integer
Dim MINUTES_IN_DAY As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
intMinutes = 0
intHours = 0
intDays = 0
HOURS_IN_DAY = DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
MINUTES_IN_DAY = DateDiff("m", WORKING_DAY_START, WORKING_DAY_END)
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
intDays = intDays + 1
End If
Next
intDays = intDays - 1
' work out the number of hours, bearing in mind we alreayd have the number of days
If StartDate <> EndDate Then ' we spread over days
' workout hours on first day
intHours = intHours + DateDiff("h", StartTime, WORKING_DAY_END)
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) Mod 60
' workout hours on last day
intHours = intHours + DateDiff("h", WORKING_DAY_START, EndTime)
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) Mod 60
' any days in between?
If intDays > 2 Then
intHours = intHours + ((intDays - 1) * HOURS_IN_DAY)
intMinutes = intMinutes + ((intDays - 1) * (MINUTES_IN_DAY Mod 60))
End If
Else ' we are on the same day
intHours = DateDiff("h", StartTime, EndTime)
intMinutes = DateDiff("n", StartTime, EndTime) Mod 60
If intMinutes > 0 And intHours > 1 Then
intHours = intHours - 1
End If
End If
' finally, tidy up the result
If intHours >= HOURS_IN_DAY Then ' add to the days
intDays = intDays + (intHours / HOURS_IN_DAY)
intHours = intHours Mod HOURS_IN_DAY
End If
If intMinutes >= 60 Then ' add to the days
intHours = intHours + (intMinutes / 60)
intMinutes = intMinutes Mod 60
End If
WorkingHours = CStr(intDays) & " days, " & CStr(intHours) & " hours, " & CStr(intMinutes) & " minutes"
End Function
And finally, just in case you want the answer back in minutes (for further manipulation) change the return type to Long, and change the final line to:
WorkingHours = (intDays * HOURS_IN_DAY) + (intHours * 60) + intMinutes
HTH <phew>
Simon
WorkingHours = (intDays * HOURS_IN_DAY) + (intHours * 60) + intMinutes
HTH <phew>
Simon
Actually make that:
WorkingHours = (intDays * MINUTES_IN_DAY) + (intHours * 60) + intMinutes
WorkingHours = (intDays * MINUTES_IN_DAY) + (intHours * 60) + intMinutes
Just a little thought:
Are all your servers within the same time-zone?
Otherwise you'll first have to recalculate the servertime from (lets say GMT) to the real working day hours.
Are all your servers within the same time-zone?
Otherwise you'll first have to recalculate the servertime from (lets say GMT) to the real working day hours.
ASKER
Yes Nico,
I thought about that, but luckily all our servers are in the same time zone.
Thanks to SimonBennett, I'm testing your code at the moment and I'll let you know if I need any modifications (I'm not very confidant with VBA coding)
Thanks
Grizzle
I thought about that, but luckily all our servers are in the same time zone.
Thanks to SimonBennett, I'm testing your code at the moment and I'll let you know if I need any modifications (I'm not very confidant with VBA coding)
Thanks
Grizzle
Grizzle: I *finally* got time to extract the functionality of my Elapsed Work Hours out of a project and into something I can e-mail. But I don't have your e-mail address! :(
ASKER
Believer,
My e-mail address is croberts@csenergy.com.au
My e-mail address is croberts@csenergy.com.au
Thanks... I'm on my way out the door so I'll send it to you first thing tomorrow. I'd like to spend some time telling you how it works instead of just throwing it at you!
BTW: First thing tomorrow may not be too early... I'm going to see Queensryche and Jessa James Dupree (of Jackal) at a local club tonite... WOOHOO!!
BTW: First thing tomorrow may not be too early... I'm going to see Queensryche and Jessa James Dupree (of Jackal) at a local club tonite... WOOHOO!!
ASKER
Simon,
The second batch of code you posted generated a Type Mismatch error that I couldn't trace so I've tested your 1st code using the output in minutes you added, and the results were quite strange. Could you test it further for me.
Thanks
Grizzle
PS some of the data I tested it with was
1.7:00-15:00 on the 21/03/00
2.12:00-16:30 Starting on the 21/0300 and finishing on the 22/03/00
The second batch of code you posted generated a Type Mismatch error that I couldn't trace so I've tested your 1st code using the output in minutes you added, and the results were quite strange. Could you test it further for me.
Thanks
Grizzle
PS some of the data I tested it with was
1.7:00-15:00 on the 21/03/00
2.12:00-16:30 Starting on the 21/0300 and finishing on the 22/03/00
Grizzle -
Have you applied all of the changes I have posted above (service packs!?)
If so then try latest version at
https://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10314664
It has been refined a little.
Cheers
Simon
Have you applied all of the changes I have posted above (service packs!?)
If so then try latest version at
https://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10314664
It has been refined a little.
Cheers
Simon
.as for the type mismatch, change the declaration to:
Public Function WorkingHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As string
hth
Simon
Public Function WorkingHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As string
hth
Simon
I'll answer this one....just for attention mind....
Fnar Fnar
¿Fnar Fnar?
With a thread that's moving along, why do you want to "answer this one....just for attention mind...." ?
With a thread that's moving along, why do you want to "answer this one....just for attention mind...." ?
ASKER
Simon,
I'd be happy to grant you the points, except the code doesn't work properly.
I've been testing it to see what was going on, any Start Time before 7:00, results in a bad result, and anything over a day also produces incorrect results.
Can you please post a copy of the Complete code, with all your "Service Packs" so that the result will be in minutes, just in case I'm interpreting your comments incorectly.
Also, Believer, I can't even consider you for an answer if I don't see some code soon.
Thanks
Grizzle
I'd be happy to grant you the points, except the code doesn't work properly.
I've been testing it to see what was going on, any Start Time before 7:00, results in a bad result, and anything over a day also produces incorrect results.
Can you please post a copy of the Complete code, with all your "Service Packs" so that the result will be in minutes, just in case I'm interpreting your comments incorectly.
Also, Believer, I can't even consider you for an answer if I don't see some code soon.
Thanks
Grizzle
(ouch)
I'll git rite on it.. sorree!!!
I'll git rite on it.. sorree!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Whoops sorry guys!
Grizzle - It won't work before 7:00 am because the function calculated Hours between 7:00 and 15:30.
Sorry for being a pain.
Simon
Grizzle - It won't work before 7:00 am because the function calculated Hours between 7:00 and 15:30.
Sorry for being a pain.
Simon
ASKER
Thanks For the Code Believer,
Took me a while but I've adapted it to my little job and it works well.
Thanks All
Grizzle
Took me a while but I've adapted it to my little job and it works well.
Thanks All
Grizzle
Grizzle: Glad it works! Sorry for the poor response time :(
Just so you guys aren't left out of the loop, does anybody want:
1. A rundown on how the solution works, and/or
2. A copy of what I sent Grizzle (leave me your e-mail)?
Just so you guys aren't left out of the loop, does anybody want:
1. A rundown on how the solution works, and/or
2. A copy of what I sent Grizzle (leave me your e-mail)?
(How can I keep this short & sweet...?)
It tracks work orders (maintenance requests). There are a lot of date/time stamps in it (call logged, request printed, request performed, request updated in system...) to track response times. One big issue that came up was that elapsed time should only reflect elapsed *work* time (not nights & weekends). I came up with a table (calendar) for designating work days & hours. I also have a routine that, given the start & end times, will return the elapsed *work* time.
Does this sound like something you can use?
By the way... IMHO you may want to consider keeping your "StartDate" and "StartTime" and your "FinishDate" and "FinishTime" in two date/time fields total instead of four fields.
Talk to you tomorrow!