Using CTE with date comparison

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Using CTE with date comparison

csanyipal
Hi,

I just want to know why the following SQLite query does not work as I expected?

WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
2017-10-03|3
which is not what I am expecting.

I am expecting the followings:
1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
2. then it compares the two dates: 2016-10-03 with 2016-10-03
3. because 2016-10-03 = 2016-10-03 it count 1
4. then add to the result date 2016-10-03 once again 1 year which is 2017-10-03
5. then it compares the two dates: 2017-10-03 with 2016-10-03
6. because 2017-10-03 > 2016-10-03 it does not count 2
7. it should gives the following result:
2016-10-03|1

What am I doing wrong here?

--
Best, Pali
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

R Smith-2
On 2018/08/02 10:29 PM, Csányi Pál wrote:

> Hi,
>
> I just want to know why the following SQLite query does not work as I expected?
>
> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
> date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
> 2017-10-03|3
> which is not what I am expecting.
>
> I am expecting the followings:
> 1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
> 2. then it compares the two dates: 2016-10-03 with 2016-10-03
> 3. because 2016-10-03 = 2016-10-03 it count 1
> 4. then add to the result date 2016-10-03 once again 1 year which is 2017-10-03
> 5. then it compares the two dates: 2017-10-03 with 2016-10-03
> 6. because 2017-10-03 > 2016-10-03 it does not count 2
> 7. it should gives the following result:
> 2016-10-03|1
>
> What am I doing wrong here?
>

When the recursive Query starts up, the first value that it outputs is
given by the very first part of the query, namely: VALUES('2015-10-03')
So on the first iteration, it will produce one row like this:
'2015-10-03'
regardless of the rest of the Query. This row is pushed into the
recursion buffer.

After that it then reads a row from the recursion buffer and checks
(within the WHERE clause) whether the value in it (namely: '2015-10-03')
is <= '2016-11-01', and finds that it definitely IS less, so continues
to produce the another line of output.

The output created is that date from the buffer (2015-10-03) which is
put through the given calculation: date(dateD, '+1 year') to give:
'2016-10-03'

It then continues to push that next row into the recursion buffer and
next reads again from it and again checks if it (2016-10-03) is <= than
2016-11-01, which again it is... so it continues to produce the next
output row, which after calculation becomes:
'2017-10-03'

It then continues to push that again into the buffer and again read it
and again checks if it (2017-10-03) is less than 2016-11-01, which THIS
TIME, it isn't... so it stops right there.

So in the end, it has produced 3 output rows namely:
'2015-10-03'
'2016-10-03'
'2017-10-03'

Which is exactly what you've asked for.

