I have as an input incidents (intervals). Those incidents can happen at any time.
I want to measure the real impact of those incidents on offices that have various opening and closing time.
This means that if the incident happen during the night, the office is closed and the incident have no impact on business. On the other hand, if an incident starts at 7AM and ends at 1PM, if the office opens at 9AM, the incident impact for the business is 4:00.
I'm today able to calculate this impact if every day of a standard week have the same opening and closing time by using :
daystart(SubIntervalBegin)+iterno()-1 as Date,
if(iterno()=1, rangemin(rangemax(frac(SubIntervalBegin),maketime($(vOuverture_Bureaux))),maketime($(vFermeture_Bureaux))), maketime($(vOuverture_Bureaux))) as Start,
if(daystart(SubIntervalBegin)+iterno()-1=daystart(SubIntervalEnd+ $(#vEpsilon)), rangemax(maketime($(vOuverture_Bureaux)),rangemin(frac(SubIntervalEnd+ $(#vEpsilon)),maketime($(vFermeture_Bureaux)))),Maketime($(vFermeture_Bureaux))) as End
while daystart(SubIntervalEnd+ $(#vEpsilon)) >= daystart(SubIntervalBegin)+iterno()-1;
left join (SubIntervals)
interval(sum(End-Start)) as Durée_ouvrée_incident_5j_8h_20h
WHERE WeekDay(Date)<5 and not match(Date,$(vJours_Fériers))
GROUP BY subIntervalUniqueID;
DROP TABLE temp5j_8h_20h;
vOuverture_Bureaux = office open hour
vFermeture_Bureaux = office closing hour
My need today is to extend this code to generate durations based on various opening days.
To illustrate that, this is my standard week :
and this is the impact of a incident starting at 06:00 a monday and ending at 19:00 a wednesday :
In this example the business was impacted 31 hours for a 61 hours incident.