USE 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 int ; DECLARE dif int ; DECLARE curr int ; DECLARE hora time; DECLARE fecha date; DECLARE diff INT; DECLARE minZeroSeq, tmp_max, max, min INT; DECLARE minZero time; 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 int primary key, timestamp time key,startofchargingdate date key); 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 MIN(timestamp),MIN(sequenceNumber) INTO minZero, minZeroSeq FROM tmp_seq WHERE sequenceNumber BETWEEN 0 AND 10; IF minZero IS NOT NULL THEN--FIND THE MAX SELECT MAX(sequencenumber) INTO tmp_max FROM tmp_seq WHERE timestamp < minZero; SELECT MAX(sequencenumber) INTO max FROM tmp_seq WHERE sequenceNumber >= tmp_max;--FIND THE MIN SELECT sequencenumber INTO min FROM tmp_seq WHERE date=iDate ORDER BY timestamp DESC LIMIT 1; END IF; IF minZero IS NULL THEN BEGIN DECLARE cur1 CURSOR FOR SELECT sequencenumber,timestamp,startofchargingdate FROM tmp_seq ORDER BY 1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; END; ELSE BEGIN DECLARE cur1 CURSOR FOR (SELECT sequencenumber,timestamp,startofchargingdate FROM tmp_seq WHERE sequencenumber BETWEEN min AND max ORDER BY 1) UNION ALL (SELECT sequencenumber,timestamp,startofchargingdate FROM tmp_seq WHERE sequencenumber >= minZeroSeq AND sequencenumber < min ORDER BY 1); DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; END; END IF; 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|delimiter ;