I have a table for bugs from a bug tracking software

I have a table for bugs from a bug tracking software; let’s call the table “bugs”. The table has four columns (id, open_date, close_date, severity). On any given day a bug is open if the open_date is on or before that day and close_date is after that day. For example, a bug is open on “2012-01-01”, if it’s created on or before “2012-01-01” and closed on or after “2012-01-02”. I want an SQL to show the number of bugs open each day for a range of dates. Hint: There are bugs that were never closed.
=======================================================================
CREATE TABLE bugs (
  id INT,
  severity INT,
  open_date DATE,
  close_date DATE
);

INSERT INTO bugs VALUES
  (
    1, 1,
    STR_TO_DATE('2015-12-31', '%Y-%m-%d'),
    STR_TO_DATE('2015-12-31', '%Y-%m-%d')
  ),
  (
    2, 1,
    STR_TO_DATE('2016-01-01', '%Y-%m-%d'),
    STR_TO_DATE('2016-01-02', '%Y-%m-%d')
  ),
  (
    3, 1,
    STR_TO_DATE('2016-01-03', '%Y-%m-%d'),
    STR_TO_DATE('2016-01-03', '%Y-%m-%d')
  ),
  (
    4, 1,
    STR_TO_DATE('2016-01-03', '%Y-%m-%d'),
    STR_TO_DATE('2016-01-04', '%Y-%m-%d')
  ),
  (
    5, 1,
    STR_TO_DATE('2016-01-06', '%Y-%m-%d'),
    STR_TO_DATE('2016-01-07', '%Y-%m-%d')

  );



CREATE PROCEDURE search (start DATE, end DATE)
BEGIN
  DECLARE result VARCHAR(255);
  SET @result = '';

  SET @iterDate = start;
  label1: LOOP
     IF @iterDate = start THEN
       SET @result = CONCAT(@result, ' SELECT * FROM bugs WHERE open_date <= DATE(\'',@iterDate,'\') AND close_date = DATE(\'',@iterDate,'\') UNION ALL');
     ELSEIF @iterDate = end THEN
       SET @result = CONCAT(@result, ' SELECT * FROM bugs WHERE open_date <= DATE(\'',@iterDate,'\') AND close_date >= DATE(\'',@iterDate,'\') UNION ALL');
     ELSE
       SET @result = CONCAT(@result, ' SELECT * FROM bugs WHERE open_date <= DATE(\'',@iterDate,'\') AND close_date = DATE(\'',@iterDate,'\') UNION ALL');
     END IF;


     SET @iterDate = DATE_ADD(@iterDate, INTERVAL 1 DAY);
     IF @iterDate <= end THEN
        ITERATE label1;
     END IF;
     LEAVE label1;
   END LOOP label1;

   SET @result = LEFT(@result, LENGTH(@result)-LENGTH('UNION ALL'));

   PREPARE stmt FROM @result;
   EXECUTE stmt;
END

CALL search(
  STR_TO_DATE('2016-01-01', '%Y-%m-%d'),

  STR_TO_DATE('2016-01-03', '%Y-%m-%d'));

Share on Google Plus

About Admin

Arun is a JAVA/J2EE developer and passionate about coding and managing technical team.