Make Gadgets Work

Posts tagged "Reports"

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. :) Problem Description: Find out the WorkDay date by when a ticket must be closed given: Country of site for which incident was logged. Time and Date when Incident was logged. Elapsed working hours in decimal. (Ten and a half hours as 10.5 and so on) Opening time of the site for which the incident was logged. Closing Time of the site for which incident was logged. SLA Type - Is the output date to be based purely on number of hours or based on SLA in days (eg: Next Business Day). 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. Example of problem 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. Pre-Requisites: Follow from previous post. Function code and explanation 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 Calling the function Function will expect 6 parameters and with specific format as explained below: param_country - This is the country code as specified in holiday table assigneddatetime - This must be provided in the format %Y-%m-%d %H-%i-%s. So for our example it will be 2018-02-23 15:00:00 param_elapsedhours - This must be provided in decimal hours format. Bear in mind that if SLA Type is not H, you need to understand the number of working hours in allowed number of days in SLA. So for 8 working hours in a day: Same day SLA will have 8 hours Next Business Day will have 16 hours(2*8) A 2 day SLA will have 24 hours and so on starttime - This must be in the format %H:%i. So for our example it will be 08:00 endtime - This must be in the format %H:%i. So for our example it will be 16:00 sla_type - This must be 'H' if the sla is based on hours or anything else (say 'D') if SLA is based on days. 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 2018-02-27 15:00 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 2018-02-26 16:00 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 2018-02-27 09:30

