On my post MySQL function to calculate elapsed working time I was asked in comments if the assumptions can be reversed such that given start date, starting time and closing time of site and the elapsed working hours, function should return the closed date.
I was convinced that it will be possible to achieve this with minor tweaks to original logic and so I only concentrated on original code and how to tweak it to achieve the result.
It is very likely that there might exist a more elegant solution but
frankly I did not have a usecase I did find a usecase afterall - Clue was in redefining the problem description for this.
Presented below is the function that with my minimal testing seems to give correct results. Feel free to try it and as always any feedback is welcome. :)
Find out the WorkDay date by when a ticket must be closed given:
Assumption: It is assumed that opening and closing times are same on all working days and that all the sites are closed on holidays and weekends.
Let's say an incident was logged on "Friday 23rd Feb 2018 at 15:00" for a site in the "UK" which opens between 08:00 to 16:00.
Now if the SLA is purely based on hours i.e. Tickets must be closed withing 16 hours then for the above incident function should calculate the closing date as:
2018-02-27 15:00 = 1 hour on Friday, then skip Saturday and Sunday, 8 hours on Monday and 7 hours on Tuesday the 27th of Feb 2018.
However, if the SLA is based on number of days, then 16 hours will translate to an SLA of next business day hence in this case function should return
2018-02-26 16:00 which is a Monday end of day and the next business day for this incident.
The use case really is in creating a report that shows the target closed date based on SLA. Specifically Next Business Day SLA, which isn't direct hours calculation and is really always the next working day but based on cut-off time when the call is logged.
So a call logged on a working day 1 after cut-off will be counted as logged on working day 2 and hence will need to be closed by working day 3 whereas a call logged within cut-off of working day 1 will need to be closed by close of business working day 2.
This is turn can allow for a comparison between Target Closed Date from this function and Actual Closed Date to show SLA failures where Actual Closed Date is greater than Target Closed Date.
Follow from previous post.
CREATE DEFINER=`ankit`@`%` FUNCTION `get_closed_date_given_start_time`( `param_country` VARCHAR(20), `assigneddatetime` VARCHAR(20), `param_elapsedhours` VARCHAR(10), `starttime` VARCHAR(20), `endtime` VARCHAR(20), `sla_type` VARCHAR(10) ) RETURNS TEXT LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN Set @starttime = starttime; Set @endtime = endtime; Select time_to_sec(timediff(@endtime,@starttime))/3600 into @maxhoursaday; Set @assigneddate = assigneddatetime; Set @timecount = param_elapsedhours; Set @timevar1 = @assigneddate; Set @nextdate = @assigneddate; Set @timevar2 = null; Set @param_country = param_country; ############ Check if the assigned time was before the starttime or closed time was after the endtime provided ############# Set @checkstart = null; Set @checkend = null; Set @slatype = sla_type; Select CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@starttime), CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@endtime) into @checkstart, @checkend; if (@slatype = 'H') then if (@assigneddate < @checkstart) then SET @nextdate = @checkstart; end if; else if (@assigneddate < @checkend) then SET @nextdate = @checkstart; end if; end if; Set @count = @timecount; while @count>0 do select weekday(@nextdate) into @weekday; # Assign the weekday value to @weekday. Weekday returns o for Monday, 2 for Tuesday ...5 for Saturday and 6 for Sunday Select sum(if(date_format(holiday_date,'%Y-%m-%d') = substring_index(@nextdate,' ',1),1,0)) from holiday_table where Country_codes = 'ALL' or instr(Country_codes,@param_country)>0 into @holidayflag; #Check if the date stored in nextdate (which is assigneddate on first run of while loop) is a holiday and set the holiday flag if ( @weekday<5 and @holidayflag=0) then #Proceed if the date in nextdate variable is neither weekend nor a holiday if (@count = @timecount) then #Check if it is first run.ie. if nextdate is assigneddate Set @timevar1 = @nextdate; #assign assigndate to variable timevar1 SELECT CONCAT(SUBSTRING_INDEX(@nextdate, ' ', 1), ' ',@endtime) INTO @timevar2;#get site closing time on assigned date and store it on to timevar2 else Select CONCAT(substring_index(@nextdate,' ',1),' ',@starttime) into @timevar1; SELECT CONCAT(substring_index(@nextdate,' ',1), ' ', @endtime) INTO @timevar2; end if; SELECT LEAST(Greatest(((TIME_TO_SEC(TIMEDIFF(@timevar2, @timevar1))) / 3600),0),@maxhoursaday) INTO @timecounttemp; Set @count = @count - @timecounttemp; end if; if @count > 0 then Select adddate(concat(substring_index(@nextdate,' ',1),' ',@starttime),1) INTO @nextdate; else Select SUBSTRING_INDEX(@nextdate, ' ', 1) INTO @nextdate; end if; end while; Set @finaldate = null; Select concat(@nextdate,' ',substring_index(addtime(@endtime,sec_to_time(3600*@count)),':',2)) INTO @finaldate; RETURN @finaldate; END
Function will expect 6 parameters and with specific format as explained below:
%Y-%m-%d %H-%i-%s. So for our example it will be 2018-02-23 15:00:00
%H:%i. So for our example it will be 08:00
%H:%i. So for our example it will be 16:00
The call for this function will be as below:
For hours SLA:
Select get_closed_date_given_start_time('ALL','2018-02-23 15:00:00','16','08:00','16:00','H');
This will give an output of
For Next Business Day SLA:
Select get_closed_date_given_start_time('ALL','2018-02-23 15:00:00','16','08:00','16:00','D');
This will give an output of
For just calculating next working day date given number of hours:
Select get_closed_date_given_start_time('ALL','2018-02-23 15:00:00','10.5','08:00','16:00','H');
This will give an output of