Calculating amount of time that falls into a specific range

[garyb2008]

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.

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

[ADezii]

@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?

[NeoPa]

Gary, a query with the following SQL embedded in it should help :

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 ;)

[NeoPa]

@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).

[ADezii]

@NeoPa
Gotcha! Thanks for the explanation.

[NeoPa]

No worries :)

It's rare enough that I even understand what an OP is asking for, so it's worth posting when I do ;)

[ADezii]

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).

[garyb2008]

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

[ADezii]

@garyb2008
Same here Gary, from all of us at Bytes!

[NeoPa]

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 :)