Neha Arora Neha Arora - 1 month ago 10
SQL Question

Combine the result of multiple SQL select queries

I have a table where I have fields as open_date and a close_date. I have to create a procedure for which, given a range of dates as input, I show all the records for each date that lies within the range, the records whose open_date>date and closed_date

for example if my table is

id open_date closed_date
1 2016-06-06 2016-06-10
2 2016-06-08 2016-06-11


and the range of dates is 2016-06-07 to 2016-06-09

then the result should be

Date Count
2016-06-07 1
2016-06-08 2
2016-06-09 2


But the result that I am getting is

Date Count
2016-06-09 2


The procedure that I have written down is given below.

CREATE DEFINER=`root`@`localhost` PROCEDURE `find_number_of_bugs`(IN startDate DateTIme, IN endDate DateTime)
BEGIN
Declare testDate Date;
SET testDate=startDate;
while testDate<=endDate DO
select testDate as Dates ,count(bugID) as Number_Of_Bugs_Open from bugs where testDate between open_date and close_date OR close_date=null;
SET testDate=DATE_ADD(testDate,INTERVAL 1 DAY);
END while;
END


I guess I have to combine the results from the multiple select statements that
run throgh the while loop. Please suggest me a way to do that.

Answer

You are gutting all the results but in multiple result sets. You can save all query results in a temporary table and query it once at the end.

CREATE DEFINER=`root`@`localhost` PROCEDURE `find_number_of_bugs`(IN startDate DateTIme, IN endDate DateTime)
BEGIN
Declare testDate Date;
SET testDate=startDate;

CREATE TEMPORARY TABLE output (Dates date,Number_Of_Bugs_Open int);

while testDate<=endDate DO

insert into output select testDate as Dates ,count(bugID) as Number_Of_Bugs_Open from bugs where testDate between open_date and close_date OR close_date=null;

SET testDate=DATE_ADD(testDate,INTERVAL 1 DAY);
END while;

select * from output;

END