CODEWITHSUNDEEP
mysqlsqldatabase
Victor
Victor

Reputation: 137

Mysql Find missing date ranges

I have a table with date ranges:

    create table d (
    date_start date,
    date_end date
    );

    insert into d values('2014-03-05', '2014-04-10');
    insert into d values('2014-05-01', '2014-06-05');
    insert into d values('2014-07-10', '2014-08-15');

I want to complete this table with missing date ranges in 2014. In this case that would be: 

    2014-01-01 - 2014-03-04
    2014-04-11 - 2014-04-30
    2014-06-06 - 2014-07-09
    2014-08-16 - 2014-12-31

Any mysql query suggestions?

Edit

Better use these values:

    create table d (
        date_start date,
        date_end date
        );

        insert into d values('2014-06-01', '2014-06-30');
        insert into d values('2014-07-01', '2014-08-03');
        insert into d values('2014-09-01', '2014-09-30');

Edit 2

I am almost there with this:

    SELECT
            date_start,
            date_end

    FROM

    (SELECT 
      ends.point AS date_start,
      starts.point AS date_end
    FROM 
      (SELECT 
        d.date_end+INTERVAL 1 DAY AS point, 
        @n:=@n+1 AS num 
      FROM 
        d 
          CROSS JOIN (SELECT @n:=1) AS init0
          ORDER BY date_start
      ) AS ends 
      INNER JOIN 
      (SELECT 
        d.date_start-INTERVAL 1 DAY AS point, 
        @m:=@m+1 AS num 
      FROM 
        d 
          CROSS JOIN (SELECT @m:=0) AS init1
          ORDER BY date_start
      ) AS starts 
      ON ends.num=starts.num
    UNION ALL 
      SELECT 
      '2014-01-01', 
        MIN(date_start) - INTERVAL 1 DAY 
      FROM 
        d 
      WHERE 
        date_start>='2014-01-01'
    UNION ALL 
      SELECT 
        MAX(date_end)+INTERVAL 1 DAY, 
        '2014-12-31'
      FROM
        d 
      WHERE 
        date_end <= '2014-12-31'
    ) as dates
    WHERE
      date_start < date_end
    ORDER BY 
      date_start;

However, this query returns wrong results for the following intervals set:

    create table d (date_start date, date_end date);

    insert into d values('2014-01-01', '2014-01-09');
    insert into d values('2014-01-10', '2014-03-15');
    insert into d values('2014-03-16', '2014-04-20');
    insert into d values('2014-05-01', '2014-07-30');
    insert into d values('2014-08-01', '2014-09-30');
    insert into d values('2014-12-25', '2014-12-31');

It seems it cannot handle single days like 2014-07-31 - 2014-07-31.

Upvotes: 2

Views: 940

Answers (1)

Alma Do
Alma Do

Reputation: 37365

In case if your date intervals will not be nested or intersecting, you can use trick with JOIN to produce result set. So, to select desired record set, you'll need:

SELECT
  *
FROM
(SELECT 
  ends.point AS date_start, 
  starts.point AS date_end 
FROM 
  (SELECT 
    d.date_end+INTERVAL 1 DAY AS point, 
    @n:=@n+1 AS num 
  FROM 
    d 
      CROSS JOIN (SELECT @n:=1) AS init0
  ) AS ends 
  INNER JOIN 
  (SELECT 
    d.date_start-INTERVAL 1 DAY AS point, 
    @m:=@m+1 AS num 
  FROM 
    d 
      CROSS JOIN (SELECT @m:=0) AS init1
  ) AS starts 
  ON ends.num=starts.num 
UNION ALL 
  SELECT 
    '2014-01-01', 
    MIN(date_start) - INTERVAL 1 DAY AS date_end
  FROM 
    d 
  HAVING 
    date_end>'2014-01-01' 
UNION ALL 
  SELECT 
    MAX(date_end)+INTERVAL 1 DAY AS date_start, 
    '2014-12-31' 
  FROM
    d 
  HAVING 
    date_start<'2014-12-31' 
) as dates
WHERE
  date_start<=date_end
ORDER BY 
  date_start;

that will result in

+------------+------------+
| date_start | date_end   |
+------------+------------+
| 2014-01-01 | 2014-03-04 |
| 2014-04-11 | 2014-04-30 |
| 2014-06-06 | 2014-07-09 |
| 2014-08-16 | 2014-12-31 |
+------------+------------+

(fiddle for this is here)

To "complete" your table you may use INSERT..SELECT syntax with SELECT query above.

Upvotes: 5

Related Questions