Join on all dates within the range MySQL

Question

I have the following table :

administratieID        |     varchar
getalTypeNaam          |     varchar
datum                  |     date
waarde                 |     double

With the following query:

                                Select administratieID AS AI, waarde WA, datum DT
                                from getalType
                                where getalTypeNaam = 'test'
                                and datum between DATE_SUB(NOW(), INTERVAL 10 DAY)  and DATE_SUB(NOW(), INTERVAL 15 DAY) 
                                order by datum

What I want is that I can do a select where all the dates between the specified dates are returned even if there is nothing in the database that coresponds with that date. I want this even if there is no value but in that case i want to return the value 0.

So I want this in return:

date       administratieID waarde
10-10-2016 xxx                 10
11-10-2016 xxx                  0  <-- no data available
12-10-2016 xxx                 40
13-10-2016 xxx                  9
14-10-2016 xxx                  0  <-- no data available

Now I get this:

date       administratieID waarde
10-10-2016 xxx                 10
12-10-2016 xxx                 40
13-10-2016 xxx                  9

What did you get currently and i would change the params for between. Thing you are checking actual e.g. between 2017-01-20 and 2017-01-15 `expr [NOT] BETWEEN begin_expr AND end_expr;` _The BETWEEN operator returns true if the value of the expr is greater than or equal to (>=) the value of begin_expr and less than or equal to (<= ) the value of the end_expr otherwise it returns zero._ — JustOnUnderMillions, Jan 25 '17 at 15:13
Handle the logic of missing results in your application code. — Strawberry, Jan 25 '17 at 15:13
@Strawberry Yeah I probably should but this would have made it much easier for how I did set things up. Well I guess I have to rewrite it a bit. — Joey, Jan 25 '17 at 15:18
Do what @Strawberry say. Iterate over the result and add missing rows with waarde 0. This can not made whit sql. If `11-10-2016` is not in the tabel, it can not be selected. — JustOnUnderMillions, Jan 25 '17 at 15:21

score 1 · Accepted Answer · answered Jan 25 '17 at 15:42

There are ways to do this, but they are quite hackish...

One would be to have an inline incrementing variable, and use a dummy table that you know has at least as many records in it as the maximum number of days in your date range. Then you can do something like this:

SET @date = DATE_SUB(NOW(), INTERVAL 15 DAY);  #or whatever the earliest date you need is



SELECT DISTINCT
      IFNULL(administratieID,'XXX') AS AI
    , IFNULL(waarde,0) AS WA
    , all_dates AS DT
FROM 
     (SELECT @date:=DATE_ADD(@date, INTERVAL 1 DAY) AS all_dates
     FROM
    my_dummy_table_with_lots_of_records
    WHERE @date >= DATE_SUB(NOW(), INTERVAL 15 DAY) AND @date <= DATE_SUB(NOW(), INTERVAL 10 DAY)) d LEFT JOIN

    getalType t ON
         DATE_FORMAT(all_dates,'%Y-%m-%d') = DATE_FORMAT(t.datum,'%Y-%m-%d')   
WHERE
    IFNULL(getalTypeNaam, 'Test') = 'Test'  #if there's no data we still need to return something

ORDER BY all_dates;

Another easier way is just to have a table called "all_dates", which you can use in the join. The table would just have one column, "datum" and you populate it with every possible date that could be used in the getalType table. This could even be done with a trigger.

Then you link to this table, instead of doing all the derived nonsense above.

This would also avoid having to have multiple sql statements, as you don't have a variable that needs initialising.

By the way in case anyone thinks I'm nuts, I tested the above sql and it does return the desired dataset. It is not theoretical. — Simon Woolf, Jan 25 '17 at 15:47
Also a very similar problem and solution here: https://stackoverflow.com/questions/1046865/mysql-select-all-dates-in-a-range-even-if-no-records-present?rq=1 — Simon Woolf, Jan 25 '17 at 16:45
Thank you it actually works and I even somewhat understands why.. ( somewhat ) — Joey, Jan 26 '17 at 09:15

Join on all dates within the range MySQL

1 Answers1