Getting missing dates from sql table

I have a mysql table with rows: ID, name, startDate, endDate.

Generally, dates should be consistent and I want to alert the user if the gap is missing.

Saying that these dates are inserted:

2012-03-25 -> 2012-03-29
2012-04-02 -> 2012-04-05


I want to show a message like

"No dates found from 2012-03-29 to 2012-04-02. Please insert data for this interval"


Is it possible to do this without surfing with php of all table records?



source to share

3 answers

SELECT t1.endDate AS gapStart, (SELECT MIN(t3.startDate) FROM `table` t3 WHERE t3.startDate > t1.endDate) AS gapEnd
FROM `table` t1
LEFT JOIN `table` t2
    ON t1.endDate = t2.startDate
WHERE t2.startDate IS NULL




Yes, it can be done without looking at the entire table using PHP, instead you will have to look at the entire table using mysql. The crude solution will:

SELECT a.enddate AS start_of_gap, 
(SELECT MIN(c.startdate)
 FROM yourtable c
 WHERE c.startdate>a.enddate) AS end_of_gap
FROM yourtable a
   FROM yourtable b
   WHERE b.startdate=a.enddate + INTERVAL 1 DAY


I expect if I think about it more, there will be a more efficient (but probably less obvious) method.



It works. I am including code to create a table and insert data for testing purposes.

 create table dates(
id int(11) not null auto_increment,
name varchar(16) not null,
startDate date,
endDate date,
primary key(id)

insert into dates (name,startDate,endDate) 
values('personA', '2012-03-25', '2012-03-29'),
      ('PersonB','2012-04-02', '2012-04-05');


So here's the request:

 select d1.endDate,d2.startDate 
 from dates d1, dates d2 
 where ( and d1.endDate < d2.startDate;




All Articles