MySQL Function to calculate closing WorkDay date given elapsed working time

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
I wrote this function to cater for a specific requirement and I don't know if there are better ways of doing it but this saved tremendous amount of time and might have real time application elsewhere. Problem Statement: Find out the age of an incident in working minutes, given the following: Time and Date of when an incident was logged Time and date of when the same incident was closed Opening time of the site for which the incident was logged Closing Time of the site for which incident was logged Country of the site for which incident has been logged 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 Function should take all the above five "given" as parameter and then calculate age of the incident. Example of problem Let's say an incident was logged on "Friday 10th June 2016 at 12:00" for a site in the "UK" which opens between 09:00 to 16:00. This incident was then closed on "Tuesday 14th June 2016 at 14:00". For the above incident function should calculate the age as 960 minutes = 16 hours = [4 hours on Friday (12:00 to 16:00) + 7 hours on Monday (09:00 to 16:00) + 5 hours on Tuesday (09:00 to 14:00)] Pre-requisites: A holiday table for the "Country" of the site for which incident is being provided should already be created on the database with the name holiday_table. Created using code below: CREATE TABLE `holiday_table` ( `holiday_table_id` INT(11) NOT NULL, `holiday_date` DATETIME NULL DEFAULT NULL, `week_day` VARCHAR(12) NULL DEFAULT NULL, `holiday_name` VARCHAR(45) NULL DEFAULT NULL, `Country_codes` VARCHAR(45) NOT NULL DEFAULT 'ALL', PRIMARY KEY (`holiday_table_id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; Sample sql-insert Data for holiday_table: INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (2, '2016-03-25 00:00:00', 'Friday', 'Good Friday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (3, '2016-03-28 00:00:00', 'Monday', 'Easter Monday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (4, '2016-05-02 00:00:00', 'Monday', 'Early May bank holiday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (5, '2016-05-30 00:00:00', 'Monday', 'Spring bank holiday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (6, '2016-08-29 00:00:00', 'Monday', 'Summer bank holiday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (7, '2016-12-26 00:00:00', 'Monday', 'Boxing Day', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (8, '2016-12-27 00:00:00', 'Tuesday', 'Christmas Day (substitute day)', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (9, '2016-01-01 00:00:00', 'Friday', 'New Year’s Day', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (10, '2016-02-08 00:00:00', 'Monday', 'Chinese New Year', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (11, '2016-02-09 00:00:00', 'Tuesday', 'Chinese New Year', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (12, '2016-05-21 00:00:00', 'Saturday', 'Vesak Day', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (13, '2016-07-06 00:00:00', 'Wednesday', 'Hari Raya Puasa', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (14, '2016-08-09 00:00:00', 'Tuesday', 'National Day', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (15, '2016-09-12 00:00:00', 'Monday', 'Hari Raya Haji', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (16, '2016-10-29 00:00:00', 'Saturday', 'Deepavali', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (17, '2016-01-01 00:00:00', 'Friday', 'New Year’s Day', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (18, '2016-01-26 00:00:00', 'Tuesday', 'Republic Day', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (19, '2016-07-06 00:00:00', 'Wednesday', 'Idul Fitr', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (20, '2016-08-15 00:00:00', 'Monday', 'Independence Day', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (21, '2016-10-11 00:00:00', 'Tuesday', 'Dussehra/Durga Puja', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (22, '2016-10-31 00:00:00', 'Monday', 'Diwali Privilege Holiday/Gobardhan Puja', 'IN'); Function code and explanation CREATE DEFINER=`root`@`localhost` FUNCTION `workday_time_diff_holiday_table`( `param_country` varchar(10), `assigneddatetime` varchar(20), `closeddatetime` varchar(20), `starttime` varchar(20), `endtime` varchar(20) ) RETURNS int(11) 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 @closeddate = closeddatetime; Set @timecount = 0; 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; Select CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@starttime), CONCAT(SUBSTRING_INDEX(@closeddate, ' ', 1), ' ',@endtime) into @checkstart, @checkend; if (@assigneddate > @checkstart) then if (@closeddate<@checkend) then Set @assigneddate = @assigneddate; Set @closeddate = @closeddate; else Set @assigneddate = @assigneddate; Set @closeddate = @checkend; end if; else if (@closeddate<@checkend) then SET @assigneddate = @checkstart; Set @closeddate = @closeddate; else SET @assigneddate = @checkstart; Set @closeddate = @checkend; end if; end if; #################### /*After above check, the assigneddate and closeddate variables will be reset in accordance with the checks.*/ #################################### SELECT DATEDIFF(@closeddate, @assigneddate) INTO @fixcount; # check the difference between assigned date and closed date. Set @count = @fixcount; # allocate the difference between closed date and assigned date to a counter If @fixcount > 0 then # true if line 57 resulted in more than 1 then run the while loop on next line while @count>=0 do # run the while loop until the count which is right now difference between closed and assigned becomes zero 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 /*Check if the date stored in nextdate (which is assigneddate on first run of while loop and closeddate on last run) is a holiday and set the holiday flag*/ 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; if ( @weekday<5 and @holidayflag=0) then #Proceed if the date in nextdate variable is neither weekend nor a holiday if (@count = @fixcount) then #Check if it is first run.ie. if nextdate is assigneddate Set @timevar1 = @assigneddate; #assign assigndate to variable timevar1 SELECT CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@endtime) INTO @timevar2;#get site closing time on assigned date and store it on to timevar2 elseif (@count = 0) then #if the date in nextdate variable is closeddate then do the following otherwise proceed Select concat(substring_index(@closeddate,' ',1),' ',@starttime) into @timevar1; # Set @timevar2 = @closeddate; else Select concat(@nextdate,' ',@starttime) into @timevar1; SELECT CONCAT(@nextdate, ' ', @endtime) INTO @timevar2; end if; SELECT LEAST(Greatest(((TIME_TO_SEC(TIMEDIFF(@timevar2, @timevar1))) / 3600),0),@maxhoursaday) INTO @timecounttemp; Set @timecount = @timecounttemp + @timecount; end if; Set @timevar1 = @nextdate; SELECT ADDDATE(SUBSTRING_INDEX(@timevar1, ' ', 1),1) INTO @nextdate; Set @count = @count - 1; end while; else #check if the assigned date / closed date is a holiday or weekend select weekday(@assigneddate) 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(@assigneddate,' ',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 assigneddate is a holiday and set the holiday flag if ( @weekday<5 and @holidayflag=0) then #Proceed if the date in assigneddate variable is neither weekend nor a holiday SELECT Least(Greatest(((TIME_TO_SEC(TIMEDIFF(@closeddate, @assigneddate))) / 3600),0),@maxhoursaday) INTO @timecount; else Set @timecount = 0; end if; end if; RETURN @timecount*60; END Calling the function Function will expect 5 parameters and with specific format as explained below: param_country - This is the country code as specified in holiday table assigneddatetime - This must be provided in the format %Y-%m-%d %H-%i-%s. So for our example it will be 2016-06-10 12:00:00 closeddatetime - This must be provided in the format %Y-%m-%d %H-%i-%s. So for our example it will be 2016-06-14 14:00:00 starttime - This must be in the format %H:%i. So for our example it will be 09:00 endtime - This must be in the format %H:%i. So for our example it will be 16:00 The call for this function will be as below: ## To get number of minutes Select `WORKDAY_TIME_DIFF_HOLIDAY_TABLE`('UK','2016-06-10 12:00:00','2016-06-14 14:00:00','09:00','16:00'); ## To get number of hours Select `WORKDAY_TIME_DIFF_HOLIDAY_TABLE`('UK','2016-06-10 12:00:00','2016-06-14 14:00:00','09:00','16:00')/60; ## To get in number of working days Select (`WORKDAY_TIME_DIFF_HOLIDAY_TABLE`('UK','2016-06-10 12:00:00','2016-06-14 14:00:00','09:00','16:00')/60)/(substring_index('16:00',':',1)-substring_index('09:00',':',1)); Complete Flowchart This chart was made using PlantText Chart Code @startuml scale 0.8 'skinparam monochrome true skinparam handwritten true skinparam shadowing false 'title Workday Time Difference Function start :Get the parameter values from calling function - assigneddatetime in 'YYYY-MM-DD hh:mm' format, - closeddatetime in 'YYYY-MM-DD hh:mm' format, - starttime in 'hh:mm' format, - endtime in 'hh:mm' format; :Set @starttime = starttime Set @endtime = endtime Set @assigneddate = assigneddatetime Set @closeddate = closeddatetime Set @timecount = 0 Set @timevar1 = @assigneddate Set @nextdate = @assigneddate Set @timevar2 = null; :Select time_to_sec(timediff (@endtime,@starttime))/3600 into @maxhoursaday; note left #TIMEDIFF calculates difference between two times #Time_to_sec function converts data into seconds #Dividing by 3600 converts the time into hours end note :Set @checkstart = null Set @checkend = null; :Select CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@starttime), CONCAT(SUBSTRING_INDEX(@closeddate, ' ', 1), ' ',@endtime) into @checkstart, @checkend; if (@assigneddate > @checkstart) then (yes) if (@closeddate0?) then (yes) while (count>=0?) is (true) :calculate weekday for nextdate select weekday(@nextdate) into @weekday; :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; note left Set the holiday flag to anything other than zero if the value in nextdate for this loop is a holiday end note if ( @weekday0 into @holidayflag; note left Set the holiday flag to anything other than zero if the value in nextdate for this loop is a holiday end note if ( @weekday

MySQL function to calculate elapsed working time

I wrote this function to cater for a specific requirement and I don't know if there are better ways of doing it but this saved tremendous amount of time and might have real time application elsewhere. Problem Statement: Find out the age of an incident in working minutes, given the