2018-08-04 14:07 GMT+02:00 Csányi Pál <[email protected]>:
> 2018-08-03 13:09 GMT+02:00 R Smith <[email protected]>:
>
>> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
