date      | total
-----------------
2018-03-19| 0
2018-03-19| 0

CREATE PROCEDURE `SP_Dashboard_getTransactionsPerDay`(STARTDATE DATE,ENDDATE DATE)
BEGIN


set @i := -1;

SELECT
  DATE(ADDDATE(STARTDATE, INTERVAL @i:=@i+1 DAY)) AS date,
  IFNULL(
      (
        SELECT
          COUNT(*) FROM event AS m2
        WHERE
          DATE(m2.created_at) = DATE(ADDDATE(STARTDATE, INTERVAL @i DAY))
          AND `m2`.`status` = 'ok'
      ),
      0) AS total
FROM
  event AS m1;
HAVING
  @i < DATEDIFF(ENDDATE, STARTDATE);
END

CREATE TABLE `event` (
  `id` int(15) NOT NULL AUTO_INCREMENT,
  `status` enum('new','ok','error') COLLATE utf8_bin NOT NULL DEFAULT 'new',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- -----------------------------------------------------
-- Table `dates`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dates`;

CREATE TABLE `dates` (
  `date` DATE NOT NULL,
  PRIMARY KEY (`date`)
)
ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COLLATE = utf8_bin;

-- -----------------------------------------------------
-- StoredProcedure `SP_filldates`
-- -----------------------------------------------------
DROP PROCEDURE IF EXISTS SP_filldates;

DELIMITER $$
CREATE PROCEDURE SP_filldates(STARTDATE DATE, ENDDATE DATE)
  BEGIN
    WHILE STARTDATE <= ENDDATE DO
      INSERT INTO dates (date) VALUES (STARTDATE);
      SET STARTDATE = date_add(STARTDATE, INTERVAL 1 DAY);
    END WHILE;
  END;
$$
DELIMITER ;

CALL SP_filldates('2018-01-01', '2018-12-31');

-- -----------------------------------------------------
-- StoredProcedure `SP_Dashboard_getTransactionsPerDay`
-- -----------------------------------------------------
DROP PROCEDURE
IF EXISTS SP_Dashboard_getTransactionsPerDay;

DELIMITER $$


CREATE PROCEDURE SP_Dashboard_getTransactionsPerDay(
  STARTDATE DATE,
  ENDDATE   DATE
)
  BEGIN

    SET @i := -1;

    SELECT
      DATE(ADDDATE(STARTDATE, INTERVAL @i := @i + 1 DAY)) AS date,
      IFNULL(
          (
            SELECT COUNT(*)
            FROM event AS m2
            WHERE
              DATE(m2.created_at) = DATE(ADDDATE(STARTDATE, INTERVAL @i DAY))
              AND `m2`.`status` = 'ok'
          ),
          0
      ) AS total
    FROM
      dates AS m1
    HAVING
      @i < DATEDIFF(ENDDATE, STARTDATE);

  END$$

DELIMITER ;