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

Posted on 2011-10-14
Last Modified: 2012-05-12
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

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

Question by:Giovanni Heward
    LVL 59

    Expert Comment

    by:Kevin Cross
    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:
    LVL 14

    Author Comment

    by:Giovanni Heward
    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.)

    LVL 59

    Expert Comment

    by:Kevin Cross
    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.
    LVL 14

    Author Comment

    by:Giovanni Heward
    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....

    Thanks for your help!
    LVL 59

    Accepted Solution

    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,

    DROP PROCEDURE IF EXISTS pareto_function;
    CREATE PROCEDURE pareto_function(in tablename VARCHAR(255), in date_begin VARCHAR(7), in date_end VARCHAR(7))
    -- Build dynamic SQL string using table and date range
    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;
    END IF;
    END $$
    CALL pareto_function('test', '1/2008', '12/2008');

    Open in new window

    LVL 14

    Author Closing Comment

    by:Giovanni Heward
    Thank you very much!  Your solution worked perfectly.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now