I am analysing a Call Data dump and I have to calculate actual time taken by the engineer to resolve the call. Currently I am simply subtracting Opendate from Closedate eg.
(Closedate - Opendate) * 24 * 60
Its giving me the time in terms of minutes.
But now I have one more challenge in this. If a holiday or Sunday falls on any of the date between Opendate and Closedate or if the call was logged out of Service-Window (eg. After 8pm and Before 8am); I have to subtract that time also to get the ACTUAL TIME TAKEN WITHIN SERVICE WINDOW & WITHIN SLA eg.
I have the list of holidays within a calendar year. I can try to use VbScript but it limits me in terms of date format and QV table access like I can't access a QV table data inside a VbScript macro.
What I want is to create a function in the Script Editor itself where I could use all programming structures like loops, ifs etc. So that I can pass Opendate and Closedate to this function from Load script, written next to this function, and it will do all for me.
I threw together a small example document. As always when it comes to these things there are several possible ways of achieving the wanted result and I have no idea how this would play over a larger data set and it's probably far from optimal but here goes.
Please see the attached example. It has a description of how I'm calculating the stuff. This will require some customization depending on what date formats you use and how the fields you use are formatted etc.
STEP 1: IF(((Week(Closure_Date)-week(Created_Date)) < 0),(Week(Closure_Date) + Week(YearEnd(Date(Today()))) - Week(Created_Date)),(Week(Closure_Date)-week(Created_Date))) AS SundayFlag -- Total Sunday between two dates
STEP 2: Closure_Date - Created_Date AS TurnAroundTIme -- No of days for completing task
STEP 3: TurnAroundTIme - SundayFlag AS ReduceSunday -- Reduce Sunday from total time period
STEP 4: NETWORKDAYS(Created_Date, Closure_Date) AS TAT1 -- Reduce Saturday and Sunday from total time period NETWORKDAYS(Created_Date, Closure_Date, <HOLIDAYS>) AS TAT2 -- Reduce Saturday, Sunday and Holidays from total time period
STEP 5: TAT2 - TAT1 AS HolidayFlag -- Total Holidays between two dates
STEP 6: ReduceSunday - HolidayFlag AS TAT_CREATED_CLOSE -- Final No of Days from completing task
To mention Holidays, I have the list of all the holidays in a calendar year. To put it as a parameter to NetWorkDays(), I have created a string of all the holidays with Concat() function. Now I have dates like 26-01-2009;15-08-2009;19-08-2009;02-10-2009 and so on. For a parameter, these should be like '26-01-2009','15-08-2009','19-08-2009'...
So can i use Networkdays(OpenDate,CloseDate,Concat(Holidays,';'))
If yes, then how to get '26-01-2009','15-08-2009','19-08-2009' from 26-01-2009;15-08-2009;19-08-2009 as ''' & Replace(Concat(Holidays,';'),';','','') & ''' is not working as in QV, we have to use '(single quote) to represent a string and i have to replace ; with ',' ...is there any consturct like other programming language & SQL which tells to replace() that coming ' is part of a string, not the enclosing character
