Hi,
I'll try to explain my problem thoroughly.
I am working with an application with customers that has an agreement for expected uptime on their system within their coretime (ex coretime 0800-1200).
I have one table containing customer information where there is information about this coretime. It is a different coretime mon-fri, sat and for sundays and holidays. I also have one table containing information about incidents that affect their systems uptime.
I want to find if an incident is within this coretime, what coretime (mon-fri, sat or sunday/holiday), and how many minutes is within coretime (incident between 1155-1205 where coretime ends at 1200 means only 5 minutes count on downtime).
Here are two example tables:
CustomerInfo:
LOAD * Inline
[CustID,CoreTimeStart,CoreTimeEnd,CoreTimeSaturdayStart,CoreTimeSaturdayEnd,CoreTimeHolidayStart,CoreTimeHolidayEnd
1, 0800, 1600, 0900, 1500, 1000, 1200
2, 0800, 1400, 0800, 1300, 1000, 1400
3, 0800, 1800, 1000, 1500, 0900, 1200
4, 0800, 1300, 0900, 1600, 1000, 1200
5, 0800, 1600, 0900, 1500, 1000, 1200];
Incidents:
LOAD * Inline
[IncID, CustID, IncStart, IncEnd, Desc
1, 3, 08/21/2011 17:53, 08/21/2011 18:15, Failure // Example friday
2, 2, 08/22/2011 12:13, 08/22/2011 14:15, Failure // Example saturday
3, 5, 08/23/2011 09:46, 08/23/2011 10:17, Failure]; // Example sunday
As you see here is that an incident can start or end outside the coretime in the customers agreement, but it is only the downtime that is inside that coretime that is to be counted as downtime. There are also, as you see, different coretimes for different customers on different days of week.
I really need som help with this, I'm really stuck.
