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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users