Calculate Stats for One Test Between Defined Dates

Posted on the March 2nd, 2008 under Databases, Featured by cwebster 

DELIMITER $$

DROP PROCEDURE IF EXISTS `activity`.`calcstats` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `calcstats`(IN l_SETNAME VARCHAR(50), IN Start_Date Date, IN End_Date Date)

BEGIN

  declare done INT DEFAULT 0;
  declare c_SetName VARCHAR(50);
  declare c_HrsIn DOUBLE;
  declare c_DateReceived DATE;
  declare c_Count INT;
  declare c_AVG DOUBLE;

DECLARE cur1 CURSOR FOR

    SELECT s.`Set`, s.`HrsIn`, s.`DateReceived`, count(*),avg(HrsIn) FROM setinfo s
    WHERE s.`Set` =l_SETNAME AND s.`DateReceived` BETWEEN Start_date and End_Date
    GROUP BY s.`DateReceived`;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  open cur1;

  repeat
        fetch cur1 into c_SetName, c_HrsIn, c_DateReceived,c_Count,c_AVG;
        if not done then
          insert into activity.stats VALUES (c_SetName, c_HrsIn, c_DateReceived,c_AVG,c_Count);
        end if;
  until done end repeat;

close cur1;

END $$

DELIMITER ;

You must be logged in to post a comment.