x1DeyMJy

1date      | total
2-----------------
32018-03-19| 0
42018-03-19| 0
5	
6CREATE PROCEDURE `SP_Dashboard_getTransactionsPerDay`(STARTDATE DATE,ENDDATE DATE)
7BEGIN
8
9
10set @i := -1;
11
12SELECT
13  DATE(ADDDATE(STARTDATE, INTERVAL @i:=@i+1 DAY)) AS date,
14  IFNULL(
15      (
16        SELECT
17          COUNT(*) FROM event AS m2
18        WHERE
19          DATE(m2.created_at) = DATE(ADDDATE(STARTDATE, INTERVAL @i DAY))
20          AND `m2`.`status` = 'ok'
21      ),
22      0) AS total
23FROM
24  event AS m1;
25HAVING
26  @i < DATEDIFF(ENDDATE, STARTDATE);
27END
28	
29CREATE TABLE `event` (
30  `id` int(15) NOT NULL AUTO_INCREMENT,
31  `status` enum('new','ok','error') COLLATE utf8_bin NOT NULL DEFAULT 'new',
32  `created_at` datetime NOT NULL,
33  `updated_at` datetime DEFAULT NULL,
34  PRIMARY KEY (`id`)
35) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
36	
37-- -----------------------------------------------------
38-- Table `dates`
39-- -----------------------------------------------------
40DROP TABLE IF EXISTS `dates`;
41
42CREATE TABLE `dates` (
43  `date` DATE NOT NULL,
44  PRIMARY KEY (`date`)
45)
46ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COLLATE = utf8_bin;
47	
48-- -----------------------------------------------------
49-- StoredProcedure `SP_filldates`
50-- -----------------------------------------------------
51DROP PROCEDURE IF EXISTS SP_filldates;
52
53DELIMITER $$
54CREATE PROCEDURE SP_filldates(STARTDATE DATE, ENDDATE DATE)
55  BEGIN
56    WHILE STARTDATE <= ENDDATE DO
57      INSERT INTO dates (date) VALUES (STARTDATE);
58      SET STARTDATE = date_add(STARTDATE, INTERVAL 1 DAY);
59    END WHILE;
60  END;
61$$
62DELIMITER ;
63
64CALL SP_filldates('2018-01-01', '2018-12-31');
65	
66-- -----------------------------------------------------
67-- StoredProcedure `SP_Dashboard_getTransactionsPerDay`
68-- -----------------------------------------------------
69DROP PROCEDURE
70IF EXISTS SP_Dashboard_getTransactionsPerDay;
71
72DELIMITER $$
73
74
75CREATE PROCEDURE SP_Dashboard_getTransactionsPerDay(
76  STARTDATE DATE,
77  ENDDATE   DATE
78)
79  BEGIN
80
81    SET @i := -1;
82
83    SELECT
84      DATE(ADDDATE(STARTDATE, INTERVAL @i := @i + 1 DAY)) AS date,
85      IFNULL(
86          (
87            SELECT COUNT(*)
88            FROM event AS m2
89            WHERE
90              DATE(m2.created_at) = DATE(ADDDATE(STARTDATE, INTERVAL @i DAY))
91              AND `m2`.`status` = 'ok'
92          ),
93          0
94      ) AS total
95    FROM
96      dates AS m1
97    HAVING
98      @i < DATEDIFF(ENDDATE, STARTDATE);
99
100  END$$
101
102DELIMITER ;