• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Need help creating a MySQL function or stored procedure to run the following working query with variables...

I need help creating a MySQL function or stored procedure to use the following query:

 
SELECT a.County, @sales := (a.`1/2010` + a.`2/2010` + a.`3/2010` + a.`4/2010` + a.`5/2010` + a.`6/2010` + a.`7/2010` + a.`8/2010` + a.`9/2010` + a.`10/2010` + a.`11/2010` + a.`12/2010`) AS `Sales`, 
	@total := ROUND((@sales)/(SELECT @total := SUM(b.`1/2010` + b.`2/2010` + b.`3/2010` + b.`4/2010` + b.`5/2010` + b.`6/2010` + b.`7/2010` + b.`8/2010` + b.`9/2010` + b.`10/2010` + b.`11/2010` + b.`12/2010`) FROM `table` b)*100,1) as `% of Total`,
	@cumlative := @total + @cumlative AS `Cumlative %`
FROM (SELECT @cumlative:=0) c, `table` a
ORDER BY `Sales` DESC;

Open in new window


With the following additions:
1. Capability to define the table as a parameter
2. Capability to search between date ranges, including years (column names are defined as M/DDDD)
3. Ability to error check input (verify table exists, verify columns exist)

(i.e. pareto_function('table', '1/2008', '12/2008')
(i.e. pareto_function('table', '1/2008', '12/2010')

0
Giovanni Heward
Asked:
Giovanni Heward
  • 3
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Does the SQL already function as needed and you just need help in making it into a Stored Procedure?
If you are sending the table name dynamically, then you probably will end up looking at prepared statements: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html
0
 
Giovanni HewardAuthor Commented:
Yes, the query itself is working properly.  Yes, I need help making it into a stored procedure or function.

There should be three variable inputs (table, date_begin, date_end); the table variable should be easy enough to implement, however the date range (my month/year) needs to support year spanning and actually represents column names (for example: pareto_function('table', '1/2008', '12/2010') -- which would span two years.  All the column names are in the M/YYYY format (i.e. 1/2008, 2/2008, 3/2008, etc.)

Thanks
0
 
Kevin CrossChief Technology OfficerCommented:
You can probably use the information schema to help. You can try parsing the column names found there to dates that way you can compare them as dates to the parameters and take advantage of chronology/date functions versus having to deal with character sorting/comparison where '9/2008' is higher than '10/2008'. As dates, those would probably sort the other way around.

I will have to test out my theory.

Do you have a script to create table structure and some sample data?

I will take a look tonight when I am back online.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Giovanni HewardAuthor Commented:
Here is the working query for the sample table...
 
SELECT a.County, 
	@sales := ( a.`1/2008` + a.`2/2008` + a.`3/2008` + a.`4/2008` + a.`5/2008` + a.`6/2008` + a.`7/2008` + a.`8/2008` + a.`9/2008` + a.`10/2008` + a.`11/2008` + a.`12/2008` ) AS `Sales` , 
	@total := ROUND( (@sales) / (SELECT SUM( b.`1/2008` + b.`2/2008` + b.`3/2008` + b.`4/2008` + b.`5/2008` + b.`6/2008` + b.`7/2008` + b.`8/2008` + b.`9/2008` + b.`10/2008` + b.`11/2008` + b.`12/2008` ) FROM `test` b ) *100, 2 ) AS `% of Total` , 
	@cumlative := @total + @cumlative AS `Cumlative %`
FROM (SELECT @cumlative :=0) c, `test` a ORDER BY `Sales` DESC;

Open in new window



Here is the script to create the sample table and populate with data....
 test.sql

Thanks for your help!
0
 
Kevin CrossChief Technology OfficerCommented:
Here you go. This is a tested example. You will need to beef it up with error handling for invalid date range entry. You can play with using actual DATE parameters also. Figure you have all the key pieces here and can expand however you see fit for your needs.

Hope that helps!

Best regards and happy coding,

Kevin
DELIMITER $$

DROP PROCEDURE IF EXISTS pareto_function;
CREATE PROCEDURE pareto_function(in tablename VARCHAR(255), in date_begin VARCHAR(7), in date_end VARCHAR(7))
BEGIN

-- Build dynamic SQL string using table and date range
SELECT 
CONCAT('SELECT a.County, 
	@sales := (`', GROUP_CONCAT(column_name SEPARATOR '`+`'),'`) AS `Sales`, 
	@total := ROUND((@sales)/(SELECT SUM(`', GROUP_CONCAT(column_name SEPARATOR '`+`'), '`) FROM `', table_name,'` b) *100, 2) AS `% of Total`, 
	@cumlative := ROUND(@total + @cumlative, 2) AS `Cumlative %`
FROM (SELECT @cumlative :=0) c, `', table_name,'` a ORDER BY `Sales` DESC;')
FROM information_schema.columns
WHERE table_name = tablename
AND column_name REGEXP '[0-9]{1,2}/[0-9]{4}'
AND STR_TO_DATE(column_name, '%m/%Y') 
    BETWEEN STR_TO_DATE(date_begin, '%m/%Y') AND STR_TO_DATE(date_end, '%m/%Y')
GROUP BY table_name
INTO @sqlstring
;

-- If no table found, skip processing.
IF(NOT @sqlstring IS NULL) THEN
-- Execute dynamic SQL string.   
   PREPARE s1 FROM @sqlstring;
   EXECUTE s1;
   DEALLOCATE PREPARE s1;
END IF;

END $$

DELIMITER ;

CALL pareto_function('test', '1/2008', '12/2008');

Open in new window

0
 
Giovanni HewardAuthor Commented:
Thank you very much!  Your solution worked perfectly.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now