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