By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,896 Members | 2,002 Online
Bytes IT Community
+ Ask a Question
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
  1. Date,    DowntimeStart,  DowntimeEnd, SLAStart, SLAEnd
  2. 1/1/08,     15:00,          23:00,      18:00,   20:00.
  3. 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
Share this Question
Share on Google+
9 Replies


ADezii
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

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

NeoPa
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

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

NeoPa
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

ADezii
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

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

NeoPa
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

Post your reply

Sign in to post your reply or Sign up for a free account.