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;
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Saturday, 4 August, 2018 14:36
>To: SQLite mailing list
>Subject: Re: [sqlite] Using CTE with date comparison
>
>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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
