Reputation: 137
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
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