SJA6Tfhm

1How to find missing rows (dates) in a mysql table?
2date           userid
32011-10-01     1
42011-10-02     1
5(missing)
62011-10-04     1
72011-10-05     1
8(missing)
92011-10-07     1
10	
11DROP PROCEDURE IF EXISTS FillDateTable;
12
13delimiter //
14CREATE PROCEDURE FillDateTable()
15    LANGUAGE SQL
16    NOT DETERMINISTIC
17    CONTAINS SQL
18    SQL SECURITY DEFINER
19    COMMENT ''
20BEGIN
21  drop table if exists datetable;
22  create table datetable (thedate datetime primary key, isweekday smallint);
23
24  SET @x := date('2000-01-01');
25  REPEAT 
26    insert into datetable (thedate, isweekday) SELECT @x, case when dayofweek(@x) in (1,7) then 0 else 1 end;
27    SET @x := date_add(@x, interval 1 day);
28    UNTIL @x >= '2030-12-31' END REPEAT;
29END//
30delimiter ;
31
32CALL FillDateTable;
33	
34SELECT thedate
35FROM datetable
36LEFT JOIN posts on posts.date = datetable.thedate
37WHERE posts.date IS NULL
38	
39SELECT thedate
40FROM datetable
41INNER JOIN (select min(date) postStart, max(date) postEnd
42            FROM posts
43            where userid=123) p on datetable.thedate BETWEEN p.postStart and p.postEnd
44LEFT JOIN posts on posts.date = datetable.thedate
45WHERE posts.date IS NULL
46	
47select c.cal_date
48from calendar c
49left join posts p on (c.cal_date = p.date)
50where p.date is null
51  and c.cal_date between '2011-10-01' and '2011=10-31'
52  and p.userid = 1
53order by c.cal_date