Clever Geek Handbook

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?

Thank!

+3


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


+1


source


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
WHERE NOT EXISTS (
   SELECT 1
   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.

0


source


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 (d1.id+1) =d2.id and d1.endDate < d2.startDate;
0


source






More articles:


All Articles