Jits Jits - 3 months ago 7
MySQL Question

Print ZERO if date record doesn't match between two date range

I have start_date and end_date of the data for e.g. start_date as 2016-07-10 and end_date as 2016-07-25. I want all the records between these two dates by addition of two fields along with ZERO values but i'm not getting date records which doesn't exists in the table.

SQL Fiddle: http://sqlfiddle.com/#!9/c8dab/9

DDLs:

CREATE TABLE `bugs` (`bug_id` int(11) NOT NULL, `bug_date` date NOT NULL, `cf1` int(11) NOT NULL, `cf2` int(11) NOT NULL, `bug_status` varchar(200) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `bugs` (`bug_id`, `bug_date`, `cf1`, `cf2`, `bug_status`) VALUES (101, '2016-07-19', 3, 2, 'RESOLVED'), (102, '2016-07-19', 2, 1, 'VERIFIED'), (103, '2016-07-19', 2, 1, 'VERIFIED'), (103, '2016-07-19', 2, 1, 'VERIFIED'), (1363, '2016-07-19', 2, 1, 'VERIFIED'), (1352, '2016-07-19', 2, 1, 'VERIFIED'), (102, '2016-07-19', 2, 1, 'VERIFIED'), (102, '2016-07-22', 2, 2, 'CLOSED'), (103, '2016-07-22', 2, 2, 'CLOSED'), (103, '2016-07-22', 2, 2, 'CLOSED'), (102, '2016-07-19', 3, 2, 'NEW'), (102, '2016-07-19', 2, 1, 'REOPENED'), (102, '2016-07-19', 2, 1, 'CLOSED'), (102, '2016-07-19', 2, 1, 'VERIFIED'), (1363, '2016-07-19', 2, 1, 'VERIFIED'), (1352, '2016-07-19', 2, 1, 'VERIFIED'), (565, '2016-07-19', 2, 1, 'VERIFIED'), (398, '2016-07-22', 2, 2, 'CLOSED'), (565, '2016-07-22', 2, 2, 'CLOSED'), (9872, '2016-07-22', 2, 2, 'CLOSED');


Query:

SELECT DATE_FORMAT(convert_tz(bugs.bug_date,@@session.time_zone,'+05:30'), '%Y-%m-%d') as date, SUM(bugs.cf1 + bugs.cf2) as count FROM bugs


WHERE (bugs.bug_date BETWEEN '2016-07-10' AND '2016-07-25')
GROUP BY bugs.bug_date;

Actual output:

date | count
2016-07-19 | 46
2016-07-22 | 24


Expected output:

date | count
2016-07-10 | 0
2016-07-11 | 0
2016-07-12 | 0
2016-07-13 | 0
2016-07-14 | 0
2016-07-15 | 0
2016-07-16 | 0
2016-07-17 | 0
2016-07-18 | 0
2016-07-19 | 46
2016-07-20 | 0
2016-07-21 | 0
2016-07-22 | 24
2016-07-23 | 0
2016-07-24 | 0
2016-07-25 | 0


I have gone through couple of related posts but i'm not getting real answer by trying similar solutions so posting it for very specific issue if someone can suggest me.

FYI.. I can't create new table like suggested in some posts, DB access is read only for me.

Please help me out, comments appreciated.

Answer

I have achieved this through pragmatically (middle layer), after getting query output -> Append date wise query data to array.

for ($i=0; $i <= $noOfDays; $i++) {
  $date = date('Y-m-d', strtotime($startDate . ' +' . $i . ' day'));
  //this will add one day in startdate and add logic for $somevalue accordingly
  $rows[] = array($date, $somevalue);
}
Comments