2018-08-05 0:18 GMT+02:00 Keith Medcalf <[email protected]>:
>
> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
