2018-08-05 0:18 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>: > > WITH RECURSIVE > dates(dateD) AS (VALUES(:StartDate) > UNION ALL > SELECT date(dateD, '+1 year') > FROM dates > WHERE date(dateD, '+1 year') <= :EndDate > ) > SELECT max(dateD), count(*) FROM dates;
How do I interpret the ':StartDate' and ':EndDate'? Should I replace for example the ':StartDate' with '1983-07-11' like this: sqlite> WITH RECURSIVE dates(dateD) AS (VALUES('1983-07-11') UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD, '+1 year') <= '1984-08-31' ) SELECT max(dateD), count(*) FROM dates; 1984-07-11|2 But this is ot what I want, because between 1983-07-11 and 1984-08-31 there is exactly one whole year out there, namely: from 1983-07-11 to 1984-07-11. So the result value '2' above is wrong in the sense that there is 1 year and not 2 years out there. The following SQL commands gives to me the right output, what I desired: sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1983-07-11','+1 year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD, '+1 year') <= '1984-08-31' ) SELECT max(dateD), count(*) FROM dates; 1984-07-11|1 sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1984-11-01','+1 year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD, '+1 year') <= '1986-01-15' ) SELECT max(dateD), count(*) FROM dates; 1985-11-01|1 sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1986-01-16','+1 year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD, '+1 year') <= '1999-07-16' ) SELECT max(dateD), count(*) FROM dates; 1999-01-16|13 but not in the following case: sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('2000-02-01','+1 year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD, '+1 year') <= '2000-08-31' ) SELECT max(dateD), count(*) FROM dates; 2001-02-01|1 because here in the last example there should be '0' and not '1' in the output of that command. I am still trying to find the proper way to do this, what I desired, if it is possible at all. -- Best, Pali _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users