2018-08-03 13:09 GMT+02:00 R Smith <ryansmit...@gmail.com>:
>
>
> On 2018/08/03 12:35 PM, Csányi Pál wrote:
>>
>> 2018-08-02 23:12 GMT+02:00 R Smith <ryansmit...@gmail.com>:
>>>
>>> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
