Updated logic

Posted on the March 13th, 2008 under Databases by cwebster

Add data to main activity table

This triggers:

1. Query to find earliest date in table (start_date)
2. Find latest date in table (end_date)

Loop though set table using Setcode

From setcode decide if daily or weekly

query table and count number of samples received in a day.
If > 20 then do daily stats

SELECT Min(s.`DateReceived`) INTO Start_Date FROM setinfo s
ORDER BY s.`DateReceived`;

SELECT Max(s.`DateReceived`) INTO End_Date FROM setinfo s
ORDER BY s.`DateReceived`;

ELSE
do weekly stats

calc_all_stats MySQL stored procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `activity`.`calc_stats_all_tests_ver2` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `calc_stats_all_tests_ver2`()
BEGIN

  declare done INT DEFAULT 0;
  declare c_SetName VARCHAR(50);
  declare Start_Date Date;
  declare End_Date Date;
  declare ave_per_day DOUBLE;

DECLARE cur1 CURSOR FOR

SELECT s.`SetCode` FROM setparameters s;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

DROP TABLE IF EXISTS `activity`.`daily_stats`;
CREATE TABLE  `activity`.`daily_stats` (
  `SetName` varchar(50) NOT NULL,
  `HrsIn` double NOT NULL,
  `DateReceived` datetime NOT NULL,
  `AVGTurnaround` double NOT NULL,
  `NumberOfRequests` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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;

SELECT Min(s.`DateReceived`) INTO Start_Date FROM setinfo s
ORDER BY s.`DateReceived`;

SELECT Max(s.`DateReceived`) INTO End_Date FROM setinfo s
ORDER BY s.`DateReceived`;

  open cur1;

  repeat
        fetch cur1 into c_SetName;
          if not done then

            SELECT AVG (counts) INTO ave_per_day from(
              SELECT s.`Set`, count(*) as counts FROM setinfo s
              WHERE s.`Set` = c_SetName
              GROUP BY s.`DateReceived`) as f;

            if ave_per_day > 20 then
              CALL calcstats(c_SetName, Start_Date, End_Date);
            else
              CALL calculate_weekly_medians_by_date_range(Start_Date, End_Date, c_SetName);
            end if;

          end if;
  until done end repeat;

close cur1;

END $$

DELIMITER ;

You must be logged in to post a comment.