DELIMITER $$

USE `temp_tables`$$

DROP PROCEDURE IF EXISTS `processorPending`$$

CREATE DEFINER=`root`@`%` PROCEDURE `processorPending`()
BEGIN

CREATE TABLE tmpproviderpending(provider_id BIGINT, file_name VARCHAR(255) NOT NULL);
CREATE INDEX `IX_DBA` ON tmpproviderpending(provider_id,file_name);


INSERT INTO tmpproviderpending
SELECT provider_id,
       MAX(file_name) AS max_file_name
  FROM processor_sample
 WHERE rec_engine = 'PENDING'
 GROUP BY provider_id
 HAVING COUNT(*) > 1 AND COUNT(DISTINCT file_name) >1;
 
DROP TABLE IF EXISTS tmpproviderpending;
	END$$

DELIMITER ;