Description:
The following query
SELECT Broker
, year(MM_YY)
, sum(Less35)
, sum(GreaterThan35Less45)
, sum(GreaterThan45)
FROM
report_survey
GROUP BY
Broker
, year(MM_YY);
does not work correctly in a stored procedure when preceded by the code shown in how to repeat section to populate the table it uses. It appears to return the last couple of records.
If the stored procedure just contains some simple INSERT INTO statements to populate the table the GROUP BY statement works correctly.
E.g.
TRUNCATE TABLE report_survey;
INSERT INTO report_survey VALUES (0, 'AA', '2010-11-01', 1, 1, 1);
INSERT INTO report_survey VALUES (0, 'AA', '2010-12-01', 1, 0, 1);
INSERT INTO report_survey VALUES (0, 'AA', '2011-08-01', 3, 1, 1);
INSERT INTO report_survey VALUES (0, 'AA', '2011-10-01', 1, 3, 1);
INSERT INTO report_survey VALUES (0, 'BB', '2010-11-01', 1, 1, 1);
INSERT INTO report_survey VALUES (0, 'BB', '2010-12-01', 1, 0, 1);
INSERT INTO report_survey VALUES (0, 'BB', '2011-08-01', 3, 1, 1);
INSERT INTO report_survey VALUES (0, 'BB', '2011-10-01', 1, 3, 1);
INSERT INTO report_survey VALUES (0, 'CC', '2010-11-01', 1, 1, 1);
INSERT INTO report_survey VALUES (0, 'CC', '2010-12-01', 1, 0, 1);
INSERT INTO report_survey VALUES (0, 'CC', '2011-08-01', 3, 1, 1);
INSERT INTO report_survey VALUES (0, 'CC', '2011-10-01', 1, 3, 1);
SELECT Broker
, year(MM_YY)
, sum(Less35)
, sum(GreaterThan35Less45)
, sum(GreaterThan45)
FROM
report_survey
GROUP BY
Broker
, year(MM_YY);
I can only assume the more complex code to populate the table in the stored procedure is having an affect on the GROUP BY statement.
How to repeat:
Create a suitable table with following structure,
CREATE TABLE report_survey
(
Id INT(10) PRIMARY KEY AUTO_INCREMENT NOT NULL,
Broker VARCHAR(100),
MM_YY DATE,
Less35 INT,
GreaterThan35Less45 INT,
GreaterThan45 INT
);
Use a version of the stored procedure below to populate the report_survey table.
BEGIN
DECLARE Less35 INT;
DECLARE GreaterThan35Less45 INT;
DECLARE GreaterThan45 INT;
DECLARE src_reference INT;
DECLARE submit_date_time DATETIME;
DECLARE survey_conducted_date_time DATETIME;
DECLARE date_diff INT;
DECLARE db_broker TEXT;
DECLARE db_mm TEXT;
DECLARE db_yy TEXT;
DECLARE broker TEXT;
DECLARE mmyy TEXT;
DECLARE eof INT;
DECLARE cur1 CURSOR FOR
SELECT c.company_name AS 'broker' -- VARCHAR(100)
, a.src_reference -- INT(10)
, b.submit_date_time -- DATETIME
, month(b.submit_date_time) AS 'mm' -- INT
, year(b.submit_date_time) AS 'yy' -- INT
, a.survey_conducted_date_time -- DATETIME
FROM
survey a
JOIN survey_request b
ON b.src_reference = a.src_reference
JOIN contacts_companies c
ON c.reference = b.broker_company_reference
WHERE
a.survey_conducted_date_time IS NOT NULL
ORDER BY
c.company_name
, b.submit_date_time;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET eof = 1;
SET Less35 = 0;
SET GreaterThan35Less45 = 0;
SET GreaterThan45 = 0;
SET broker = '';
SET mmyy = '';
TRUNCATE TABLE report_survey;
OPEN cur1;
SET eof = 0;
WHILE eof = 0
DO
FETCH cur1 INTO db_broker, src_reference, submit_date_time, db_mm, db_yy, survey_conducted_date_time;
IF broker <> db_broker THEN
SET broker = db_broker;
SET mmyy = '';
END IF;
IF mmyy <> concat(db_mm, '-', db_yy) THEN
IF mmyy <> '' THEN
INSERT INTO report_survey VALUES (0, broker, str_to_date(concat('1-', mmyy), '%d-%m-%Y'), Less35, GreaterThan35Less45, GreaterThan45);
END IF;
SET mmyy = concat(db_mm, '-', db_yy);
SET Less35 = 0;
SET GreaterThan35Less45 = 0;
SET GreaterThan45 = 0;
END IF;
SET date_diff = datediff(survey_conducted_date_time, submit_date_time);
IF date_diff <= 35 THEN
SET Less35 = Less35 + 1;
END IF;
IF date_diff > 35 AND date_diff <= 45 THEN
SET GreaterThan35Less45 = GreaterThan35Less45 + 1;
END IF;
IF date_diff > 45 THEN
SET GreaterThan45 = GreaterThan45 + 1;
END IF;
END WHILE;
CLOSE cur1;
SELECT Broker
, year(MM_YY)
, sum(Less35)
, sum(GreaterThan35Less45)
, sum(GreaterThan45)
FROM
report_survey
GROUP BY
Broker
, year(MM_YY);
END
Suggested fix:
Nothing to suggest.
Description: The following query SELECT Broker , year(MM_YY) , sum(Less35) , sum(GreaterThan35Less45) , sum(GreaterThan45) FROM report_survey GROUP BY Broker , year(MM_YY); does not work correctly in a stored procedure when preceded by the code shown in how to repeat section to populate the table it uses. It appears to return the last couple of records. If the stored procedure just contains some simple INSERT INTO statements to populate the table the GROUP BY statement works correctly. E.g. TRUNCATE TABLE report_survey; INSERT INTO report_survey VALUES (0, 'AA', '2010-11-01', 1, 1, 1); INSERT INTO report_survey VALUES (0, 'AA', '2010-12-01', 1, 0, 1); INSERT INTO report_survey VALUES (0, 'AA', '2011-08-01', 3, 1, 1); INSERT INTO report_survey VALUES (0, 'AA', '2011-10-01', 1, 3, 1); INSERT INTO report_survey VALUES (0, 'BB', '2010-11-01', 1, 1, 1); INSERT INTO report_survey VALUES (0, 'BB', '2010-12-01', 1, 0, 1); INSERT INTO report_survey VALUES (0, 'BB', '2011-08-01', 3, 1, 1); INSERT INTO report_survey VALUES (0, 'BB', '2011-10-01', 1, 3, 1); INSERT INTO report_survey VALUES (0, 'CC', '2010-11-01', 1, 1, 1); INSERT INTO report_survey VALUES (0, 'CC', '2010-12-01', 1, 0, 1); INSERT INTO report_survey VALUES (0, 'CC', '2011-08-01', 3, 1, 1); INSERT INTO report_survey VALUES (0, 'CC', '2011-10-01', 1, 3, 1); SELECT Broker , year(MM_YY) , sum(Less35) , sum(GreaterThan35Less45) , sum(GreaterThan45) FROM report_survey GROUP BY Broker , year(MM_YY); I can only assume the more complex code to populate the table in the stored procedure is having an affect on the GROUP BY statement. How to repeat: Create a suitable table with following structure, CREATE TABLE report_survey ( Id INT(10) PRIMARY KEY AUTO_INCREMENT NOT NULL, Broker VARCHAR(100), MM_YY DATE, Less35 INT, GreaterThan35Less45 INT, GreaterThan45 INT ); Use a version of the stored procedure below to populate the report_survey table. BEGIN DECLARE Less35 INT; DECLARE GreaterThan35Less45 INT; DECLARE GreaterThan45 INT; DECLARE src_reference INT; DECLARE submit_date_time DATETIME; DECLARE survey_conducted_date_time DATETIME; DECLARE date_diff INT; DECLARE db_broker TEXT; DECLARE db_mm TEXT; DECLARE db_yy TEXT; DECLARE broker TEXT; DECLARE mmyy TEXT; DECLARE eof INT; DECLARE cur1 CURSOR FOR SELECT c.company_name AS 'broker' -- VARCHAR(100) , a.src_reference -- INT(10) , b.submit_date_time -- DATETIME , month(b.submit_date_time) AS 'mm' -- INT , year(b.submit_date_time) AS 'yy' -- INT , a.survey_conducted_date_time -- DATETIME FROM survey a JOIN survey_request b ON b.src_reference = a.src_reference JOIN contacts_companies c ON c.reference = b.broker_company_reference WHERE a.survey_conducted_date_time IS NOT NULL ORDER BY c.company_name , b.submit_date_time; DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1; SET Less35 = 0; SET GreaterThan35Less45 = 0; SET GreaterThan45 = 0; SET broker = ''; SET mmyy = ''; TRUNCATE TABLE report_survey; OPEN cur1; SET eof = 0; WHILE eof = 0 DO FETCH cur1 INTO db_broker, src_reference, submit_date_time, db_mm, db_yy, survey_conducted_date_time; IF broker <> db_broker THEN SET broker = db_broker; SET mmyy = ''; END IF; IF mmyy <> concat(db_mm, '-', db_yy) THEN IF mmyy <> '' THEN INSERT INTO report_survey VALUES (0, broker, str_to_date(concat('1-', mmyy), '%d-%m-%Y'), Less35, GreaterThan35Less45, GreaterThan45); END IF; SET mmyy = concat(db_mm, '-', db_yy); SET Less35 = 0; SET GreaterThan35Less45 = 0; SET GreaterThan45 = 0; END IF; SET date_diff = datediff(survey_conducted_date_time, submit_date_time); IF date_diff <= 35 THEN SET Less35 = Less35 + 1; END IF; IF date_diff > 35 AND date_diff <= 45 THEN SET GreaterThan35Less45 = GreaterThan35Less45 + 1; END IF; IF date_diff > 45 THEN SET GreaterThan45 = GreaterThan45 + 1; END IF; END WHILE; CLOSE cur1; SELECT Broker , year(MM_YY) , sum(Less35) , sum(GreaterThan35Less45) , sum(GreaterThan45) FROM report_survey GROUP BY Broker , year(MM_YY); END Suggested fix: Nothing to suggest.