On Sun, Aug 05, 2018 at 01:12:17PM -0600, Keith Medcalf wrote:
>
> >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 output of code above is:
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 2004-02-01 2004-02-01 2004-02-02 2004-02-02 0 0
1
which is wrong because the StarDate and EndDate are the same:
'2004-02-02'. The output should be zero '0' in this case.
I tried this SQL command for three cases. The output of two cases are
the expected, but one is not. See bellow.
1983-07-11' - '1984-08-31'
^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite> WITH RECURSIVE dates (StartDate, EndDate) as ( select '1983-07-11',
'1984-08-31' ), 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
---------- ---------- ---------- ---------- ---------- ----------
---------- ----------
1983-07-11 1984-07-10 1984-08-10 1984-08-31 1984-08-31 1 1
21
This is the expected output.
'1984-11-01' - '1986-01-15'
^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite> WITH RECURSIVE dates (StartDate, EndDate) as ( select '1984-11-01',
'1986-01-15' ), 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
---------- ---------- ---------- ---------- ---------- ----------
---------- ----------
1984-11-01 1985-10-31 1986-01-01 1986-01-15 1986-01-15 1 2
14
This is not the expected output.
The expected output would be 1 years, 2 months, but 15 days instead.
Why gives it 14 days instead?
'1986-01-16' - '1999-07-16'
^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite> WITH RECURSIVE dates (StartDate, EndDate) as ( select '1986-01-16',
'1999-07-16' ), 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
---------- ---------- ---------- ---------- ---------- ----------
---------- ----------
1986-01-16 1999-01-15 1999-07-15 1999-07-16 1999-07-16 13 6
1
This is the expected output.
So I am asking: why is the output of above examples in two cases as I
am expecting, but in other two cases is not?
The expected outputs is good only if does match that value which I am
calculate manually, without SQL query, by using logic that I described
earlier in this thread.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
