434,896 Members | 2,002 Online
Need help? Post your question and get tips & solutions from a community of 434,896 IT Pros & Developers. It's quick & easy.

# Calculating amount of time that falls into a specific range

 P: 18 Hello Can someone help me with this one please! Im working on a service level tracking database which records the times that a service goes down during the day (DowntimeStart/DowntimeEnd) and compares this to the times that it is required to be available (SLAStart/SLAEnd). I want to return the amount of time the service was down during the SLA period in minutes. Expand|Select|Wrap|Line Numbers Date,    DowntimeStart,  DowntimeEnd, SLAStart, SLAEnd 1/1/08,     15:00,          23:00,      18:00,   20:00. 2/1/08,     15:00,          16:00,      15:30,   20:00. The result for record 1 above should be 120 minutes. The result for record 2 above should be 30 minutes. Hope you can help.... Regards Gary Dec 21 '08 #1
9 Replies

 Expert 5K+ P: 8,638 @garyb2008 Gary, please clarify something for me before we proceed. In Record #1, the Down Time Start (DowntimeStart) was 3 hours prior to the Available Time Start (SLAStart), namely 15:00 ==> 18:00. The Down Time End (DowntimeEnd) also extended 3 hours passed the allowable Available Time End (SLAEnd), namely 23:00 ==> 20:00. Should not the result for Record 1 be 360 minutes? Dec 21 '08 #2

 Expert Mod 15k+ P: 31,491 Gary, a query with the following SQL embedded in it should help : Expand|Select|Wrap|Line Numbers SELECT [Date],        IIf([DowntimeEnd]<[SLAEnd],[DowntimeEnd],[SLAEnd])-        IIf([DowntimeStart]>[SLAStart],[DowntimeStart],[SLAStart]) AS SLADownTime FROM ... I have no table information supplied so you'll have to fill that info in for yourself ;) Dec 21 '08 #3

 Expert Mod 15k+ P: 31,491 @ADezii Downtime Start / End specifies the time the system was actually down. SLA Start / End specifies the time we actually care about the system being down. Although the system was down for 8 hours (480 mins = 23:00 - 15:00), we only care that it was down all the time (2 hours) between 18:00 to 20:00 (120 mins). Dec 21 '08 #4

 Expert 5K+ P: 8,638 @NeoPa Gotcha! Thanks for the explanation. Dec 22 '08 #5

 Expert Mod 15k+ P: 31,491 No worries :) It's rare enough that I even understand what an OP is asking for, so it's worth posting when I do ;) Dec 22 '08 #6

 Expert 5K+ P: 8,638 Here is a rather weird solution that I came up with, probably not even Post-Worthy, but I'll throw it up here anyway, just in case no other alternative works! (LOL). Dec 22 '08 #7

 P: 18 Hi Guys Thanks for looking into this while i was away, was havin a time out from the project over christmas. I think ill go with NeoPa's solution for this, but im keeping your function ADezii's as with a few modifications it may solve another requirement i know will also be needed. Happy new year all Gary Regards Gary Dec 30 '08 #8

 Expert 5K+ P: 8,638 @garyb2008 Same here Gary, from all of us at Bytes! Dec 30 '08 #9

 Expert Mod 15k+ P: 31,491 I think a lot of us had a time-out over Christnmas Gary :) I'm glad this is clearer now. Have a very Happy New Year :) Jan 5 '09 #10