How to put zero or null in a SQL query of a date range, for those whose value does not exist?

Question :

Formulate the question was a bit difficult, but I’ll try to be as explicit as possible using an example to set the question straight.

There are the following data in my table:

Thepurposeistoexecuteaquerysql(theDBMSusedisMySQL)inthedaterangebetween2014-09-01until2014-09-10andhavethefollowingresult:

What I already have:

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

Select the data in the DB, by means of between of the desired dates and save to a list;

For each day (or time) existing in the range of the desired date, save to an auxiliary variable (an instance of Calendar in Java);

Check if there is an element with this date in the list obtained in step 1, if not, add an element in the resulting list with that date, but zero in the quantity field;

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

Answer :

You can use a generator for no intermediate tables: / p>

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.

Operation:

Each subquery generates the numbers 0 through 9, except the last one, which generates numbers from 0 to 3;

The formula d1+d2*10+d3*100 transforms the outputs of the 3 subqueries into a number of 0 to 399

With DATE_ADD( '2014-01-01', INTERVAL d1+d2*10+d3*100 DAY ) we’ve turned this number into a date.

To limit to the days of the year in question, we use the same formula that generates the date, to ensure that we are no longer than one year (remembering that the number of days varies in leap years).

How to use:

As described in other answers, just use 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

Cold.
As the Wakin suggested and as I was writing.

Make a date table, and from there a LEFT JOIN with your table.

Here are two links to suggestions for creating this table:

Generate Table Dates 1

Generate Table Dates 2

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

You can create a PROCEDURE by creating a temporary table before running 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 ;

Data

--------------------------------------
| 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');

Conducting the query

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);

Result:

--------------------------------------
| 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        |
--------------------------------------

Question :

Answer :

Leave a Reply Cancel reply