Calculate Weekly Medians (1 week) using Setcode and a Start Date

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

DELIMITER $$

DROP PROCEDURE IF EXISTS `activity`.`calc_weekly_medians` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `calc_weekly_medians`(IN l_SETNAME VARCHAR(50), IN Start_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.`DateReceived` BETWEEN Start_Date and DATE_ADD(Start_Date,INTERVAL +1 WEEK)
    AND s.`Set`=l_SETNAME
    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.weekly_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.