Hi guys
Please I want to know the deadline for resulotion for each organization
for ex:123467 this ticket has been processed by two organizations( Amerique et Qatar) but i want now the time taken by organisation to solve this ticket and thereafter i want calculate sla for every organisation
Its urgent Plz Thanks in advance!
Set @StartDate = '2017-01-01 00:00:00.000';
Set @EndDate='2018-05-23 23:59:59.000'; //12/05/2017 10:21
Set @SnapShotDate= $(@EndDate);
Holidays:
LOAD @1 as Day
FROM
..\1_Setup\SD_KPIs_And_Expressions.xls
(biff, no labels, table is Holidays$);
Let vHol= chr(39) ;
For j=0 to FieldValueCount('Day')-1
Let vHol= vHol & peek('Day',$(j),'Holidays') & chr(39)& ',' & chr(39);
Next j;
Let vHol= vHol & chr(39) ;
Drop table Holidays;
FactTicket_Tmp:
LOAD *
,If(Isnull(ResolveDate)
,If (PriorityCode = 1
,Interval(Date('$(@SnapShotDate)')-OpenDate), // For emergency tickets add the condition on the application criticity, add condition if ticket has been closed or not (pivot date instead of Resolve Date)
Interval(rangesum(NetWorkDays(OpenDate+1,Date('$(@SnapShotDate)')-1,$(vHol)) * MakeTime(10) // 10 hours per workday, for all day inbetween the period, excluding bounderies
,if(NetWorkDays(Date('$(@SnapShotDate)'),Date('$(@SnapShotDate)'),$(vHol)),Rangemin(rangemax(frac(Date('$(@SnapShotDate)')),maketime(8)),maketime(18))-Rangemax(rangemin(frac(Date('$(@SnapShotDate)')),maketime(8)),maketime(8)),0) // working hours last day
,if(NetWorkDays(OpenDate,OpenDate,$(vHol)),Rangemin(rangemax(frac(OpenDate),maketime(18)),maketime(18))-Rangemax(rangemin(frac(OpenDate),maketime(18)),maketime(8)),0) // working first day
,if(NetWorkDays(OpenDate,OpenDate,$(vHol)) and floor(OpenDate)=floor(Date('$(@SnapShotDate)')),-MakeTime(10))))) // correct for first equals last day
,If (PriorityCode = 1
//and Match(Lookup('Critique','CIApplicationName',Upper(%CIApplicationName),'RefApplications'),'OUI')=1
,Interval(ResolveDate-OpenDate),
Interval(rangesum(NetWorkDays(OpenDate+1,ResolveDate-1,$(vHol)) * MakeTime(10)
,if(NetWorkDays(ResolveDate,ResolveDate,$(vHol)),Rangemin(rangemax(frac(ResolveDate),maketime(8)),maketime(18))-Rangemax(rangemin(frac(ResolveDate),maketime(8)),maketime(8)),0)
,if(NetWorkDays(OpenDate,OpenDate,$(vHol)),Rangemin(rangemax(frac(OpenDate),maketime(18)),maketime(18))-Rangemax(rangemin(frac(OpenDate),maketime(18)),maketime(8)),0)
,if(NetWorkDays(OpenDate,OpenDate,$(vHol)) and floor(OpenDate)=floor(ResolveDate),-MakeTime(10))
)))
) as NetWorkingHours
RESIDENT FactTicket_Init;
