Week patterns

[#1]

Hi everyone,

I have seen several excelent tutorials made by the community dealing with calculating durations in working hours.

• I've seen the solution of using networkdays() including holidays
• some using interval(sum(end-start) where weekday(date<5) that offer including saturdays in the calculation.

Today i'd like to go a little bit further using week patterns which mean that each day can have different durations :

ServiceITCartography:

LOAD * INLINE [

Service,          WeekDay,    Start,          End

ServiceIT 1,         Mon.,          08:00          20:00

ServiceIT 1,         Tues.,         08:00          20:00

ServiceIT 1,         Wed.,         08:00          15:00

ServiceIT 1,         Thur.,         08:00          20:00

ServiceIT 1,         Fri.,           08:00          20:00

ServiceIT 1,         Sat.,          09:00          16:00

ServiceIT 1,         Sun.,         00:00          00:00

];

Incidents:

LOAD * INLINE [

IncidentID, Service, IncidentStart,         IncidentEnd

Incident1,  ServiceIT 1, 01/01/2013 08:12:21, 08/01/2013 21:47:02

];

Is there any way of applying this week pattern to calculate incidents durations ? 

There might be a solution using a case if on weekday but this sounds slow & dirty...

Thanks in advance !

[#2]

Anyone has ever faced this issue ?

[jjordaan]

I don't know if I understand your question correctly but do you want to count the number of incidents per day?

[#4]

Hi,

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 :

temp5j_8h_20h:

LOAD

          subIntervalUniqueID,

          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

Resident SubIntervals

while daystart(SubIntervalEnd+ $(#vEpsilon)) >= daystart(SubIntervalBegin)+iterno()-1;

left join (SubIntervals)

LOAD

          subIntervalUniqueID,

          interval(sum(End-Start)) as Durée_ouvrée_incident_5j_8h_20h

          Resident temp5j_8h_20h

WHERE WeekDay(Date)<5 and not match(Date,$(vJours_Fériers))

GROUP BY subIntervalUniqueID;

DROP TABLE temp5j_8h_20h;

NB :

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.

Any ideas ?

Thanks in advance