CODEWITHSUNDEEP
mysqldate
Leonardo
Leonardo

Reputation: 1363

Get every missing date between date range

I have a table with a date field that varies from month to month, for example:

id    import_date   name ...
1     2017-02-01    Leonardo
7     2017-03-01    Alison
8     2017-06-01    Juliet
11    2017-09-01    Laura

Every first day in month we import some data to this table. As you can see, some register from January, April, May, July and August are missing, in this example.

How can I get only the missing dates between a start and end date range?

Upvotes: 0

Views: 42

Answers (1)

Leonardo
Leonardo

Reputation: 1363

That's what I've done:

SELECT data_series.import_date FROM 
(
    SELECT DATE_ADD(:startDate, INTERVAL @i :=@i + 1 MONTH) AS import_date 
        FROM imports, (SELECT @i := -1) i, 
        (SELECT MIN(import_date) as minVal, MAX(import_date) as maxVal 
    FROM imports WHERE import_date BETWEEN :startDate AND :endDate) AS smallerSet 
    WHERE @i < DATEDIFF(smallerSet.maxVal, smallerSet.minVal)
) AS data_series 
LEFT JOIN 
(
    SELECT DISTINCT import_date FROM imports 
    WHERE reference_date BETWEEN :startDate AND :endDate
) AS import_period 
ON data_series.import_date = import_period.import_date 
WHERE import_period.import_date IS NULL

where :startDate and :endDate are variables I'm using in Spring Boot.

Upvotes: 0

Related Questions