Make Gadgets Work

Posts tagged "MySQL"

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
My requirement was to get the data in a format that google chart can use to draw the chart I want. Now gogle chart accepts data in json format where all column names separated with comma are in first square bracket set followed by values in rest of the square bracket sets and each square bracket set is separated by comma as well. Having searched on good old google, there did not appear to be any quick way of doing it without getting hands dirty with likes of php and as I was to plug this into a BIRT report where a simple html would do the trick, I really just needed the data-set to be returned in format that google-chart understood. I figured it can be easily done using a MySQL stored procedure and can be a repeatable process which resulted in creation of stored procedure presented below. The code CREATE DEFINER=`root`@`localhost` PROCEDURE `json_builder_multiple_string`(IN `var1` varchar(10000), IN `tab_name` text, IN `int_col_as_str` int) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN /* - This procedure will take columns and tablename as parameter. - Third parameter is to tell the procedure how many columns are to be returned as string - surrounded by quotes - Third parameter must always be less than the total columns being requested. - Rest of the columns must have numerical values as they won't be surrounded with quotes - Procedure returns 0 for each null value. ​ SAMPLE PROCEDURE CALL CALL `json_builder_multiple_string`('Year,Month,Region,Sales,Expenses', 'myjsonexample',4); ​ SAMPLE QUERY GENERATED: SELECT CONCAT('[\'Year\',\'Month\',\'Region\',\'Sales\',\'Expenses\'],', GROUP_CONCAT('[', CONCAT_WS(',', CONCAT('\'', IFNULL(`Year`, 0), '\',\'', IFNULL(`Month`, 0), '\',\'', IFNULL(`Region`, 0), '\',\'', IFNULL(`Sales`, 0), '\''), IFNULL(`Expenses`, 0)) SEPARATOR '],'), ']') AS data_set FROM myjsonexample ​ SAMPLE OUTPUT: ['Year','Month','Region','Sales','Expenses'],['2004','JAN','NW','1000',400],['2005','Feb','SW','1170',460],['2006','Mar','NE','2000',1210],['2007','Apr','SE','650',540],['2008','May','EC','0',0] ​ SAMPLE TABLE USED: Drop Table if exists `myjsonexample`; CREATE TABLE `myjsonexample` ( `Year` int(11) NOT NULL, `Month` varchar(25), `Region` varchar(25), `Sales` int(11) , `Expenses` int(11) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `myjsonexample` (`Year`, `Month`, `Region`, `Sales`, `Expenses`) VALUES (2004,'JAN','NW',1000,400), (2005,'Feb','SW',1170,460), (2006,'Mar','NE',2000,1210), (2007,'Apr','SE',650,540), (2008,'May','EC',null,null); */ SET SESSION group_concat_max_len = 1000000; Set @stmt1 = null; select concat('Select concat(\'',concat('[\\\'',replace(replace(var1,'_',' '),',','\\\',\\\''),'\\\'],'),'\'', ', group_concat(\'[\', concat_ws(\',\', CONCAT(\'\\\'\', ifnull(`',replace(substring_index(var1,',',int_col_as_str),',','`,0),\'\\\',\\\'\',ifnull(`'), '`,0), \'\\\'\'),', (concat('ifnull(`', replace( SUBSTRING(var1,LENGTH(SUBSTRING_INDEX(var1, ',', int_col_as_str)) + 2,LENGTH(var1)), ',', '`,0),ifnull(`'), '`,0)') ) ,') SEPARATOR \'],\'),\']\') as data_set from ', tab_name) into @stmt1; Prepare stmt2 from @stmt1; Execute stmt2; END The alternative code Another variation of above stored procedure where the output is returned in an output parameter can be created simply by adding two more lines to above code after Execute stmt2 and also including the output parameter in first line. Complete code below: CREATE DEFINER=`root`@`localhost` PROCEDURE `json_builder_outparam`(IN `var1` varchar(10000), IN `tab_name` text, OUT `varout` text) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN /* - This procedure will take columns and tablename as parameter. - Third parameter is to tell the procedure how many columns are to be returned as string - surrounded by quotes - Third parameter must always be less than the total columns being requested. - Rest of the columns must have numerical values as they won't be surrounded with quotes - Procedure returns 0 for each null value. ​ SAMPLE PROCEDURE CALL CALL `json_builder_multiple_string`('Year,Month,Region,Sales,Expenses', 'myjsonexample',4); ​ SAMPLE QUERY GENERATED: SELECT CONCAT('[\'Year\',\'Month\',\'Region\',\'Sales\',\'Expenses\'],', GROUP_CONCAT('[', CONCAT_WS(',', CONCAT('\'', IFNULL(`Year`, 0), '\',\'', IFNULL(`Month`, 0), '\',\'', IFNULL(`Region`, 0), '\',\'', IFNULL(`Sales`, 0), '\''), IFNULL(`Expenses`, 0)) SEPARATOR '],'), ']') AS data_set FROM myjsonexample ​ SAMPLE OUTPUT: ['Year','Month','Region','Sales','Expenses'],['2004','JAN','NW','1000',400],['2005','Feb','SW','1170',460],['2006','Mar','NE','2000',1210],['2007','Apr','SE','650',540],['2008','May','EC','0',0] ​ SAMPLE TABLE USED: Drop Table if exists `myjsonexample`; CREATE TABLE `myjsonexample` ( `Year` int(11) NOT NULL, `Month` varchar(25), `Region` varchar(25), `Sales` int(11) , `Expenses` int(11) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `myjsonexample` (`Year`, `Month`, `Region`, `Sales`, `Expenses`) VALUES (2004,'JAN','NW',1000,400), (2005,'Feb','SW',1170,460), (2006,'Mar','NE',2000,1210), (2007,'Apr','SE',650,540), (2008,'May','EC',null,null); */ SET SESSION group_concat_max_len = 1000000; Set @stmt1 = null; select concat('Select concat(\'',concat('[\\\'',replace(replace(var1,'_',' '),',','\\\',\\\''),'\\\'],'),'\'', ', group_concat(\'[\', concat_ws(\',\', CONCAT(\'\\\'\', ifnull(`',replace(substring_index(var1,',',int_col_as_str),',','`,0),\'\\\',\\\'\',ifnull(`'), '`,0), \'\\\'\'),', (concat('ifnull(`', replace( SUBSTRING(var1,LENGTH(SUBSTRING_INDEX(var1, ',', int_col_as_str)) + 2,LENGTH(var1)), ',', '`,0),ifnull(`'), '`,0)') ) ,') SEPARATOR \'],\'),\']\') as data_set from ', tab_name) into @stmt1; Prepare stmt2 from @stmt1; Execute stmt2; Set varout = @varouttemp; Select varout; END The BIRT usage explained My sample BIRT reports are available for download HERE. Enabling the google chart on BIRT couldn't be simpler. Create a new report Add a text field, open it and change it to HTML as shown in the image below Copy the html[1] (modify as required - I used jsfiddle for checking the code and making changes) and paste in to the text field. Run Once the BIRT preview is working fine, create a dataset by calling the stored-procedure above and bind it to this text field. To bind a dataset, in "Report Design" perspective, click on the field and then in "Property Editor" select "Binding" as shown below: Then right click on the text field and click on edit and then click on the "Fx" symbol next to where field was changed to HTML on top of the window. This will open javascript editor. On javascript editor select the static dataset and replace it by selecting the dataset like so: That should change the HTML to dynamically get the data from json builder. <html> <head> <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> <script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript"> google.charts.load('current', { 'packages': ['geochart', 'corechart', 'table', 'controls'] }); google.charts.setOnLoadCallback(drawRegionsMap); function drawRegionsMap() { // Create our data table. var data = google.visualization.arrayToDataTable([ ['Country', 'Period', 'RMPV', 'TARGET', 'Average'], ['Germany', 'Nov-2015', 75, 120, 97.5], ['Germany', 'Dec-2015', 55, 65, 60], ['Canada', 'Nov-2015', 200, 85,42.5], ['Canada', 'Dec-2015', 55, 65, 60], ['IN', 'Nov-2015', 328, 30, 179], ['IN', 'Dec-2015', 55, 65, 60], ['France', 'Dec-2015', 30, 100, 65], ['France', 'Nov-2015', 75, 120, 97.5], ['Brazil', 'Dec-2015', 50, 100, 75], ['Brazil', 'Nov-2015', 200, 85, 142.5], ['United States', 'Dec-2015', 55, 65, 60], ['United States', 'Nov-2015', 328, 30, 179] ]); var data1 = google.visualization.data.group(data,[0], [{'column': 4, 'aggregation': google.visualization.data.avg, 'type': 'number'}] ); var options = { colorAxis: { // values: [0,30,90,150], colors: ['#F40EF4','#FCFC2A','#7BFC2A', '#FC4A2A'] }, backgroundColor: '#F1F7FD', datalessRegionColor: '#8B8E91', defaultColor: '#f5f5f5' }; var chart = new google.visualization.GeoChart(document.getElementById('regions_div')); function selectHandler() { var selectedItem = chart.getSelection()[0]; if (selectedItem) { var message = data1.getValue(selectedItem.row, 0); // alert(message) //if(message=='Canada'){ var display_value = [message]; //}; }; /* Start of testing*/ // Create a dashboard. var dashboard = new google.visualization.Dashboard( document.getElementById('dashboard_div')); // Create a range slider, passing some options var donutRangeSlider = new google.visualization.ControlWrapper({ 'controlType': 'NumberRangeFilter', 'containerId': 'filter_div', 'options': { //'filterColumnLabel': 'M602' 'filterColumnIndex': [2] } }); // Create a range slider, passing some options var donutRangeSlider1 = new google.visualization.ControlWrapper({ 'controlType': 'NumberRangeFilter', 'containerId': 'filter_div1', 'options': { //'filterColumnLabel': 'M525' 'filterColumnIndex': [3] } }); var categoryPicker = new google.visualization.ControlWrapper({ controlType: 'CategoryFilter', containerId: 'control3', state: { selectedValues: display_value }, options: { //filterColumnLabel: 'Location Type', filterColumnIndex: [0], ui: { labelStacking: 'vertical', allowTyping: false, allowMultiple: false, allowNone: false } } }); // Create a column chart, passing some options var ColumnChart = new google.visualization.ChartWrapper({ 'chartType': 'ColumnChart', 'containerId': 'chart1', 'options': { 'width': '900px', 'legend': 'bottom', 'vAxes': { 0: { title: 'Average Utilisation/Target Utilisation' }, 1: { title: 'RMPV Utilisation' } }, 'series': { 1: { targetAxisIndex: 1 } } }, view: { columns: [1, 2, 3, 4] } }); // Create a table chart, passing some options var tableChart = new google.visualization.ChartWrapper({ 'chartType': 'Table', 'containerId': 'chart2', }); var pie = new google.visualization.ChartWrapper({ chartType: 'PieChart', containerId: 'chart3', options: { legend: 'bottom', title: 'Average Utilisation per month', pieSliceText: 'value' }, // Instruct the piechart to use colums 0 (Name) and 3 (Donuts Eaten) // from the 'data' DataTable. view: { columns: [1, 4] } }); // Establish dependencies, declaring that 'filter' drives 'pieChart', // so that the pie chart will only display entries that are let through // given the chosen slider range. dashboard.bind([donutRangeSlider, donutRangeSlider1, categoryPicker], [ColumnChart, tableChart, pie]); // Draw the dashboard. dashboard.draw(data); //redraw the main chart so selection doesn't retun empty chart.draw(data1, options); // End of testing*/ }; google.visualization.events.addListener(chart, 'select', selectHandler); chart.draw(data1, options); } </script> </head> <body> <div id="regions_div" style="width: 900px; height: 500px;"></div> <!--Div that will hold the dashboard--> <div id="dashboard_div"> <!--Divs that will hold each control and chart--> <table> <tr> <td> <div id="control3" align="center"></div> </td> </tr> <tr> <td> <div id="filter_div"></div> </td> <td> <div id="filter_div1"></div> </td> </tr> </table> <div id="chart1"></div> <div id="chart3"></div> <div id="chart2" align="center"></div> </div> </body> </html> HTML to be pasted in text field: ↩︎

MySQL Stored Procedure to return JSON for google charts on BIRT

My requirement was to get the data in a format that google chart can use to draw the chart I want. Now gogle chart accepts data in json format where all column names separated with comma are in first square bracket set followed by values in rest of the square