Generate Dates starting from a date returned by a condition - SQL

Question

A series of dates with a specified interval can be generated using a variable and a static date as per the linked question that I asked earlier. However when there's a where clause to produce a start date, the dates generation seems to stop and only shows the first interval date. I also checked other posts, those that I found e.g. 1, e.g. 2, e.g. 3 are shown with a static date or using CTE.. I am looking for a solution without storedprocedures/functions...

This works:

SELECT DATE(DATE_ADD('2012-01-12', 
INTERVAL @i:=@i+30 DAY) ) AS dateO
FROM members, (SELECT @i:=0) r
where @i < DATEDIFF(now(), date '2012-01-12') 
;

These don't:

SELECT DATE_ADD(date '2012-01-12', 
INTERVAL @j:=@j+30 DAY) AS dateO, @j
FROM `members`, (SELECT @j:=0) s
where @j <= DATEDIFF(now(), date '2012-01-12') 
and mmid = 100
;

SELECT DATE_ADD(stdate, 
INTERVAL @k:=@k+30 DAY) AS dateO, @k
FROM `members`, (SELECT @k:=0) t
where @k <= DATEDIFF(now(), stdate)
and mmid = 100
;

• SQLFIDDLE REFERENCE

Expected Results:

Be the same as the first query results given it starts generating dates with stDate of mmid=100.

Preferably in ANSI SQL so it can be supported in MYSQL, SQL Server/MS Access SQL as Oracle has trunc and rownum given per this query with 14 votes and PostGres has generatge_Series function. I would like to know if this is a bug or a limitation in MYSQL?

---

PS: I have asked a similar quetion before. It was based on static date values where as this one is based on a date value from a table column based on a condition.

Answer 1

The simplest way to insure cross-platform compatibility is to use a calendar table. In its simplest form

create table calendar (
  cal_date date primary key
);

insert into calendar values
('2013-01-01'),
('2013-01-02'); -- etc.

There are many ways to generate dates for insertion.

Instead of using a WHERE clause to generate rows, you use a WHERE clause to select rows. To select October of this year, just

select cal_date
from calendar
where cal_date between '2013-10-01' and '2013-10-31';

It's reasonably compact--365,000 rows to cover a period of 1000 years. That ought to cover most business scenarios.

If you need cross-platform date arithmetic, you can add a tally column.

drop table calendar;
create table calendar (
  cal_date date primary key,
  tally integer not null unique check (tally > 0)
);

insert into calendar values ('2012-01-01', 1); -- etc.

To select all the dates of 30-day intervals, starting on 2012-01-12 and ending at the end of the calendar year, use

select cal_date
from calendar
where ((tally - (select tally
                 from calendar
                 where cal_date = '2012-01-12')) % 30 ) = 0;

cal_date
--
2012-01-12
2012-02-11
2012-03-12
2012-04-11
2012-05-11
2012-06-10
2012-07-10
2012-08-09
2012-09-08
2012-10-08
2012-11-07
2012-12-07

If your "mmid" column is guaranteed to have no gaps--an unspoken requirement for a calendar table--you can use the "mmid" column in place of my "tally" column.