How do you set zero or null in an SQL query of a date range, for those whose value does not exist?

Question

Formulating the question was a little difficult, but I will try to be as explicit as possible using an example to put the question.

There are the following data in my table:

The aim is to carry out a consultation sql (the DBMS used is Mysql) in the date range 2014-09-01 until 2014-09-10 and have the following result:

What I already have:

To be honest, I have no idea how to do this, so far I have solved this in the control of my application, an algorithm like:

1. Select the data in the DB, by means of a between of the desired dates and store in a list;
2. For each day (or time) existing in the desired date range, store in an auxiliary variable (an instance of Calendar in Java);
3. Check if In the list obtained in point 1 there is an element with this date, if there is not, add an element in the resulting list with this date, but zero in the quantity field;

Is there any way to do this already in consultation on sql? The way I do it now is not the most efficient.

Answer 1

You can use a generator of dates not to need intermediate tables:

SELECT
   DATE_ADD( '2014-01-01', INTERVAL d1+d2*10+d3*100 DAY ) AS diaDoAno
FROM
   ( SELECT 0 AS d1 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 ) t1,
   ( SELECT 0 AS d2 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 ) t2,
   ( SELECT 0 AS d3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 ) t3
WHERE
   YEAR( DATE_ADD( '2014-01-01', INTERVAL d1+d2*10+d3*100 DAY ) ) = 2014

The query looks great, but the performance is excellent.

Functioning:

1. Each subquery generates the numbers from 0 to 9, except the last one, which generates numbers from 0 to 3;
2. the formula d1+d2*10+d3*100 transforms the outputs of the 3 Subqueries in a number of 0 to 399
3. with DATE_ADD( '2014-01-01', INTERVAL d1+d2*10+d3*100 DAY ) we turn this number into a date.
4. To limit it to the days of the year in question, we use the same formula that generates the date, to ensure that we do not exceed the interval of one year (remembering that the number of days varies in leap years).

How to use:

As described in other answers, just use a JOIN with the desired table.

SELECT
   DATE_ADD( '2014-01-01', INTERVAL d1+d2*10+d3*100 DAY ) AS dia_do_ano.
   contador,
   quantidade
FROM
   ( SELECT 0 AS d1 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 ) t1,
   ( SELECT 0 AS d2 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 ) t2,
   ( SELECT 0 AS d3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 ) t3
LEFT JOIN
    sua_tabela ON dia_do_ano = data_tempo
WHERE
    YEAR( DATE_ADD( '2014-01-01', INTERVAL d1+d2*10+d3*100 DAY ) ) = 2014

Answer 2

Cold. As Wakin suggested and as I was writing.

Make a schedule of dates, and from this one LEFT JOIN with your table.

Here are two links with suggestions for creating this table:

Generate Table Dates 1

Generate Table Dates 2

For the values null, just add in your COUNT the verification IFNULL() and your problem will be solved.

Answer 3

You can create a PROCEDURE creating a temporary table before executing the query, for example:

DELIMITER //
CREATE PROCEDURE TempDateTable(data_ini DATE, data_fim DATE)

BEGIN
    DECLARE v_curdate DATE;
    SET @v_curdate = data_ini;
    START TRANSACTION;
    DROP TABLE IF EXISTS tempdatetable;

    CREATE TEMPORARY TABLE tempdatetable  (
        data_tempo DATE NOT NULL PRIMARY KEY
    );

  WHILE @v_curdate <= data_fim DO
    INSERT INTO `TempDateTable`(`data_tempo`) VALUES (@v_curdate);
    SET @v_curdate = ADDDATE(@v_curdate, INTERVAL 1 DAY);
  END WHILE;
  COMMIT;
END//
DELIMITER ;

Dice

--------------------------------------
| data_tempo | contador | quantidade |
--------------------------------------
| 2014-09-01 | CDR_SUC  | 256        |
| 2014-09-04 | CDR_SUC  | 258        |
| 2014-09-05 | CDR_SUC  | 195        |
| 2014-09-06 | CDR_SUC  | 100        |
| 2014-09-10 | CDR_SUC  | 317        |
--------------------------------------

Running Procedure

CALL `databasetest`.`TempDateTable`('2014-09-01', '2014-09-10');

Performing the consultation

SELECT tmp.data_tempo, test.contador, IFNULL(test.quantidade,0) 
FROM tempdatetable as tmp 
    LEFT JOIN  testedados as  test ON DATE(test.data_tempo) = DATE(tmp.data_tempo);

Upshot:

--------------------------------------
| data_tempo | contador | quantidade |
--------------------------------------
| 2014-09-01 | CDR_SUC  | 256        |
| 2014-09-02 | NULL     | 0          |
| 2014-09-03 | NULL     | 0          |
| 2014-09-04 | CDR_SUC  | 258        |
| 2014-09-05 | CDR_SUC  | 195        |
| 2014-09-06 | CDR_SUC  | 100        |
| 2014-09-07 | NULL     | 0          |
| 2014-09-08 | NULL     | 0          |
| 2014-09-09 | NULL     | 0          |
| 2014-09-10 | CDR_SUC  | 317        |
--------------------------------------