Note: The first part of the query will ALWAYS reach the output buffer,
even if it isn't a recursive query, and the UNION is NOT specified, you
will get at least the '2015-10-03' value.
Note: When comparing in the WHERE clause, you do not compare the newly
calculated value (date(xxx, +1 year)), but indeed you compare the
before-calculated value, i.e. the previous value in the buffer (the same
as how your calculation is done on the PREVIOUS value in the buffer to
yield the new date with.

I hope that helps to make sense.

Cheers,
Ryan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

csanyipal
2018-08-02 23:12 GMT+02:00 R Smith <[hidden email]>:

> On 2018/08/02 10:29 PM, Csányi Pál wrote:
>>
>> Hi,
>>
>> I just want to know why the following SQLite query does not work as I
>> expected?
>>
>> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
>> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
>> date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
>> 2017-10-03|3
>> which is not what I am expecting.
>>
>> I am expecting the followings:
>> 1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
>> 2. then it compares the two dates: 2016-10-03 with 2016-10-03
>> 3. because 2016-10-03 = 2016-10-03 it count 1
>> 4. then add to the result date 2016-10-03 once again 1 year which is
>> 2017-10-03
>> 5. then it compares the two dates: 2017-10-03 with 2016-10-03
>> 6. because 2017-10-03 > 2016-10-03 it does not count 2
>> 7. it should gives the following result:
>> 2016-10-03|1
>>
>> What am I doing wrong here?
>>
>
> When the recursive Query starts up, the first value that it outputs is given
> by the very first part of the query, namely: VALUES('2015-10-03')
> So on the first iteration, it will produce one row like this:
> '2015-10-03'
> regardless of the rest of the Query. This row is pushed into the recursion
> buffer.
>
> After that it then reads a row from the recursion buffer and checks (within
> the WHERE clause) whether the value in it (namely: '2015-10-03') is <=
> '2016-11-01', and finds that it definitely IS less, so continues to produce
> the another line of output.
>
> The output created is that date from the buffer (2015-10-03) which is put
> through the given calculation: date(dateD, '+1 year') to give:
> '2016-10-03'
>
> It then continues to push that next row into the recursion buffer and next
> reads again from it and again checks if it (2016-10-03) is <= than
> 2016-11-01, which again it is... so it continues to produce the next output
> row, which after calculation becomes:
> '2017-10-03'
>
> It then continues to push that again into the buffer and again read it and
> again checks if it (2017-10-03) is less than 2016-11-01, which THIS TIME, it
> isn't... so it stops right there.
>
> So in the end, it has produced 3 output rows namely:
> '2015-10-03'
> '2016-10-03'
> '2017-10-03'
>
> Which is exactly what you've asked for.
>
> Note: The first part of the query will ALWAYS reach the output buffer, even
> if it isn't a recursive query, and the UNION is NOT specified, you will get
> at least the '2015-10-03' value.
> Note: When comparing in the WHERE clause, you do not compare the newly
> calculated value (date(xxx, +1 year)), but indeed you compare the
> before-calculated value, i.e. the previous value in the buffer (the same as
> how your calculation is done on the PREVIOUS value in the buffer to yield
> the new date with.
>
> I hope that helps to make sense.
>
> Cheers,
> Ryan

Ryan, thank you very much for the explanation!

Then, how can I implement steps from 1 to 7 in SQLite language?

--
Best, Pali
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

csanyipal
In reply to this post by R Smith-2
2018-08-02 23:12 GMT+02:00 R Smith <[hidden email]>:

> On 2018/08/02 10:29 PM, Csányi Pál wrote:
>>
>> Hi,
>>
>> I just want to know why the following SQLite query does not work as I
>> expected?
>>
>> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
>> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
>> date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
>> 2017-10-03|3
>> which is not what I am expecting.
>>
>> I am expecting the followings:
>> 1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
>> 2. then it compares the two dates: 2016-10-03 with 2016-10-03
>> 3. because 2016-10-03 = 2016-10-03 it count 1
>> 4. then add to the result date 2016-10-03 once again 1 year which is
>> 2017-10-03
>> 5. then it compares the two dates: 2017-10-03 with 2016-10-03
>> 6. because 2017-10-03 > 2016-10-03 it does not count 2
>> 7. it should gives the following result:
>> 2016-10-03|1
>>
>> What am I doing wrong here?
>>
>
> When the recursive Query starts up, the first value that it outputs is given
> by the very first part of the query, namely: VALUES('2015-10-03')
> So on the first iteration, it will produce one row like this:
> '2015-10-03'
> regardless of the rest of the Query. This row is pushed into the recursion
> buffer.
>
> After that it then reads a row from the recursion buffer and checks (within
> the WHERE clause) whether the value in it (namely: '2015-10-03') is <=
> '2016-11-01', and finds that it definitely IS less, so continues to produce
> the another line of output.
>
> The output created is that date from the buffer (2015-10-03) which is put
> through the given calculation: date(dateD, '+1 year') to give:
> '2016-10-03'
>
> It then continues to push that next row into the recursion buffer and next
> reads again from it and again checks if it (2016-10-03) is <= than
> 2016-11-01, which again it is... so it continues to produce the next output
> row, which after calculation becomes:
> '2017-10-03'
>
> It then continues to push that again into the buffer and again read it and
> again checks if it (2017-10-03) is less than 2016-11-01, which THIS TIME, it
> isn't... so it stops right there.
>
> So in the end, it has produced 3 output rows namely:
> '2015-10-03'
> '2016-10-03'
> '2017-10-03'
>
> Which is exactly what you've asked for.
>
> Note: The first part of the query will ALWAYS reach the output buffer, even
> if it isn't a recursive query, and the UNION is NOT specified, you will get
> at least the '2015-10-03' value.
> Note: When comparing in the WHERE clause, you do not compare the newly
> calculated value (date(xxx, +1 year)), but indeed you compare the
> before-calculated value, i.e. the previous value in the buffer (the same as
> how your calculation is done on the PREVIOUS value in the buffer to yield
> the new date with.
>
> I hope that helps to make sense.
>
> Cheers,
> Ryan

I think then that that the following SQL query gives to me the desired result:
WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
date('2016-11-01') ) SELECT max(dateD), count(dateD)-2 FROM dates;

count(dateD)-2 does the math. Right?

--
Best, Pali
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

R Smith-2


On 2018/08/03 12:35 PM, Csányi Pál wrote:

> 2018-08-02 23:12 GMT+02:00 R Smith <[hidden email]>:
>> On 2018/08/02 10:29 PM, Csányi Pál wrote:
>>> Hi,
>>>
>>> I just want to know why the following SQLite query does not work as I
>>> expected?
>>>
>>> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
>>> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
>>> date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
>>> 2017-10-03|3
>>> which is not what I am expecting.
>>>
>>> I am expecting the followings:
>>> 1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
>>> 2. then it compares the two dates: 2016-10-03 with 2016-10-03
>>> 3. because 2016-10-03 = 2016-10-03 it count 1
>>> 4. then add to the result date 2016-10-03 once again 1 year which is
>>> 2017-10-03
>>> 5. then it compares the two dates: 2017-10-03 with 2016-10-03
>>> 6. because 2017-10-03 > 2016-10-03 it does not count 2
>>> 7. it should gives the following result:
>>> 2016-10-03|1
>>>
>>> What am I doing wrong here?
>>>
>> When the recursive Query starts up, the first value that it outputs is given
>> by the very first part of the query, namely: VALUES('2015-10-03')
>> So on the first iteration, it will produce one row like this:
>> '2015-10-03'
>> regardless of the rest of the Query. This row is pushed into the recursion
>> buffer.
>>
>> After that it then reads a row from the recursion buffer and checks (within
>> the WHERE clause) whether the value in it (namely: '2015-10-03') is <=
>> '2016-11-01', and finds that it definitely IS less, so continues to produce
>> the another line of output.
>>
>> The output created is that date from the buffer (2015-10-03) which is put
>> through the given calculation: date(dateD, '+1 year') to give:
>> '2016-10-03'
>>
>> It then continues to push that next row into the recursion buffer and next
>> reads again from it and again checks if it (2016-10-03) is <= than
>> 2016-11-01, which again it is... so it continues to produce the next output
>> row, which after calculation becomes:
>> '2017-10-03'
>>
>> It then continues to push that again into the buffer and again read it and
>> again checks if it (2017-10-03) is less than 2016-11-01, which THIS TIME, it
>> isn't... so it stops right there.
>>
>> So in the end, it has produced 3 output rows namely:
>> '2015-10-03'
>> '2016-10-03'
>> '2017-10-03'
>>
>> Which is exactly what you've asked for.
>>
>> Note: The first part of the query will ALWAYS reach the output buffer, even
>> if it isn't a recursive query, and the UNION is NOT specified, you will get
>> at least the '2015-10-03' value.
>> Note: When comparing in the WHERE clause, you do not compare the newly
>> calculated value (date(xxx, +1 year)), but indeed you compare the
>> before-calculated value, i.e. the previous value in the buffer (the same as
>> how your calculation is done on the PREVIOUS value in the buffer to yield
>> the new date with.
>>
>> I hope that helps to make sense.
>>
>> Cheers,
>> Ryan
> I think then that that the following SQL query gives to me the desired result:
> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
> date('2016-11-01') ) SELECT max(dateD), count(dateD)-2 FROM dates;
>
> count(dateD)-2 does the math. Right?
>

That depends on what the desired result is. Do you want to count how
many years elapsed (in full) since 3 October 2015?  Count()-2 is the
worst hack for this (unless the question is specifically "what is 2 less
than the number of years between x and y....").

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?

Maybe if you explain what is the question you are asking, and perhaps
provide 2 examples, the one you already done is fine, but give another
one where the dates are from 2015-10-03 to 2025-11-01 or such, how must
that date-range be answered by the query?



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

csanyipal
2018-08-03 13:09 GMT+02:00 R Smith <[hidden email]>:

>
>
> On 2018/08/03 12:35 PM, Csányi Pál wrote:
>>
>> 2018-08-02 23:12 GMT+02:00 R Smith <[hidden email]>:
>>>
>>> On 2018/08/02 10:29 PM, Csányi Pál wrote:
>>>>
>>>> Hi,
>>>>
>>>> I just want to know why the following SQLite query does not work as I
>>>> expected?
>>>>
>>>> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
>>>> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
>>>> date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
>>>> 2017-10-03|3
>>>> which is not what I am expecting.
>>>>
>>>> I am expecting the followings:
>>>> 1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
>>>> 2. then it compares the two dates: 2016-10-03 with 2016-10-03
>>>> 3. because 2016-10-03 = 2016-10-03 it count 1
>>>> 4. then add to the result date 2016-10-03 once again 1 year which is
>>>> 2017-10-03
>>>> 5. then it compares the two dates: 2017-10-03 with 2016-10-03
>>>> 6. because 2017-10-03 > 2016-10-03 it does not count 2
>>>> 7. it should gives the following result:
>>>> 2016-10-03|1
>>>>
>>>> What am I doing wrong here?
>>>>
>>> When the recursive Query starts up, the first value that it outputs is
>>> given
>>> by the very first part of the query, namely: VALUES('2015-10-03')
>>> So on the first iteration, it will produce one row like this:
>>> '2015-10-03'
>>> regardless of the rest of the Query. This row is pushed into the
>>> recursion
>>> buffer.
>>>
>>> After that it then reads a row from the recursion buffer and checks
>>> (within
>>> the WHERE clause) whether the value in it (namely: '2015-10-03') is <=
>>> '2016-11-01', and finds that it definitely IS less, so continues to
>>> produce
>>> the another line of output.
>>>
>>> The output created is that date from the buffer (2015-10-03) which is put
>>> through the given calculation: date(dateD, '+1 year') to give:
>>> '2016-10-03'
>>>
>>> It then continues to push that next row into the recursion buffer and
>>> next
>>> reads again from it and again checks if it (2016-10-03) is <= than
>>> 2016-11-01, which again it is... so it continues to produce the next
>>> output
>>> row, which after calculation becomes:
>>> '2017-10-03'
>>>
>>> It then continues to push that again into the buffer and again read it
>>> and
>>> again checks if it (2017-10-03) is less than 2016-11-01, which THIS TIME,
>>> it
>>> isn't... so it stops right there.
>>>
>>> So in the end, it has produced 3 output rows namely:
>>> '2015-10-03'
>>> '2016-10-03'
>>> '2017-10-03'
>>>
>>> Which is exactly what you've asked for.
>>>
>>> Note: The first part of the query will ALWAYS reach the output buffer,
>>> even
>>> if it isn't a recursive query, and the UNION is NOT specified, you will
>>> get
>>> at least the '2015-10-03' value.
>>> Note: When comparing in the WHERE clause, you do not compare the newly
>>> calculated value (date(xxx, +1 year)), but indeed you compare the
>>> before-calculated value, i.e. the previous value in the buffer (the same
>>> as
>>> how your calculation is done on the PREVIOUS value in the buffer to yield
>>> the new date with.
>>>
>>> I hope that helps to make sense.
>>>
>>> Cheers,
>>> Ryan
>>
>> I think then that that the following SQL query gives to me the desired
>> result:
>> WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
>> date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
>> date('2016-11-01') ) SELECT max(dateD), count(dateD)-2 FROM dates;
>>
>> count(dateD)-2 does the math. Right?
>>
>
> That depends on what the desired result is. Do you want to count how many
> years elapsed (in full) since 3 October 2015?  Count()-2 is the worst hack
> for this (unless the question is specifically "what is 2 less than the
> number of years between x and y....").
>
> 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?
>
> Maybe if you explain what is the question you are asking, and perhaps
> provide 2 examples, the one you already done is fine, but give another one
> where the dates are from 2015-10-03 to 2025-11-01 or such, how must that
> date-range be answered by the query?

I will try to explain what is the question and will provide two or
three examples.

I am working on a SQLite database which I want to use for calculation
for the Length Of Service for  me as a worker.
The database is so far with only one table:
CREATE TABLE MyLengthOfService (
    id              INT  PRIMARY KEY
                         UNIQUE,
    WorkPlaceName   TEXT,
    StartDate       DATE,
    EndDate         DATE,
    WithWorkingTime INT,
    Comment         TEXT,
    Years           INT,
    RemainingMonths INT,
    RemainingDays   INT
);

I want to create a Trigger which would calculate Years,
RemainingMonths, and RemainingDays for a WorkPlace.

So far the Trigger is this:

UPDATE MyLengthOfService SET Years = ( WITH RECURSIVE dates(dateD) AS
( SELECT StartDate FROM MyLengthOfService WHERE EndDate = NEW.EndDate
UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE dateD <=
date(( SELECT EndDate FROM MyLengthOfService )) ) SELECT
count(DateD)-2 FROM dates WHERE EndDate = NEW.EndDate );

and this trigger does not work as expected because it updates every
record - row in the table, like this:
1
-2
-2
etc.

But, I must to calculate these times as follows ( because this way is
calculated here, in our Country - Serbia ):

We get the year after 12 months,
the month runs from the first to the last day,
the number of days is equal to the remaining days.

For example, I went to work on 2017.02.11.
On 2018.02.11 I had been working for a year now.
Additionally, a month passed by 02.11 to 03.10.
In addition, until July 10, five months have passed
 and there are still twelve days,
which is also counted on work,
that is, practice in my time.

In the MyWorkPlaces table, you can
include multiple data entries.
Finally, these should all be summed up
in years, months, and days.

Worked at work years, months and days we have to multiply it by working time.
For example, if from 01.09.2000 until 31.01.2001 I only worked half a full time,
that's it the five months I have to multiply with 0.5 and then I get 2.5 months,
which is 2 months and 15 days.

CALCULATING THE NUMBER OF MY YEARS
1. I would add a year for start date.
2. Is the date longer than the ending date?
3. If not, repeat step 1.
4. I would count how many times I did it
   step 1. I have so many full years in this workplace.
5. I would write the result into Years column of the table.

CALCULATION THE NUMBER OF MY MONTHS
1. To final date of my years counting I would add a month.
2. Is the date longer than the ending date?
3. If not, repeat step 1.
4. I would count how many times I did it
   step 1. I have that full months at this job
   beyond its entire years.
5. I would write the result into RemainingMonths column of the table.

CALCULATION THE NUMBER OF MY DAYS
1. To final date of my months counting I would add a day.
2. Is the date longer than the ending date?
3. If not, repeat step 1.
4. I would count how many times I did it
   step 1. I have that full days in this workplace
   beyond its entire years and months.
5. I would write the result into RemainingDays column of the table.

How do I aggregate the work I've been working so far
in years, months and days by using the data from
Years, RemainingMonths, RemainingDays columns of the table?

I would sum the remaining days and if the sum is greater than 30,
I would divide it with 30. This way I would get the remaining days.
Then the result of dividing would add to the remaining months.
If the result of adding is greater than 12,
I would divide that number with 12. This way I would get the remaining
months.
Then the result of dividing would add to the number of years.

Examples of dates are in the database itself:
--
-- File generated with SQLiteStudio v3.1.1 on P aug. 3 14:33:26 2018
--
-- Text encoding used: UTF-8
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- Table: MyLengthOfService
CREATE TABLE MyLengthOfService (
    id              INT  PRIMARY KEY
                         UNIQUE,
    WorkPlaceName   TEXT,
    StartDate       DATE,
    EndDate         DATE,
    WithWorkingTime INT,
    Comment         TEXT,
    Years           INT,
    RemainingMonths INT,
    RemainingDays   INT
);

INSERT INTO MyLengthOfService (
                                  id,
                                  WorkPlaceName,
                                  StartDate,
                                  EndDate,
                                  WithWorkingTime,
                                  Comment,
                                  Years,
                                  RemainingMonths,
                                  RemainingDays
                              )
                              VALUES (
                                  1,
                                  'Name of the 1. work place',
                                  '1983-07-11',
                                  '1984-08-31',
                                  1,
                                  'workman',
                                  NULL,
                                  NULL,
                                  NULL
                              );

INSERT INTO MyLengthOfService (
                                  id,
                                  WorkPlaceName,
                                  StartDate,
                                  EndDate,
                                  WithWorkingTime,
                                  Comment,
                                  Years,
                                  RemainingMonths,
                                  RemainingDays
                              )
                              VALUES (
                                  2,
                                  'Name of the 2. work place',
                                  '1984-11-01',
                                  '1986-01-15',
                                  1,
                                  'workman',
                                  NULL,
                                  NULL,
                                  NULL
                              );

INSERT INTO MyLengthOfService (
                                  id,
                                  WorkPlaceName,
                                  StartDate,
                                  EndDate,
                                  WithWorkingTime,
                                  Comment,
                                  Years,
                                  RemainingMonths,
                                  RemainingDays
                              )
                              VALUES (
                                  3,
                                  'Name of the 3. work place',
                                  '1986-01-16',
                                  '1999-07-16',
                                  1,
                                  'workman',
                                  NULL,
                                  NULL,
                                  NULL
                              );

INSERT INTO MyLengthOfService (
                                  id,
                                  WorkPlaceName,
                                  StartDate,
                                  EndDate,
                                  WithWorkingTime,
                                  Comment,
                                  Years,
                                  RemainingMonths,
                                  RemainingDays
                              )
                              VALUES (
                                  4,
                                  'Name of the 4. work place',
                                  '2000-02-01',
                                  '2000-08-31',
                                  1,
                                  'teacher',
                                  NULL,
                                  NULL,
                                  NULL
                              );

INSERT INTO MyLengthOfService (
                                  id,
                                  WorkPlaceName,
                                  StartDate,
                                  EndDate,
                                  WithWorkingTime,
                                  Comment,
                                  Years,
                                  RemainingMonths,
                                  RemainingDays
                              )
                              VALUES (
                                  5,
                                  'Name of the 4. work place',
                                  '2000-09-01',
                                  '2001-01-31',
                                  0.5,
                                  'teacher',
                                  NULL,
                                  NULL,
                                  NULL
                              );

INSERT INTO MyLengthOfService (
                                  id,
                                  WorkPlaceName,
                                  StartDate,
                                  EndDate,
                                  WithWorkingTime,
                                  Comment,
                                  Years,
                                  RemainingMonths,
                                  RemainingDays
                              )
                              VALUES (
                                  6,
                                  'Name of the 4. work place',
                                  '2001-02-01',
                                  '2001-08-31',
                                  1,
                                  'teacher',
                                  NULL,
                                  NULL,
                                  NULL
                              );

< intentionally deleted rows >

COMMIT TRANSACTION;
PRAGMA foreign_keys = on;

The desired results for some workplaces are the followings:
( These years, months and days were filled by the administration
worker on the workplace. )
id: 1, 1 year, 1 month, 21 days
id: 2, 1 year, 1 month, 15 days ( I think that that here should be 1
year, 2 months and 15 days.)
id: 3, 13 years, 6 months, 1 day
id: 4, 0 year, 7 months, 0 day
id: 5, 0 year, 2 months, 15 days ( because here I was working with
half of working time )

etc.

Any advices will be appreciated!

--
Best, Pali
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

Kees Nuyt
On Fri, 3 Aug 2018 15:11:06 +0200, Csányi Pál
<[hidden email]> wrote:

>The database is so far with only one table:
>CREATE TABLE MyLengthOfService (
>    id              INT  PRIMARY KEY
>                         UNIQUE,
>    WorkPlaceName   TEXT,

You shouldn't use UNIQUE for the PRIMARY KEY.
Any PRIMARY KEY is implicitly unique by itself.
Adding the UNIQUE keyword might create an extra, redundant,
index, which will eat file space and processing time without
having any added value. And it may confuse the query optimizer.

The idea is that any set (table) may have more than one key to
uniquely identify a tuple (row).
Each key is called a candidate key.

Only one of those keys can be choosen to be the PRIMARY KEY.
All other candidate keys can get honored by adding the UNIQUE
clause, to recognize them as alternative keys.

--
Regards,
Kees Nuyt

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

csanyipal
In reply to this post by R Smith-2
2018-08-03 13:09 GMT+02:00 R Smith <[hidden email]>:

> 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?

--
Best, Pali
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

csanyipal
2018-08-04 14:07 GMT+02:00 Csányi Pál <[hidden email]>:

> 2018-08-03 13:09 GMT+02:00 R Smith <[hidden email]>:
>
>> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

Keith Medcalf

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-
>[hidden email]] 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 <[hidden email]>:
>> 2018-08-03 13:09 GMT+02:00 R Smith <[hidden email]>:
>>
>>> 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

csanyipal
2018-08-05 0:18 GMT+02:00 Keith Medcalf <[hidden email]>:
>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

Keith Medcalf

: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 take it you want to compute YEARS MONTHS and DAYS between two dates:

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

---
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-
>[hidden email]] On Behalf Of Csányi Pál
>Sent: Sunday, 5 August, 2018 02:08
>To: SQLite mailing list
>Subject: Re: [sqlite] Using CTE with date comparison
>
>2018-08-05 0:18 GMT+02:00 Keith Medcalf <[hidden email]>:
>>
>> 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

csanyipal
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

Keith Medcalf

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

csanyipal
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

David Raymond
The whole fencepost thing is probably doing weird things. Here's my take on it.
This is "time to get from A to B". If you want "Total timespan from A to B inclusive" then just add 1 day.

Trying to do the +1 day or -1 day in the middle for the fencepost thing is probably what's causing the error you mentioned. With one of the days being the 1st, the -1 day might leave it on the 31st or the 30th or the 28th or the 29th, depending on the start month. Best to just wait to the very end and say "oh yeah, plus the start day"


with recursive
dates (startDate, endDate) as (values (:startDate, :endDate)),
yearsTable (startDate, years, months, days, resultDate, endDate)
as (
    select min(startDate, endDate),
    0, 0, 0,
    min(startDate, endDate), max(startDate, endDate)
    from dates
    union all
    select startDate, years + 1, months, days,
    date(startDate,
        '+' || cast(years + 1 as text) || ' years'),
    endDate
    from yearsTable where resultDate < endDate
),
monthsTable (startDate, years, months, days, resultDate, endDate)
as (
    select * from (
        select * from yearsTable where resultDate <= endDate
        order by years desc, months desc, days desc limit 1)
    union all
    select startDate, years, months + 1, days,
    date(startDate,
        '+' || cast(years as text) || ' years',
        '+' || cast(months + 1 as text) || ' months'),
    endDate
    from monthsTable where resultDate < endDate
),
daysTable (startDate, years, months, days, resultDate, endDate)
as (
    select * from(
        select * from monthsTable where resultDate <= endDate
        order by years desc, months desc, days desc limit 1)
    union all
    select startDate, years, months, days + 1,
    date(startDate,
        '+' || cast(years as text) || ' years',
        '+' || cast(months as text) || ' months',
        '+' || cast(days + 1 as text) || ' days'),
    endDate
    from daysTable where resultDate < endDate
)
select  startDate, years, months, days, resultDate
from daysTable where resultDate = endDate;


startDate   years       months      days        resultDate
----------  ----------  ----------  ----------  ----------
2004-02-02  0           0           0           2004-02-02
2004-02-02  14          6           1           2018-08-03
1983-07-11  1           1           20          1984-08-31
1984-11-01  1           2           14          1986-01-15
1986-01-16  13          6           0           1999-07-16
1970-01-01  48          7           5           2018-08-06


Leap year

startDate   years       months      days        resultDate
----------  ----------  ----------  ----------  ----------
2016-02-28  1           0           1           2017-03-01
2015-02-28  1           0           2           2016-03-01


Around the end of a month

startDate   years       months      days        resultDate
----------  ----------  ----------  ----------  ----------
2018-01-15  0           0           30          2018-02-14
2018-02-15  0           0           27          2018-03-14
2016-02-15  0           0           28          2016-03-14
2018-04-15  0           0           29          2018-05-14


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Csányi Pál
Sent: Monday, August 06, 2018 10:55 AM
To: SQLite mailing list
Subject: Re: [sqlite] Using CTE with date comparison

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Using CTE with date comparison

csanyipal
On Mon, Aug 06, 2018 at 05:02:00PM +0000, David Raymond wrote:

> The whole fencepost thing is probably doing weird things. Here's my take on it.
> This is "time to get from A to B". If you want "Total timespan from A to B inclusive" then just add 1 day.
>
> Trying to do the +1 day or -1 day in the middle for the fencepost thing is probably what's causing the error you mentioned. With one of the days being the 1st, the -1 day might leave it on the 31st or the 30th or the 28th or the 29th, depending on the start month. Best to just wait to the very end and say "oh yeah, plus the start day"
>
>
> with recursive
> dates (startDate, endDate) as (values (:startDate, :endDate)),
> yearsTable (startDate, years, months, days, resultDate, endDate)
> as (
>     select min(startDate, endDate),
>     0, 0, 0,
>     min(startDate, endDate), max(startDate, endDate)
>     from dates
>     union all
>     select startDate, years + 1, months, days,
>     date(startDate,
>         '+' || cast(years + 1 as text) || ' years'),
>     endDate
>     from yearsTable where resultDate < endDate
> ),
> monthsTable (startDate, years, months, days, resultDate, endDate)
> as (
>     select * from (
>         select * from yearsTable where resultDate <= endDate
>         order by years desc, months desc, days desc limit 1)
>     union all
>     select startDate, years, months + 1, days,
>     date(startDate,
>         '+' || cast(years as text) || ' years',
>         '+' || cast(months + 1 as text) || ' months'),
>     endDate
>     from monthsTable where resultDate < endDate
> ),
> daysTable (startDate, years, months, days, resultDate, endDate)
> as (
>     select * from(
>         select * from monthsTable where resultDate <= endDate
>         order by years desc, months desc, days desc limit 1)
>     union all
>     select startDate, years, months, days + 1,
>     date(startDate,
>         '+' || cast(years as text) || ' years',
>         '+' || cast(months as text) || ' months',
>         '+' || cast(days + 1 as text) || ' days'),
>     endDate
>     from daysTable where resultDate < endDate
> )
> select  startDate, years, months, days, resultDate
> from daysTable where resultDate = endDate;
>
>
> startDate   years       months      days        resultDate
> ----------  ----------  ----------  ----------  ----------
> 2004-02-02  0           0           0           2004-02-02
> 2004-02-02  14          6           1           2018-08-03
> 1983-07-11  1           1           20          1984-08-31
> 1984-11-01  1           2           14          1986-01-15
> 1986-01-16  13          6           0           1999-07-16
> 1970-01-01  48          7           5           2018-08-06
>
>
> Leap year
>
> startDate   years       months      days        resultDate
> ----------  ----------  ----------  ----------  ----------
> 2016-02-28  1           0           1           2017-03-01
> 2015-02-28  1           0           2           2016-03-01
>
>
> Around the end of a month
>
> startDate   years       months      days        resultDate
> ----------  ----------  ----------  ----------  ----------
> 2018-01-15  0           0           30          2018-02-14
> 2018-02-15  0           0           27          2018-03-14
> 2016-02-15  0           0           28          2016-03-14
> 2018-04-15  0           0           29          2018-05-14

With this WITH statement I get outputs which are such as I expected to be.

Thank you very much!!

--
Best, Pali
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál