|
|
|
|
Hi,
I have a small database with schema attached in this mail.
I have two triggers to update fields after an update occures.
When I run the SQL code in sqlite3 CLI, or in SqliteStudio's SQL
Editor, it output is that I expect. It gives the numbers of months and
days so far:
SELECT total(RemainingDays) FROM MyLengthOfService;
total(RemainingDays)
63
SELECT total(RemainingDays) % 30 FROM MyLengthOfService;
total(RemainingDays) % 30
3
But even if I run the trigger in SqliteStudio's SQL Editor alone, it
does not give the expected output:
UPDATE SummedYearsMonthsDays SET RemainingSummedDays = CASE WHEN (
SELECT total(RemainingDays) FROM MyLengthOfService ) < 30 THEN
RemainingSummedDays = ( SELECT total(RemainingDays) FROM
MyLengthOfService ) ELSE RemainingSummedDays = ( SELECT
total(RemainingDays) % 30 FROM MyLengthOfService ) END WHERE id = 1;
The output is empty.
When the trigger is fired, those numbers are not updated in the
corresponding fields of the corresponding table.
To be more precise, the AllYearsMonthsDays trigger does not work.
The YearsRemainingMonthsDays trigger works.
What am I missing here?
--
Best, Pali
Üdvözlettel, Csányi Pál tanár
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
|
|
|
You don't list your trigger definition if there's anything that might need
troubleshooting, but I will say that I've recently stopped using
SQLiteStudio for its somewhat erratic behavior.
While it's got a great regex implementation (which doesn't port to running
in pure CLI-based scripts) and a no-nonsense GUI, it also tends to flake
out with memory issues on relatively small databases. I also cannot find if
it's actively supported; no recent versions or activity on the forum.
You might need to crosscheck your results within alternates like
SQLiteAdmin or dbVis.
Regards.
Brian P Curley
On Sun, Aug 26, 2018, 3:41 AM Csányi Pál < [hidden email]> wrote:
> Hi,
>
> I have a small database with schema attached in this mail.
> I have two triggers to update fields after an update occures.
>
> When I run the SQL code in sqlite3 CLI, or in SqliteStudio's SQL
> Editor, it output is that I expect. It gives the numbers of months and
> days so far:
> SELECT total(RemainingDays) FROM MyLengthOfService;
> total(RemainingDays)
> 63
>
> SELECT total(RemainingDays) % 30 FROM MyLengthOfService;
> total(RemainingDays) % 30
> 3
>
> But even if I run the trigger in SqliteStudio's SQL Editor alone, it
> does not give the expected output:
> UPDATE SummedYearsMonthsDays SET RemainingSummedDays = CASE WHEN (
> SELECT total(RemainingDays) FROM MyLengthOfService ) < 30 THEN
> RemainingSummedDays = ( SELECT total(RemainingDays) FROM
> MyLengthOfService ) ELSE RemainingSummedDays = ( SELECT
> total(RemainingDays) % 30 FROM MyLengthOfService ) END WHERE id = 1;
> The output is empty.
>
> When the trigger is fired, those numbers are not updated in the
> corresponding fields of the corresponding table.
> To be more precise, the AllYearsMonthsDays trigger does not work.
> The YearsRemainingMonthsDays trigger works.
>
> What am I missing here?
>
> --
> Best, Pali
> Üdvözlettel, Csányi Pál tanár
> _______________________________________________
> 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
|
|
|
On Sun, Aug 26, 2018 at 07:45:33AM -0400, Brian Curley wrote:
> You don't list your trigger definition if there's anything that might need
> troubleshooting, but I will say that I've recently stopped using
> SQLiteStudio for its somewhat erratic behavior.
I attached to my previous mail the whole database dump in which is
that trigger which does not work as I expected.
But here is then the database itself:
CREATE TABLE MyLengthOfService (id INT PRIMARY KEY, 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', 1, 1, 21);
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', 1, 2, 15);
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', 13, 6, 1);
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', 0, 7, 0);
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', 0, 5, 0);
INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (6, 'Name of the 4. work place', '2001-02-01', '2018-08-26', 1, 'teacher', 17, 6, 26);
CREATE TABLE SummedYearsMonthsDays (
id INT PRIMARY KEY,
SummedYears INT,
RemainingSummedMonths INT,
RemainingSummedDays INT
);
INSERT INTO SummedYearsMonthsDays (id, SummedYears, RemainingSummedMonths, RemainingSummedDays) VALUES (1, 12, 0, 0);
CREATE TRIGGER AllYearsMonthsDays AFTER UPDATE OF Years, RemainingMonths, RemainingDays ON MyLengthOfService BEGIN UPDATE SummedYearsMonthsDays SET RemainingSummedDays = CASE WHEN ( SELECT total(RemainingDays) FROM MyLengthOfService ) < 30 THEN RemainingSummedDays = ( SELECT total(RemainingDays) FROM MyLengthOfService ) ELSE RemainingSummedDays = ( SELECT total(RemainingDays) % 30 FROM MyLengthOfService ) END WHERE id = 1;
UPDATE SummedYearsMonthsDays SET RemainingSummedMonths = CASE WHEN ( SELECT total(RemainingDays) FROM MyLengthOfService ) < 30 THEN RemainingSummedMonths = ( SELECT total(RemainingMonths) FROM MyLengthOfService ) ELSE RemainingSummedMonths = ( SELECT CAST ( total(RemainingMonths) + total(RemainingMonths) / 30 AS INTEGER ) FROM MyLengthOfService ) END WHERE id = 1; END;
CREATE TRIGGER YearsRemainingMonthsDays AFTER UPDATE OF EndDate ON MyLengthOfService BEGIN UPDATE MyLengthOfService SET Years = ( with recursive dates (startDateR, endDateR) as (SELECT date(StartDate,'-1 day'), date(EndDate) FROM MyLengthOfService WHERE EndDate = NEW.EndDate), yearsTable (startDateR, years, months, days, resultDate, endDateR) as ( select min(startDateR, endDateR), 0, 0, 0, min(startDateR, endDateR), max(startDateR, endDateR) from dates union all select startDateR, years + 1, months, days, date(startDateR, '+' || cast(years + 1 as text) || ' years'), endDateR from yearsTable where resultDate < endDateR ), monthsTable (startDateR, years, months, days, resultDate, endDateR) as ( select * from ( select *from yearsTable where resultDate <= endDateR order by years desc, months desc, days desc limit 1) unionall select startDateR, years, months + 1, days, date(startDateR, '+' || cast(years as text) || ' years', '+' || cast(months + 1 as text) || ' months'), endDateR from monthsTable where resultDate < endDateR ), daysTable (startDateR, years, months, days, resultDate, endDateR) as ( select * from( select * from monthsTable where resultDate <= endDateR order by years desc, months desc, days desc limit 1) union all select startDateR, years, months, days + 1, date(startDateR, '+' || cast(years as text) || ' years', '+' || cast(months as text) || ' months', '+' || cast(days + 1 as text) || ' days'), endDateR from daysTable where resultDate < endDateR ) select years from daysTable where resultDate = endDateR ), RemainingMonths = ( with recursive dates (startDateR, endDateR) as (SELECT date(StartDate,'-1 day'), date(EndDate) FROM MyLengthOfService WHERE EndDate = NEW.EndDate), yearsTable (startDateR, years, months, days, resultDate, endDateR) as ( select min(startDateR, endDateR), 0, 0, 0, min(startDateR, endDateR), max(startDateR,endDateR) from dates union all select startDateR, years + 1, months, days, date(startDateR, '+' || cast(years + 1 as text) || ' years'), endDateR from yearsTable where resultDate < endDateR ), monthsTable (startDateR, years, months, days, resultDate, endDateR) as ( select * from ( select * from yearsTable where resultDate <= endDateR order by years desc, months desc, days desc limit 1) union all select startDateR, years, months + 1, days, date(startDateR, '+' || cast(years as text) || ' years', '+' || cast(months + 1 as text) || ' months'), endDateR from monthsTable where resultDate < endDateR ), daysTable (startDateR, years, months, days, resultDate, endDateR) as ( select * from( select * from monthsTable where resultDate <= endDateR order by years desc, months desc, days desc limit 1) union all select startDateR, years, months, days + 1, date(startDateR, '+' || cast(years as text) || ' years', '+' || cast(months as text) || ' months', '+' || cast(days + 1 as text) || ' days'), endDateR from daysTable where resultDate < endDateR ) select months from daysTable where resultDate = endDateR ), RemainingDays = ( with recursive dates (startDateR, endDateR) as (SELECT date(StartDate,'-1 day'), date(EndDate) FROM MyLengthOfService WHERE EndDate = NEW.EndDate), yearsTable (startDateR, years, months, days, resultDate, endDateR) as ( select min(startDateR, endDateR), 0, 0, 0, min(startDateR, endDateR), max(startDateR, endDateR) from dates union all select startDateR, years + 1, months, days, date(startDateR, '+' || cast(years + 1 as text) ||' years'), endDateR from yearsTable where resultDate < endDateR ), monthsTable (startDateR, years, months, days, resultDate, endDateR) as ( select * from ( select * from yearsTable where resultDate <= endDateR order by years desc, months desc, days desc limit 1) union all select startDateR, years, months + 1, days, date(startDateR, '+' || cast(years as text) || ' years', '+' || cast(months + 1 as text) || ' months'), endDateR from monthsTable where resultDate < endDateR ), daysTable (startDateR, years, months, days, resultDate, endDateR) as ( select * from( select * from monthsTable where resultDate <= endDateR order by years desc, months desc, days desc limit 1) union all select startDateR, years, months, days + 1, date(startDateR, '+' || cast(years as text) || ' years', '+' || cast(months as text) || ' months', '+' ||cast(days + 1 as text) || ' days'), endDateR from daysTable where resultDate < endDateR ) select days from daysTable where resultDate = endDateR ) WHERE EndDate = NEW.EndDate; END;
The question is: why does not work the AllYearsMonthsDays trigger as I expecting?
> Regards.
>
> Brian P Curley
>
>
> On Sun, Aug 26, 2018, 3:41 AM Csányi Pál < [hidden email]> wrote:
>
> > Hi,
> >
> > I have a small database with schema attached in this mail.
> > I have two triggers to update fields after an update occures.
> >
> > When I run the SQL code in sqlite3 CLI, or in SqliteStudio's SQL
> > Editor, it output is that I expect. It gives the numbers of months and
> > days so far:
> > SELECT total(RemainingDays) FROM MyLengthOfService;
> > total(RemainingDays)
> > 63
> >
> > SELECT total(RemainingDays) % 30 FROM MyLengthOfService;
> > total(RemainingDays) % 30
> > 3
> >
> > But even if I run the trigger in SqliteStudio's SQL Editor alone, it
> > does not give the expected output:
> > UPDATE SummedYearsMonthsDays SET RemainingSummedDays = CASE WHEN (
> > SELECT total(RemainingDays) FROM MyLengthOfService ) < 30 THEN
> > RemainingSummedDays = ( SELECT total(RemainingDays) FROM
> > MyLengthOfService ) ELSE RemainingSummedDays = ( SELECT
> > total(RemainingDays) % 30 FROM MyLengthOfService ) END WHERE id = 1;
> > The output is empty.
> >
> > When the trigger is fired, those numbers are not updated in the
> > corresponding fields of the corresponding table.
> > To be more precise, the AllYearsMonthsDays trigger does not work.
> > The YearsRemainingMonthsDays trigger works.
> >
> > What am I missing here?
> >
> > --
> > Best, Pali
--
Best, Pali
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
|
|
|
Please check the syntax of the case-end function. As you have written
them, they just return boolean values.
J-L Hainaut
On 26/08/2018 14:16, Csányi Pál wrote:
> On Sun, Aug 26, 2018 at 07:45:33AM -0400, Brian Curley wrote:
>> You don't list your trigger definition if there's anything that might need
>> troubleshooting, but I will say that I've recently stopped using
>> SQLiteStudio for its somewhat erratic behavior.
> I attached to my previous mail the whole database dump in which is
> that trigger which does not work as I expected.
>
> But here is then the database itself:
> CREATE TABLE MyLengthOfService (id INT PRIMARY KEY, 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', 1, 1, 21);
> 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', 1, 2, 15);
> 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', 13, 6, 1);
> 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', 0, 7, 0);
> 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', 0, 5, 0);
> INSERT INTO MyLengthOfService (id, WorkPlaceName, StartDate, EndDate, WithWorkingTime, Comment, Years, RemainingMonths, RemainingDays) VALUES (6, 'Name of the 4. work place', '2001-02-01', '2018-08-26', 1, 'teacher', 17, 6, 26);
>
> CREATE TABLE SummedYearsMonthsDays (
> id INT PRIMARY KEY,
> SummedYears INT,
> RemainingSummedMonths INT,
> RemainingSummedDays INT
> );
> INSERT INTO SummedYearsMonthsDays (id, SummedYears, RemainingSummedMonths, RemainingSummedDays) VALUES (1, 12, 0, 0);
>
> CREATE TRIGGER AllYearsMonthsDays AFTER UPDATE OF Years, RemainingMonths, RemainingDays ON MyLengthOfService BEGIN UPDATE SummedYearsMonthsDays SET RemainingSummedDays = CASE WHEN ( SELECT total(RemainingDays) FROM MyLengthOfService ) < 30 THEN RemainingSummedDays = ( SELECT total(RemainingDays) FROM MyLengthOfService ) ELSE RemainingSummedDays = ( SELECT total(RemainingDays) % 30 FROM MyLengthOfService ) END WHERE id = 1;
> UPDATE SummedYearsMonthsDays SET RemainingSummedMonths = CASE WHEN ( SELECT total(RemainingDays) FROM MyLengthOfService ) < 30 THEN RemainingSummedMonths = ( SELECT total(RemainingMonths) FROM MyLengthOfService ) ELSE RemainingSummedMonths = ( SELECT CAST ( total(RemainingMonths) + total(RemainingMonths) / 30 AS INTEGER ) FROM MyLengthOfService ) END WHERE id = 1; END;
>
>
>
> CREATE TRIGGER YearsRemainingMonthsDays AFTER UPDATE OF EndDate ON MyLengthOfService BEGIN UPDATE MyLengthOfService SET Years = ( with recursive dates (startDateR, endDateR) as (SELECT date(StartDate,'-1 day'), date(EndDate) FROM MyLengthOfService WHERE EndDate = NEW.EndDate), yearsTable (startDateR, years, months, days, resultDate, endDateR) as ( select min(startDateR, endDateR), 0, 0, 0, min(startDateR, endDateR), max(startDateR, endDateR) from dates union all select startDateR, years + 1, months, days, date(startDateR, '+' || cast(years + 1 as text) || ' years'), endDateR from yearsTable where resultDate < endDateR ), monthsTable (startDateR, years, months, days, resultDate, endDateR) as ( select * from ( select *from yearsTable where resultDate <= endDateR order by years desc, months desc, days desc limit 1) unionall select startDateR, years, months + 1, days, date(startDateR, '+' || cast(years as text) || ' years', '+' || cast(months + 1 as text) || ' months'), endDateR from monthsTable where resultDate < endDateR ), daysTable (startDateR, years, months, days, resultDate, endDateR) as ( select * from( select * from monthsTable where resultDate <= endDateR order by years desc, months desc, days desc limit 1) union all select startDateR, years, months, days + 1, date(startDateR, '+' || cast(years as text) || ' years', '+' || cast(months as text) || ' months', '+' || cast(days + 1 as text) || ' days'), endDateR from daysTable where resultDate < endDateR ) select years from daysTable where resultDate = endDateR ), RemainingMonths = ( with recursive dates (startDateR, endDateR) as (SELECT date(StartDate,'-1 day'), date(EndDate) FROM MyLengthOfService WHERE EndDate = NEW.EndDate), yearsTable (startDateR, years, months, days, resultDate, endDateR) as ( select min(startDateR, endDateR), 0, 0, 0, min(startDateR, endDateR), max(startDateR,endDateR) from dates union all select startDateR, years + 1, months, days, date(startDateR, '+' || cast(years + 1 as text) || ' years'), endDateR from yearsTable where resultDate < endDateR ), monthsTable (startDateR, years, months, days, resultDate, endDateR) as ( select * from ( select * from yearsTable where resultDate <= endDateR order by years desc, months desc, days desc limit 1) union all select startDateR, years, months + 1, days, date(startDateR, '+' || cast(years as text) || ' years', '+' || cast(months + 1 as text) || ' months'), endDateR from monthsTable where resultDate < endDateR ), daysTable (startDateR, years, months, days, resultDate, endDateR) as ( select * from( select * from monthsTable where resultDate <= endDateR order by years desc, months desc, days desc limit 1) union all select startDateR, years, months, days + 1, date(startDateR, '+' || cast(years as text) || ' years', '+' || cast(months as text) || ' months', '+' || cast(days + 1 as text) || ' days'), endDateR from daysTable where resultDate < endDateR ) select months from daysTable where resultDate = endDateR ), RemainingDays = ( with recursive dates (startDateR, endDateR) as (SELECT date(StartDate,'-1 day'), date(EndDate) FROM MyLengthOfService WHERE EndDate = NEW.EndDate), yearsTable (startDateR, years, months, days, resultDate, endDateR) as ( select min(startDateR, endDateR), 0, 0, 0, min(startDateR, endDateR), max(startDateR, endDateR) from dates union all select startDateR, years + 1, months, days, date(startDateR, '+' || cast(years + 1 as text) ||' years'), endDateR from yearsTable where resultDate < endDateR ), monthsTable (startDateR, years, months, days, resultDate, endDateR) as ( select * from ( select * from yearsTable where resultDate <= endDateR order by years desc, months desc, days desc limit 1) union all select startDateR, years, months + 1, days, date(startDateR, '+' || cast(years as text) || ' years', '+' || cast(months + 1 as text) || ' months'), endDateR from monthsTable where resultDate < endDateR ), daysTable (startDateR, years, months, days, resultDate, endDateR) as ( select * from( select * from monthsTable where resultDate <= endDateR order by years desc, months desc, days desc limit 1) union all select startDateR, years, months, days + 1, date(startDateR, '+' || cast(years as text) || ' years', '+' || cast(months as text) || ' months', '+' ||cast(days + 1 as text) || ' days'), endDateR from daysTable where resultDate < endDateR ) select days from daysTable where resultDate = endDateR ) WHERE EndDate = NEW.EndDate; END;
>
> The question is: why does not work the AllYearsMonthsDays trigger as I expecting?
>
>
>
>> Regards.
>>
>> Brian P Curley
>>
>>
>> On Sun, Aug 26, 2018, 3:41 AM Csányi Pál < [hidden email]> wrote:
>>
>>> Hi,
>>>
>>> I have a small database with schema attached in this mail.
>>> I have two triggers to update fields after an update occures.
>>>
>>> When I run the SQL code in sqlite3 CLI, or in SqliteStudio's SQL
>>> Editor, it output is that I expect. It gives the numbers of months and
>>> days so far:
>>> SELECT total(RemainingDays) FROM MyLengthOfService;
>>> total(RemainingDays)
>>> 63
>>>
>>> SELECT total(RemainingDays) % 30 FROM MyLengthOfService;
>>> total(RemainingDays) % 30
>>> 3
>>>
>>> But even if I run the trigger in SqliteStudio's SQL Editor alone, it
>>> does not give the expected output:
>>> UPDATE SummedYearsMonthsDays SET RemainingSummedDays = CASE WHEN (
>>> SELECT total(RemainingDays) FROM MyLengthOfService ) < 30 THEN
>>> RemainingSummedDays = ( SELECT total(RemainingDays) FROM
>>> MyLengthOfService ) ELSE RemainingSummedDays = ( SELECT
>>> total(RemainingDays) % 30 FROM MyLengthOfService ) END WHERE id = 1;
>>> The output is empty.
>>>
>>> When the trigger is fired, those numbers are not updated in the
>>> corresponding fields of the corresponding table.
>>> To be more precise, the AllYearsMonthsDays trigger does not work.
>>> The YearsRemainingMonthsDays trigger works.
>>>
>>> What am I missing here?
>>>
>>> --
>>> Best, Pali
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
|
|
|
On 2018/08/26 2:16 PM, Csányi Pál wrote:
>
>>> When the trigger is fired, those numbers are not updated in the
>>> corresponding fields of the corresponding table.
>>> To be more precise, the AllYearsMonthsDays trigger does not work.
>>> The YearsRemainingMonthsDays trigger works.
>>>
>>> What am I missing here?
>>>
>>> --
>>> Best, Pali
Many things are wrong with those triggers.
They can't use CTE's, the CASE statements evaluate equations, not
values, so they have Boolean results, and one desperate space is missing
after a union all statement, (to name the obvious ones) but then more
hurtful to my OCD is the repetitive adding of CTEs, each of which
evaluates 3 values of which, on every iteration, only 1 value is updated
in the table - not to mention that the table is pressed into service as
a VIEW, and the very expensive ON UPDATE trigger is filling in the table
values that would be much more salient and simple with a VIEW.
Ok, enough about what is wrong with it. Here's how it can be fixed:
First, get rid of the triggers, completely.
Then get rid of the columns in the table named: Years, RemainingMonths,
and RemainingDays.
This will leave you with a Table scripted like this (I added Johnny to
test future end-of-service dates):
CREATE TABLE "MyLengthOfService" (
"id" INT PRIMARY KEY,
"WorkPlaceName" TEXT,
"StartDate" DATE,
"EndDate" DATE,
"WithWorkingTime" INT,
"Comment" TEXT
);
INSERT INTO "MyLengthOfService"
("id","WorkPlaceName","StartDate","EndDate","WithWorkingTime","Comment")
VALUES
(1,'Name of the 1. work place','1983-07-11','1984-08-31',1,'workman')
,(2,'Name of the 2. work place','1984-11-01','1986-01-15',1,'workman')
,(3,'Name of the 3. work place','1986-01-16','1999-07-16',1,'workman')
,(4,'Name of the 4. work place','2000-02-01','2000-08-31',1,'teacher')
,(5,'Name of the 4. work place','2000-09-01','2001-01-31',0.5,'teacher')
,(6,'Name of the 4. work place','2001-02-01','2018-08-26',1,'teacher')
,(7,'Johnny','2018-05-01','2019-04-30',1,'workman')
;
Then, add this View:
CREATE VIEW MyLengthOfServiceTotal AS
WITH worktime(id, dStart, dEnd) AS (
SELECT id, date(StartDate), date(EndDate) FROM MyLengthOfService
), timediff(id, years, months, days) AS (
SELECT id,
CAST(strftime('%Y',dEnd) AS INT)-CAST(strftime('%Y',dStart) AS INT),
CAST(strftime('%m',dEnd) AS INT)-CAST(strftime('%m',dStart) AS INT),
CAST(strftime('%d',dEnd) AS INT)-CAST(strftime('%d',dStart) AS INT)
FROM worktime
), timefix(id, years, months, days) AS (
SELECT id,
CASE WHEN years < 0 THEN 0 WHEN months < 0 THEN years-1 ELSE
years END,
CASE WHEN months < 0 THEN months+12 ELSE months END,
CASE WHEN days < 0 THEN days+30 ELSE days END
FROM timediff
), post(id, years, months, days) AS (
SELECT id,
CASE WHEN days>29 AND months = 11 THEN years+1 ELSE years END,
CASE WHEN days>29 THEN months+1 ELSE months END,
CASE WHEN days>29 THEN 0 ELSE days+1 END
FROM timefix
)
SELECT post.id, post.years AS TotalYears, post.months AS TotalMonths,
post.days AS TotalDays
FROM post
The workings is simple: it calculates the differences in years, months
and days between the starting and ending dates in the timediff CTE. Then
it corrects those for overflow in the timefix CTE, and then lastly the
post CTE is a processing step for some rules that are specific to your
case (like adding a day, calling 30+ days a month, etc.).
This will now allow you to query things like this:
SELECT A.*, B.TotalYears, B.TotalMonths, B.TotalDays
FROM MyLengthOfService AS A
JOIN MyLengthOfServiceTotal AS B ON B.id = A.id
;
-- | | | | WithWor-
| | Total- | Total- | Total-
-- id | WorkPlaceName | StartDate | EndDate |
kingTime | Comment | Years | Months | Days
-- --- | ------------------------- | ---------- | ---------- |
-------- | ------- | ------ | ------ | ------
-- 1 | Name of the 1. work place | 1983-07-11 | 1984-08-31 |
1 | workman | 1 | 1 | 21
-- 2 | Name of the 2. work place | 1984-11-01 | 1986-01-15 |
1 | workman | 1 | 2 | 15
-- 3 | Name of the 3. work place | 1986-01-16 | 1999-07-16 |
1 | workman | 13 | 6 | 1
-- 4 | Name of the 4. work place | 2000-02-01 | 2000-08-31 |
1 | teacher | 0 | 7 | 0
-- 5 | Name of the 4. work place | 2000-09-01 | 2001-01-31 |
0.5 | teacher | 0 | 5 | 0
-- 6 | Name of the 4. work place | 2001-02-01 | 2018-08-26 |
1 | teacher | 17 | 6 | 26
-- 7 | Johnny | 2018-05-01 | 2019-04-30 |
1 | workman | 0 | 11 | 30
which will show you what you had already asked for - i.e. the length of
service of all the people. You could also make this into a view so you
only need to query 1 single item. (If that's a requirement)
Now, once that is done, here are two more views that will give you more
information - if it is ever needed and if you plan ahead, i.e. adding
service EndDate that are not yet reached.
The first view below will show the Service time Elapsed (i.e. how long
have they been in service until today).
The next one will show the Service time remaining (i.e. how long do they
still work until the end of their service - if their work did not end
yet, else showing Zero).
Then at the end, there is a last view which adds all this together into
one single view that you can query with all information available and a
sample query to call it.
CREATE VIEW MyLengthOfServiceElapsed AS
WITH worktime(id, dStart, dEnd) AS (
SELECT id, date(StartDate), CASE WHEN date('now') < date(EndDate)
THEN date('now') ELSE date(EndDate) END FROM MyLengthOfService
), timediff(id, years, months, days) AS (
SELECT id,
CAST(strftime('%Y',dEnd) AS INT)-CAST(strftime('%Y',dStart) AS INT),
CAST(strftime('%m',dEnd) AS INT)-CAST(strftime('%m',dStart) AS INT),
CAST(strftime('%d',dEnd) AS INT)-CAST(strftime('%d',dStart) AS INT)
FROM worktime
), timefix(id, years, months, days) AS (
SELECT id,
CASE WHEN years < 0 THEN 0 WHEN months < 0 THEN years-1 ELSE
years END,
CASE WHEN months < 0 THEN months+12 ELSE months END,
CASE WHEN days < 0 THEN days+30 ELSE days END
FROM timediff
), post(id, years, months, days) AS (
SELECT id,
CASE WHEN days>29 AND months = 11 THEN years+1 ELSE years END,
CASE WHEN days>29 THEN months+1 ELSE months END,
CASE WHEN days>29 THEN 0 ELSE days+1 END
FROM timefix
)
SELECT post.id, post.years AS ElapsedYears, post.months AS
ElapsedMonths, post.days AS ElapsedDays
FROM post
;
CREATE VIEW MyLengthOfServiceRemain AS
WITH worktime(id, dStart, dEnd) AS (
SELECT id, CASE WHEN date('now') BETWEEN date(StartDate) AND
date(EndDate) THEN date('now') ELSE date(EndDate) END, date(EndDate)
FROM MyLengthOfService
), timediff(id, years, months, days) AS (
SELECT id,
CAST(strftime('%Y',dEnd) AS INT)-CAST(strftime('%Y',dStart) AS INT),
CAST(strftime('%m',dEnd) AS INT)-CAST(strftime('%m',dStart) AS INT),
CAST(strftime('%d',dEnd) AS INT)-CAST(strftime('%d',dStart) AS INT)
FROM worktime
), timefix(id, years, months, days) AS (
SELECT id,
CASE WHEN years < 0 THEN 0 WHEN months < 0 THEN years-1 ELSE
years END,
CASE WHEN months < 0 THEN months+12 ELSE months END,
CASE WHEN days < 0 THEN days+30 ELSE days END
FROM timediff
), post(id, years, months, days) AS (
SELECT id,
CASE WHEN days>29 AND months = 11 THEN years+1 ELSE years END,
CASE WHEN days>29 THEN months+1 ELSE months END,
CASE WHEN days>29 THEN 0 ELSE days END
FROM timefix
)
SELECT post.id, post.years AS RemainYears, post.months AS RemainMonths,
post.days AS RemainDays
FROM post
;
CREATE VIEW MyLengthOfServiceAll AS
SELECT A.*,
B.TotalYears, B.TotalMonths, B.TotalDays,
C.ElapsedYears, C.ElapsedMonths, C.ElapsedDays,
D.RemainYears, D.RemainMonths, D.RemainDays
FROM MyLengthOfService AS A
JOIN MyLengthOfServiceTotal AS B ON B.id = A.id
JOIN MyLengthOfServiceElapsed AS C ON C.id = A.id
JOIN MyLengthOfServiceRemain AS D ON D.id = A.id
;
This Query will then show all:
SELECT * FROM MyLengthOfServiceAll;
This is all working well on sqlite 3.24.0 on my side, but I may have
copy-pasted something wrong - if you have any questions or difficulties,
please ask again.
Hope that helps,
Ryan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
|
|
|
On Sun, Aug 26, 2018 at 07:17:00PM +0200, R Smith wrote:
> On 2018/08/26 2:16 PM, Csányi Pál wrote:
> >
> >>> When the trigger is fired, those numbers are not updated in the
> >>> corresponding fields of the corresponding table.
> >>> To be more precise, the AllYearsMonthsDays trigger does not work.
> >>> The YearsRemainingMonthsDays trigger works.
> >>>
> >>> What am I missing here?
> >>>
> >>> --
> >>> Best, Pali
>
> Many things are wrong with those triggers.
> They can't use CTE's, the CASE statements evaluate equations, not
> values, so they have Boolean results, and one desperate space is missing
> after a union all statement, (to name the obvious ones) but then more
> hurtful to my OCD is the repetitive adding of CTEs, each of which
> evaluates 3 values of which, on every iteration, only 1 value is updated
> in the table - not to mention that the table is pressed into service as
> a VIEW, and the very expensive ON UPDATE trigger is filling in the table
> values that would be much more salient and simple with a VIEW.
>
> Ok, enough about what is wrong with it. Here's how it can be fixed:
>
> First, get rid of the triggers, completely.
> Then get rid of the columns in the table named: Years, RemainingMonths,
> and RemainingDays.
>
> This will leave you with a Table scripted like this (I added Johnny to
> test future end-of-service dates):
>
> CREATE TABLE "MyLengthOfService" (
> "id" INT PRIMARY KEY,
> "WorkPlaceName" TEXT,
> "StartDate" DATE,
> "EndDate" DATE,
> "WithWorkingTime" INT,
> "Comment" TEXT
> );
> INSERT INTO "MyLengthOfService"
> ("id","WorkPlaceName","StartDate","EndDate","WithWorkingTime","Comment")
> VALUES
> (1,'Name of the 1. work place','1983-07-11','1984-08-31',1,'workman')
> ,(2,'Name of the 2. work place','1984-11-01','1986-01-15',1,'workman')
> ,(3,'Name of the 3. work place','1986-01-16','1999-07-16',1,'workman')
> ,(4,'Name of the 4. work place','2000-02-01','2000-08-31',1,'teacher')
> ,(5,'Name of the 4. work place','2000-09-01','2001-01-31',0.5,'teacher')
> ,(6,'Name of the 4. work place','2001-02-01','2018-08-26',1,'teacher')
> ,(7,'Johnny','2018-05-01','2019-04-30',1,'workman')
> ;
You have misunderstand the purpose of the MyLengthOfService table.
The MyLengthOfService table contains WorkPlaces not for many persons,
but for one person only.
So my goal is to get summed years, months and days for that one
person. The person has been worked on many places.
--
Best, Pali
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
|
|
|
On 2018/08/26 8:44 PM, Csányi Pál wrote:
> On Sun, Aug 26, 2018 at 07:17:00PM +0200, R Smith wrote:
>
> You have misunderstand the purpose of the MyLengthOfService table.
> The MyLengthOfService table contains WorkPlaces not for many persons,
> but for one person only.
>
> So my goal is to get summed years, months and days for that one
> person. The person has been worked on many places.
>
Great, that's even easier:
WITH worktime(grp, totDays) AS (
SELECT WorkPlaceName, SUM(CAST(strftime('%J',date(EndDate)) AS
INT)-CAST(strftime('%J',date(StartDate)) AS INT))
FROM MyLengthOfService
GROUP BY WorkPlaceName
)
SELECT grp AS WorkPlace, (totDays / 365) AS Years, ((totDays % 365) /
30) AS Months, ((totDays % 365) % 30) AS Days, totDays
FROM worktime
;
And, can be made into a VIEW if you like.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
|
|
|
Well, the documentation *says* that a with clause cannot be used in a trigger, but that is incorrect, at least for the current tip of trunk ... because the following script works ...
---//--- snip ---//---
pragma recursive_triggers = 1;
create table if not exists services
(
id INTEGER PRIMARY KEY,
startDate text not null,
endDate text null,
years integer null,
months integer null,
days integer null,
toggle integer default (-1)
);
create trigger if not exists InsertServices after insert on services
begin
update services
set toggle = -new.toggle
where id = new.id;
end;
create trigger if not exists UpdateServices after update of startDate, endDate, toggle on services
begin
update services
set (years, months, days) = (with recursive
dates (startDate, endDate) as
(
select coalesce(new.startDate, date('now', 'localtime')),
coalesce(new.endDate, date('now','localtime'))
),
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, printf('%+d years', years + 1)),
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, printf('%+d years', years),
printf('%+d months', months + 1)),
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, printf('%+d years', years),
printf('%+d months', months),
printf('%+d days', days + 1)),
endDate
from daysTable
where resultDate < endDate
),
dateDifference (startDate, resultDate, years, months, days) as
(
select startDate,
resultDate,
years,
months,
days
from daysTable
where resultDate = endDate
)
select years,
months,
days
from dateDifference
)
where id = new.id;
end;
.mode col
.head on
insert into services (startDate) values ('1995-02-01');
insert into services (startDate, endDate) values ('1995-02-01', '2018-08-31');
insert into services (startDate) values ('2004-02-01');
insert into services (startDate, endDate) values ('2004-02-01', '2018-08-31');
select * from services;
-- when run this recalculates all rows where the endDate is null using the current date
update services set toggle=-toggle where endDate is null;
---//--- snip ---//---
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
|
|
Locked
