2018-08-04 14:07 GMT+02:00 Csányi Pál <csanyi...@gmail.com>: > 2018-08-03 13:09 GMT+02:00 R Smith <ryansmit...@gmail.com>: > >> Do you want a recursive query that will add all years between 3 October and >> some other date, but NOT the first year and NOT the last year? > > I want a recursive query that gives years to the start date until it > reaches the end date or if it exceeds the end date, the last added > date is valid. Meanwhile, it should report how many times it has added > a year. Its output is the date of the last added year and the number > of additions. Is this possible?
I think I found the solution. Here is two SQL statement with different dates, and it seems to me that that it do it right, no? sqlite> WITH RECURSIVE dates(dateD) AS ( SELECT date('1983-07-11','+1 year') UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD) <= date('1984-08-31') ) SELECT date(dateD), count(*) FROM dates WHERE dateD <= '1984-08-31'; 1984-07-11|1 sqlite> WITH RECURSIVE dates(dateD) AS ( SELECT date('1984-11-01','+1 year') UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD) <= date('1986-01-15') ) SELECT date(dateD), count(*) FROM dates WHERE dateD <= '1986-01-15'; 1985-11-01|1 -- Best, Pali _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users