MySQL query for display 0 if not records on dates

Question

I have booking table in my database , Entries will be inserted day by day.

Using all the data i want to generate an chart.

First of all check my Query

SELECT
    COUNT(b.id) as total_booking,
    DATE_FORMAT(
        b.appointment_time,
        "%d-%b-%Y"
    ) AS booking_date
FROM
    bookings AS b
GROUP BY
    b.appointment_time

And i am getting result like this.

Total Booking         Booking_date
-----------------------------------------------
 1                     07-Jun-2016
 1                     08-Jun-2016
 2                     09-Jun-2016
 1                     12-Jun-2016
 1                     13-Jun-2016
 1                     15-Jun-2016
 1                     16-Jun-2016
 1                     22-Jun-2016
 1                     25-Jun-2016

What i want it . i want to fill those date gap with the zero. like on the dates which i dont have booking it should display as 0.

Want output like this.

Total 
Booking   Booking_date

0        01-Jun-2016
0        02-Jun-2016
0        03-Jun-2016
0        03-Jun-2016
0        04-Jun-2016
0        05-Jun-2016
0        06-Jun-2016
1        07-Jun-2016
1        08-Jun-2016
0        09-Jun-2016
0        10-Jun-2016
0        11-Jun-2016
1        12-Jun-2016
1        13-Jun-2016
0        14-Jun-2016
1        15-Jun-2016
1        16-Jun-2016
0        17-Jun-2016
0        18-Jun-2016
0        19-Jun-2016
0        20-Jun-2016
0        21-Jun-2016
1        22-Jun-2016
0        23-Jun-2016
0        24-Jun-2016
1        25-Jun-2016
0        26-Jun-2016
0        27-Jun-2016
0        28-Jun-2016
0        29-Jun-2016
0        30-Jun-2016

Any hints ? ?

