-DOESN't WORKUSE cdrsDROP PROCEDURE mscSequence;delimiter | CREATE PROCEDURE mscSequence(IN iDate DATE, IN msc CHAR(4))BEGIN DECLARE sufix VARCHAR(15); DECLARE sufixT VARCHAR(15); DECLARE sufixY VARCHAR(15); DECLARE done INT DEFAULT 0; DECLARE counter INT DEFAULT 0; DECLARE hasHole INT DEFAULT 0; DECLARE ant mediumint unsigned; DECLARE dif mediumint unsigned; DECLARE curr mediumint unsigned; DECLARE hora time; DECLARE fecha date; DECLARE min INT; DECLARE max INT; DECLARE last INT; DECLARE diff INT; SET hasHole = 0; SELECT CONCAT(msc,'_',CONVERT(DATE_FORMAT(iDate,'%Y%m%d'),CHAR(15))) into sufix; SELECT CONCAT(msc,'_',CONVERT(DATE_FORMAT(DATE_ADD(iDate, interval 1 day ),'%Y%m%d'),CHAR(15))) into sufixT; SELECT CONCAT(msc,'_',CONVERT(DATE_FORMAT(DATE_ADD(iDate, interval -1 day ),'%Y%m%d'),CHAR(15))) into sufixY; CREATE TEMPORARY TABLE IF NOT EXISTS tmp_seq (sequencenumber mediumint unsigned primary key, timestamp time,startofchargingdate date); CREATE TEMPORARY TABLE IF NOT EXISTS tmp_q (minr int, maxr int,cuenta int, hora varchar(50),fecha date); TRUNCATE TABLE tmp_seq; TRUNCATE TABLE tmp_q; SET @sqlStmt = CONCAT('INSERT INTO tmp_seq SELECT sequencenumber,timestamp,startofchargingdate FROM ',sufix,' WHERE startofchargingdate!=\'0000-00-00\' UNION SELECT sequencenumber,timestamp,startofchargingdate FROM ',sufixY,' WHERE hour>12 AND sequencenumber > (SELECT MIN(sequencenumber) FROM ',sufix,' WHERE hour=0) AND startofchargingdate!=\'0000-00-00\' UNION SELECT sequencenumber,timestamp,startofchargingdate FROM ',sufixT,' WHERE hour<12 AND sequencenumber<(SELECT MAX(sequencenumber) FROM ',sufix,' WHERE timestamp BETWEEN \'23:58:00\' and \'23:59:59\') AND startofchargingdate!=\'0000-00-00\' ORDER BY 1'); SELECT @sqlStmt; PREPARE stmtx FROM @sqlStmt; EXECUTE stmtx; DEALLOCATE PREPARE stmtx; SELECT sequencenumber FROM tmp_seq INTO last LIMIT 1; SELECT MAX(sequencenumber),MIN(sequencenumber),MAX(sequencenumber)-MIN(sequencenumber) FROM tmp_seq INTO min,max,diff; IF diff>10000000 THEN UPDATE tmp_seq SET sequenceNumber=sequenceNumber+max+1 WHERE sequenceNumber < last; END IF; BEGIN DECLARE cur1 CURSOR FOR SELECT sequencenumber,timestamp,startofchargingdate FROM tmp_seq ORDER BY 1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO curr,hora,fecha; IF NOT done THEN IF counter = 0 THEN SET ant = curr; ELSE IF curr <> ant + 1 THEN SET dif = curr-ant-1; INSERT INTO tmp_q values(ant+1,curr-1,dif,hora,fecha); END IF; SET ant = curr; END IF; SET counter = counter + 1 ; END IF; UNTIL done END REPEAT; CLOSE cur1; SELECT COUNT(*) FROM tmp_q WHERE fecha=iDate AND cuenta>2 INTO hasHole; DELETE FROM daily_queries.sequenceHoles WHERE date=iDate AND switch=msc; IF hasHole > 0 THEN INSERT INTO daily_queries.sequenceHoles (`date`,switch,`type`,`from`,`to`,difference,observations) SELECT iDate,msc,'',minr,maxr,cuenta,'' FROM tmp_q WHERE fecha=iDate AND cuenta>2 ON DUPLICATE KEY UPDATE difference=tmp_q.cuenta; ELSE INSERT INTO daily_queries.sequenceHoles (`date`,switch,`type`,`from`,`to`,difference,observations) VALUES (iDate,msc,'','','','',''); END IF; END;END|delimiter ;---DO WORKUSE cdrsDROP PROCEDURE mscSequence;delimiter | CREATE PROCEDURE mscSequence(IN iDate DATE, IN msc CHAR(4))BEGIN DECLARE sufix VARCHAR(15); DECLARE sufixT VARCHAR(15); DECLARE sufixY VARCHAR(15); DECLARE done INT DEFAULT 0; DECLARE counter INT DEFAULT 0; DECLARE hasHole INT DEFAULT 0; DECLARE ant mediumint unsigned; DECLARE dif mediumint unsigned; DECLARE curr mediumint unsigned; DECLARE hora time; DECLARE fecha date; DECLARE min INT; DECLARE max INT; DECLARE last INT; DECLARE diff INT; SET hasHole = 0; SELECT CONCAT(msc,'_',CONVERT(DATE_FORMAT(iDate,'%Y%m%d'),CHAR(15))) into sufix; SELECT CONCAT(msc,'_',CONVERT(DATE_FORMAT(DATE_ADD(iDate, interval 1 day ),'%Y%m%d'),CHAR(15))) into sufixT; SELECT CONCAT(msc,'_',CONVERT(DATE_FORMAT(DATE_ADD(iDate, interval -1 day ),'%Y%m%d'),CHAR(15))) into sufixY; CREATE TEMPORARY TABLE IF NOT EXISTS tmp_seq (sequencenumber mediumint unsigned primary key, timestamp time,startofchargingdate date); CREATE TEMPORARY TABLE IF NOT EXISTS tmp_q (minr int, maxr int,cuenta int, hora varchar(50),fecha date); TRUNCATE TABLE tmp_seq; TRUNCATE TABLE tmp_q; SET @sqlStmt = CONCAT('INSERT INTO tmp_seq SELECT sequencenumber,timestamp,startofchargingdate FROM ',sufix,' WHERE startofchargingdate!=\'0000-00-00\' UNION SELECT sequencenumber,timestamp,startofchargingdate FROM ',sufixY,' WHERE hour>12 AND sequencenumber > (SELECT MIN(sequencenumber) FROM ',sufix,' WHERE hour=0) AND startofchargingdate!=\'0000-00-00\' UNION SELECT sequencenumber,timestamp,startofchargingdate FROM ',sufixT,' WHERE hour<12 AND sequencenumber<(SELECT MAX(sequencenumber) FROM ',sufix,' WHERE timestamp BETWEEN \'23:58:00\' and \'23:59:59\') AND startofchargingdate!=\'0000-00-00\' ORDER BY 1'); SELECT @sqlStmt; PREPARE stmtx FROM @sqlStmt; EXECUTE stmtx; DEALLOCATE PREPARE stmtx; SET last:= (SELECT sequencenumber FROM tmp_seq ORDER BY startofchargingdate DESC LIMIT 1); SELECT MAX(sequencenumber),MIN(sequencenumber),MAX(sequencenumber)-MIN(sequencenumber) FROM tmp_seq INTO min,max,diff; IF diff>10000000 THEN UPDATE tmp_seq SET sequenceNumber=sequenceNumber+max+1 WHERE sequenceNumber < last; END IF; BEGIN DECLARE cur1 CURSOR FOR SELECT sequencenumber,timestamp,startofchargingdate FROM tmp_seq ORDER BY 1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO curr,hora,fecha; IF NOT done THEN IF counter = 0 THEN SET ant = curr; ELSE IF curr <> ant + 1 THEN SET dif = curr-ant-1; INSERT INTO tmp_q values(ant+1,curr-1,dif,hora,fecha); END IF; SET ant = curr; END IF; SET counter = counter + 1 ; END IF; UNTIL done END REPEAT; CLOSE cur1; SELECT COUNT(*) FROM tmp_q WHERE fecha=iDate AND cuenta>2 INTO hasHole; DELETE FROM daily_queries.sequenceHoles WHERE date=iDate AND switch=msc; IF hasHole > 0 THEN INSERT INTO daily_queries.sequenceHoles (`date`,switch,`type`,`from`,`to`,difference,observations) SELECT iDate,msc,'',minr,maxr,cuenta,'' FROM tmp_q WHERE fecha=iDate AND cuenta>2 ON DUPLICATE KEY UPDATE difference=tmp_q.cuenta; ELSE INSERT INTO daily_queries.sequenceHoles (`date`,switch,`type`,`from`,`to`,difference,observations) VALUES (iDate,msc,'','','','',''); END IF; END;END|delimiter ;