Calculating an end date to be within business hours

[Anonymous]

Hi

The project that i am working on is on IT Calls that are logged.  Depending on their priority, they need to be completed during business hours. (see below)

Priority	Business Hrs to Resolve
3	12 hrs
4	24 hrs
5	40 hrs

I need to calculate the End Date & Time of the Call which needs to be within business hours.    My business hours is as follows:

Monday to Friday from 8:00  to 17:00 .  Weekends and Public holidays are excluded.  Below is a list of public holidays.

01-Jan

21-Mar

27-Apr

01-May

16-Jun

09-Aug

24-Sep

16-Dec

25-Dec

26-Dec

I have attached a sample list of Calls logged, its priority and business hours for Call to be closed.  

Please can you assist. 

Please note a call can be logged out of business hours (ie on weekends and after 17:00, however they only allowed to be solved within business hours)

Kind regards

Nayan

[pokassov]

Hello!

It's an interesting task. I will try to find out how to do this.

[Anonymous]

Thank you Sergey, I appreciate it.

Kind regards

Nayan

[MK_QSL]

I have saved holidays in an Excel file. So you can do the same.

CallData saved in Excel File named Business Hours to Resolve Call.xls and SheetName is CallDate.

Holidays saved in same excel file wiht SheetName Holidays.

x

Let vStartTime = Time#('08:00:00','hh:mm');
Let vEndTime = Time#('17:00:00','hh:mm');
Let vTotalWorkingHours = Interval('$(vEndTime)' - '$(vStartTime)')*24;


CallData:
LOAD Call,
     Priority,
     [Created Date] as CreatedDateTime,
     [Business Hours to Resolve Call],
     Num(KeepChar([Business Hours to Resolve Call],'0123456789'),'#0') as ResolveBusinessHr
FROM
[Business Hour End Date.xls]
(biff, embedded labels, table is CallDate$);


Map_Holidays:
Mapping LOAD
  Holidays,
  1 as Flag
FROM
[Business Hour End Date.xls]
(biff, embedded labels, table is Holidays$);


Temp:
Load
  *,
  IF(TempAfterEndTime = Time('00:00:00'),TempResolveDateTime,TimeStamp(TempResolveDate + 1 + '$(vStartTime)' + TempAfterEndTime)) as TempAfterEndTime2
;
Load
  *,
  Time(RangeMax(0,TempResolveTime - '$(vEndTime)')) as TempAfterEndTime
;
Load
  *,
  Date(Floor(TempResolveDateTime)) as TempResolveDate,
  Time(Frac(TempResolveDateTime)) as TempResolveTime
;
Load
  *,
  TimeStamp(OfficialCreatedDateTime + Interval(NoOfDay,'D') + NoOfHours) as TempResolveDateTime
;
Load
  *,
  TimeStamp(CallDate + OfficialCallTime) as OfficialCreatedDateTime
;
Load
  *,
  Floor(ResolveBusinessHr/$(vTotalWorkingHours)) as NoOfDay,
  Time(Interval((ResolveBusinessHr-Floor(ResolveBusinessHr/$(vTotalWorkingHours))*$(vTotalWorkingHours)),'hh')/24) as NoOfHours,
  WeekDay(CallDate) as CallDay,
  Time(RangeMin(RangeMax(CallTime,'$(vStartTime)'),'$(vEndTime)')) as OfficialCallTime
;
Load
  *,
  Date(Floor(CreatedDateTime)) as CallDate,
  Time(Frac(CreatedDateTime)) as CallTime
Resident CallData;


Drop Table CallData;


Temp2:
Load
  Call as Call,
  OfficialCreatedDateTime as NewCreatedDateTime,
  Date(Floor(OfficialCreatedDateTime)) as NewCreatedDate,
  Time(Frac(OfficialCreatedDateTime)) as NewCreatedTime,
  TempAfterEndTime2 as NewResolveDateTime,
  Date(Floor(TempAfterEndTime2)) as NewResolveDate,
  Time(Frac(TempAfterEndTime2)) as NewResolveTime
Resident Temp;


Left Join(Temp2)
Load
  Call,
  SUM(HolidayFlag) as TotalHoliday
Group By Call
;
Load
  *,
  If(Match(WeekDay,'Sat','Sun'),1,ApplyMap('Map_Holidays',StartDate,0)) as HolidayFlag
;
Load
  *,
  WeekDay(StartDate) as WeekDay
;
Load
  Call,
  Date(NewCreatedDate + IterNo() - 1) as StartDate
Resident Temp2
While NewCreatedDate + IterNo() - 1 <= NewResolveDate;


Left Join (Temp)
Load
  Call,
  NewCreatedDateTime,
  NewResolveDateTime,
  TimeStamp(NewResolveDateTime + Interval(TotalHoliday,'D')) as NewResolveDatTime2
Resident Temp2;


Drop Table Temp2;


Drop Fields ResolveBusinessHr, CallTime,CallDay, OfficialCallTime,OfficialCreatedDateTime;
Drop Fields TempResolveDateTime,TempResolveDate,TempResolveTime,TempAfterEndTime;
Drop Fields TempAfterEndTime2, CallDate, NewCreatedDateTime,NewResolveDateTime, NoOfDay, NoOfHours;
Rename Field NewResolveDatTime2 to TargetResolveDateTime;

[Anonymous]

Thank you Manish for your relpy.  Will try it out and let you know.

Kind regards

Nayan

[ahaahaaha]

Hi,

as variant in attached file. The accuracy of the ending date was made up to an hour.

Regards,

Andrey

[Anonymous]

Hi Manish

Thank you for your reply, It workds.  However I have picked up that there are some TargetResolveDateTime falling on  a Sunday.  Attached herewith is the mode and data as well as table showing Calls where the TargetResolveDateTime falling on a Sunday.

Kind regards

Nayan

[Anonymous]

Thank you Audrey for your reply.  Will have a look and let you know.

Kind regards

Nayan

[MK_QSL]

I will work on this and come back to you..

[Anonymous]

Thank you Manish.  I appreciate it.

Kind regards

Nayan