DELIMITER //

CREATE OR REPLACE PROCEDURE compareMeterReads (IN starting_date datetime)
       BEGIN
       
      	/*Declare the variables*/
      	DECLARE first_date_val DATETIME;
      	DECLARE ser_num_val VARCHAR(25);
        DECLARE last_date_val DATETIME;
        /*DECLARE aSql VARCHAR(200);*/
        
        /*When this = 1, the fetch will be done*/
        DECLARE exit_fetch INT DEFAULT 0;
      
      	/*Declare the cursor.*/
		DECLARE results_cursor  CURSOR FOR
       
      	SELECT MIN(created_date) first_date, serial_number
        FROM `y2019_meters`
        WHERE created_date > starting_date AND serial_number != ''
        GROUP BY serial_number;
        
        /*'2018-06-30 23:59:59'*/
    
		/*Declare NOT FOUND handler*/
		DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET exit_fetch = 1;
        
        
        /*Open the cursor*/
        OPEN results_cursor;
        
        
        getData: LOOP
        
            /*Get the results and assign the values to first_date_val and ser_num_val*/
        	FETCH results_cursor INTO first_date_val, ser_num_val;
            
            /*SET @serNum = ser_num;*/
            
            /*Get last_date*/
            SELECT MIN(created_date) last_date
            FROM `y2019_meters`
            WHERE created_date > first_date_val AND serial_number = ser_num_val
            GROUP BY serial_number;
            
                      
            
            SET @ser_num = ser_num_val;
            /*SET first_date= '2021-06-30 23:59:59';
            SET last_date = '2021-07-01 23:59:59';*/
            
            
            /*Insert all bad values, along with the serial_number and corresponding date_timestamps, into the table*/
          	SET @aSql = 'INSERT INTO meter_read_comparisons (serial_number, first_date, last_date, bad_value_black, bad_value_color) VALUES( ?, ?, ?, 35, 99)';
            
            PREPARE stmt FROM @aSql;             
            
            EXECUTE stmt USING @ser_num_val, first_date_val, last_date_val;
            DEALLOCATE PREPARE stmt;
            
          
          	IF exit_fetch = 1 THEN
           		LEAVE getData;
           	END IF; 
          
		END LOOP getData; 
            
        
        /*Close the cursor*/
        CLOSE results_cursor;	   
		
         
       END

DELIMITER ;


CALL compareReads('2018-12-14 08:24:28');

SELECT * FROM meter_read_comparisons;













 /*Open the cursor that will return the black and color meter values for first and last meter reads
            for the current serial_number*/
       		OPEN volumes_cursor;
            
            
            getVolumes: LOOP
            
            /*Get the results and assign the values to first_date_val and ser_num_val*/        	           
            FETCH volumes_cursor INTO black_meter_val, color_meter_val, meter_read_order;
            
           /* IF (meter_read_order = 'first') THEN
            	SET first_black_meter = black_meter_val;
        		SET first_color_meter = color_meter_val;               
                
            ELSE
        		SET last_black_meter = black_meter_val;
       			SET last_color_meter = color_meter_val;
            END IF;*/
               
            END LOOP getVolumes; */
            
            
            /*Close the cursor*/
        	CLOSE volumes_cursor;