Calculate Weekly Medians Between Two Dates using Setcode and a Start Date and an End Date

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

DELIMITER $$

DROP PROCEDURE IF EXISTS `activity`.`calculate_weekly_medians_by_date_range` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `calculate_weekly_medians_by_date_range`(IN Start_Date DATE, IN End_date DATE, IN C_SetName VARCHAR(50))
BEGIN

  DECLARE Count_Date DATE;

  DROP TABLE IF EXISTS `activity`.`weekly_stats`;

  CREATE TABLE  `activity`.`weekly_stats` (
    `SetName` varchar(50) NOT NULL,
    `HrsIn` double NOT NULL,
    `WeekDate` datetime NOT NULL,
    `AVGTurnaround` double NOT NULL,
    `NumberOfRequests` INT NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  SET Count_Date=Start_Date;

  WHILE Count_Date < End_Date DO
    CALL calc_weekly_medians(c_SetName, Count_Date);
    SET Count_Date = DATE_ADD(Count_Date,INTERVAL +1 WEEK);
  END WHILE;

END $$

DELIMITER ;