>Some where in the WITH clause above I want to put '+1 day' in the
>command out there.
That is because the query does not count the StartDate but does count the
EndDate, so if your EndDate is the next day from the StartDate you get 1 day,
not two. You need to move the fencepost created by the StartDate to counteract
this (so that, effectively, the starting point becomes the day before the first
day, or "day 0") ... This makes the dateY / dateM reflect the date on which, at
the completion of that day, the year or month respectively was completed:
WITH RECURSIVE
dates (StartDate, EndDate) as
(
select '2004-02-02', '2004-02-02'
),
yearC (dateY) AS
(
SELECT date(StartDate, '+1 year', '-1 day')
FROM dates
WHERE date(StartDate, '+1 year', '-1 day') <= EndDate
UNION ALL
SELECT date(dateY, '+1 year')
FROM yearC, dates
WHERE date(dateY, '+1 year') <= EndDate
),
years (dateY, years) as
(
SELECT coalesce((SELECT max(dateY)
FROM yearC), date(StartDate, '-1 day')),
coalesce((SELECT count(*)
FROM yearC), 0)
FROM dates
),
monthC (dateM) as
(
SELECT date(dateY, '+1 month')
FROM years, dates
WHERE date(dateY, '+1 month') <= EndDate
UNION ALL
SELECT date(dateM, '+1 month')
FROM monthC, dates
WHERE date(dateM, '+1 month') <= EndDate
),
months (dateM, months) as
(
SELECT coalesce((SELECT max(dateM)
FROM monthC), dateY),
coalesce((SELECT count(*)
FROM monthC), 0)
FROM years
),
dayC (dateD) as
(
SELECT date(dateM, '+1 day')
FROM months, dates
WHERE date(dateM, '+1 day') <= EndDate
UNION ALL
SELECT date(dateD, '+1 day')
FROM dayC, dates
WHERE date(dateD, '+1 day') <= EndDate
),
days (dateD, days) as
(
SELECT coalesce((SELECT max(dateD)
FROM dayC), DateM),
coalesce((SELECT count(*)
FROM dayC), 0)
FROM months
)
SELECT StartDate,
DateY,
DateM,
DateD,
EndDate,
years,
months,
days
FROM dates, years, months, days;
StartDate dateY dateM dateD EndDate years months
days
---------- ---------- ---------- ---------- ---------- ----------
---------- ----------
2004-02-02 2018-02-01 2018-08-01 2018-08-03 2018-08-03 14 6
2
---
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 pali
>Sent: Sunday, 5 August, 2018 07:35
>To: SQLite mailing list
>Subject: Re: [sqlite] Using CTE with date comparison
>
>On Sun, Aug 05, 2018 at 05:25:02AM -0600, Keith Medcalf wrote:
>>
>> :StartDate and :EndDate are NAMED PARAMETERS for when your
>application executes the statement (that is, they are substituted
>with the values you want for the StartDate and EndDate respectively.
>
>I understand now. Thank you!
>
>> I take it you want to compute YEARS MONTHS and DAYS between two
>dates:
>
>Exactly that is what I want.
>
>> WITH RECURSIVE
>> dates (StartDate, EndDate) as
>> (
>> select '2004-02-02', '2018-08-03'
>> ),
>> yearC (dateY) AS
>> (
>> SELECT date(StartDate, '+1 year')
>> FROM dates
>> WHERE date(StartDate, '+1 year') <= EndDate
>> UNION ALL
>> SELECT date(dateY, '+1 year')
>> FROM yearC, dates
>> WHERE date(dateY, '+1 year') <= EndDate
>> ),
>> years (dateY, years) as
>> (
>> SELECT coalesce((SELECT max(dateY)
>> FROM yearC), StartDate),
>> coalesce((SELECT count(*)
>> FROM yearC), 0)
>> FROM dates
>> ),
>> monthC (dateM) as
>> (
>> SELECT date(dateY, '+1 month')
>> FROM years, dates
>> WHERE date(dateY, '+1 month') <= EndDate
>> UNION ALL
>> SELECT date(dateM, '+1 month')
>> FROM monthC, dates
>> WHERE date(dateM, '+1 month') <= EndDate
>> ),
>> months (dateM, months) as
>> (
>> SELECT coalesce((SELECT max(dateM)
>> FROM monthC), dateY),
>> coalesce((SELECT count(*)
>> FROM monthC), 0)
>> FROM years
>> ),
>> dayC (dateD) as
>> (
>> SELECT date(dateM, '+1 day')
>> FROM months, dates
>> WHERE date(dateM, '+1 day') <= EndDate
>> UNION ALL
>> SELECT date(dateD, '+1 day')
>> FROM dayC, dates
>> WHERE date(dateD, '+1 day') <= EndDate
>> ),
>> days (dateD, days) as
>> (
>> SELECT coalesce((SELECT max(dateD)
>> FROM dayC), DateM),
>> coalesce((SELECT count(*)
>> FROM dayC), 0)
>> FROM months
>> )
>> SELECT StartDate,
>> DateY,
>> DateM,
>> DateD,
>> EndDate,
>> years,
>> months,
>> days
>> FROM dates, years, months, days;
>>
>> StartDate dateY dateM dateD EndDate years
>months days
>> ---------- ---------- ---------- ---------- ---------- -------
>--- ---------- ----------
>> 2004-02-02 2018-02-02 2018-08-02 2018-08-03 2018-08-03 14
>6 1
>
>Thank you very much!
>That's what I wanted, albeit with a small addition: I should get one
>day more.
>
>This is due the fact that when calculated a month, days which belongs
>to a month are as follows, e.g. for a month which is 31 days long:
>1.,
>2., 3., ..., 30., 31.
>
>Some where in the WITH clause above I want to put '+1 day' in the
>command out there.
>
>I am going to study this command which I want to use in a Trigger.
>Thank you very much again for the solution!
>
>--
>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
