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')

LVL 15
Giovanni HewardAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief 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
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
 
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
 
Giovanni HewardAuthor Commented:
Thank you very much!  Your solution worked perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.