You need create a calendar table or use procedure to get date range — splash58, Jun 25 '16 at 10:50
This is a bit tricky because your source table is completely missing certain days. One option is to create a dates table which contains the missing data. Have a look [here](http://stackoverflow.com/questions/10132024/how-to-populate-a-table-with-a-range-of-dates) for how to do this. — Tim Biegeleisen, Jun 25 '16 at 10:51
Generally, issues of data display are best handled in a presentation layer/application level code (e.g. a bit of PHP) — Strawberry, Jun 25 '16 at 10:52
what is the minimum date and maximum date you want ? how can we derived it from available data on the booking table in your example you get it from 1 to 30 of that month — Mahesh Madushanka, Jun 25 '16 at 10:53
@MaheshMadushanka Min date will be starting date of the current month and max date will be last date of the current month which i will get through php. — Punit Gajjar, Jun 25 '16 at 10:54
@TimBiegeleisen , i dont have knowledge of procedures, Can you help me with the query only ? — Punit Gajjar, Jun 25 '16 at 11:02
There is no helping with the query only. You don't have data which you want to display, and it needs to be generated somehow. — Tim Biegeleisen, Jun 25 '16 at 11:03

score 0 · Answer 1 · answered Jun 25 '16 at 11:23

here is one example how you can do it. this demo find out the min and max date of you table and calculate the days between. It works only up to 999 days, but you can extend it

SELECT
  COUNT(b.id) AS total_booking
  , DATE_FORMAT(
        md,
        "%d-%b-%Y"
    ) AS booking_date
FROM (
  SELECT (SELECT min(appointment_time) FROM bookings) + INTERVAL t.x DAY AS md
  FROM 
  (
      SELECT t*100+h*10+th AS x FROM
      (SELECT 0 th UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
      SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) A,
      (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
      SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) B,
      (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
      SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) C
    ORDER BY x
  ) t
  WHERE x <= (
    SELECT DATEDIFF(max(appointment_time),min(appointment_time)) AS cnt_days FROM bookings
  ) 
) AS date_range
LEFT JOIN bookings b ON date_range.md = b.appointment_time
GROUP BY
    date_range.md;

SAMPLE

my Table

MariaDB [yourSchema]> select * from bookings;
+----+------------------+
| id | appointment_time |
+----+------------------+
|  1 | 2016-01-01       |
|  1 | 2016-01-02       |
|  1 | 2016-01-02       |
|  1 | 2016-01-09       |
+----+------------------+
4 rows in set (0.00 sec)

execute the query

MariaDB [yourSchema]> SELECT
    ->   COUNT(b.id) AS total_booking
    ->   , DATE_FORMAT(
    ->         md,
    ->         "%d-%b-%Y"
    ->     ) AS booking_date
    -> FROM (
    ->   SELECT (SELECT min(appointment_time) FROM bookings) + INTERVAL t.x DAY AS md
    ->   FROM
    ->   (
    ->       SELECT t*100+h*10+th AS x FROM
    ->       (SELECT 0 th UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
    ->       SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) A,
    ->       (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
    ->       SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) B,
    ->       (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
    ->       SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) C
    ->     ORDER BY x
    ->   ) t
    ->   WHERE x <= (
    ->     SELECT DATEDIFF(max(appointment_time),min(appointment_time)) AS cnt_days FROM bookings
    ->   )
    -> ) AS date_range
    -> LEFT JOIN bookings b ON date_range.md = b.appointment_time
    -> GROUP BY
    ->     date_range.md;
+---------------+--------------+
| total_booking | booking_date |
+---------------+--------------+
|             1 | 01-Jan-2016  |
|             2 | 02-Jan-2016  |
|             0 | 03-Jan-2016  |
|             0 | 04-Jan-2016  |
|             0 | 05-Jan-2016  |
|             0 | 06-Jan-2016  |
|             0 | 07-Jan-2016  |
|             0 | 08-Jan-2016  |
|             1 | 09-Jan-2016  |
+---------------+--------------+
9 rows in set (0.00 sec)

MariaDB [yourSchema]>

score 0 · Answer 2 · answered Jun 25 '16 at 11:27

Create DIM DATE TABLE FOR THE DATE RANGE YOU WANT

CREATE  TABLE IF NOT EXISTS `DIM_DATE` (   `DATE_KEY` INT(11) NOT NULL AUTO_INCREMENT,   `DATE_VALUE` DATE NULL ,   PRIMARY KEY (`DATE_KEY`)  )ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

fOLLOWING sp will load date for that date range

DELIMITER $$ 

CREATE PROCEDURE sp_Load_DIM_DATE(p_start_date DATE, p_end_date DATE)
BEGIN
    DECLARE v_full_date DATE;
    DECLARE v_end_date DATE;
    SET v_full_date = p_start_date;
    SET v_end_date = p_end_date;
    WHILE v_full_date < v_end_date DO

        INSERT INTO DIM_DATE ( DATE_VALUE) VALUES (v_full_date);

        SET v_full_date = DATE_ADD(v_full_date, INTERVAL 1 DAY);
    END WHILE;
END $$

DELIMITER ;

then load date range to it using start and end date

CALL  sp_Load_DIM_DATE('2016-05-01',DATE(now()));

SELECT * FROM DIM_DATE;
+----------+------------+
| DATE_KEY | DATE_VALUE |
+----------+------------+
|        1 | 2016-05-01 |
|        2 | 2016-05-02 |
|        3 | 2016-05-03 |
|        4 | 2016-05-04 |
|        5 | 2016-05-05 |
|        6 | 2016-05-06 |
|        7 | 2016-05-07 |
|        8 | 2016-05-08 |
|        9 | 2016-05-09 |
|       10 | 2016-05-10 |
|       11 | 2016-05-11 |
|       12 | 2016-05-12 |
|       13 | 2016-05-13 |
|       14 | 2016-05-14 |
------------------------
-------------------------

|       52 | 2016-06-21 |
|       53 | 2016-06-22 |
|       54 | 2016-06-23 |
|       55 | 2016-06-24 |

Then you can get it using DIM_DATE LEFT JOIN bookings table on date value

@Punit I think this will help you if you want any clarification please comment — Mahesh Madushanka, Jun 25 '16 at 11:29

MySQL query for display 0 if not records on dates

2 Answers2