CODEWITHSUNDEEP
mysqldatedatediff
GabrielCol
GabrielCol

Reputation: 181

Getting the missing dates from a sql table

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

As a rule, the dates should be consecutive and i want to alert the user if an interval is missing.

Saying i have this dates inserted:

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

I wanna show a message like 

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

Can this be done without surfing with php the entire table entries?

Thanks!

Upvotes: 3

Views: 1472

Answers (3)

symcbean
symcbean

Reputation: 48357

Yes, it can be done without surfing the whole table using PHP, instead you have to surf the whole table using mysql. A crude solution would be:

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 thought about it some more, there will be a more efficient (but likely less obvious) method.

Upvotes: 0

kasavbere
kasavbere

Reputation: 6003

This works. I include code for creating table and inserting 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 is the query:

 select d1.endDate,d2.startDate 
 from dates d1, dates d2 
 where (d1.id+1) =d2.id and d1.endDate < d2.startDate;

Upvotes: 0

user1191247
user1191247

Reputation: 12998

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

Upvotes: 1

Related Questions