[SOLVED]DATASET Doesn't Filter as Expected

[Zvoni]

Right.
Changed my approach.
In a nutshell: i treat friday and saturday like any other "hard" Holiday, a.k.a "non working day"
I created a View "NonWorkingDays", consisting of a FULL OUTER JOIN of a Calendar CTE only grabbing fridays and saturdays and the holidays in OCA, with Holidays taking precedence (a holiday can be on a friday or saturday).
The "Range" of the Calendar CTE is StartOfYear of today ('2023-01-01') to Today + 2 Years ('2025-07-21')
The View can be change to be more "dynamic", e.g. Today -1 year to Today + 2 Years ('2022-07-22' to '2025-07-21').
In that case "Old" View must be dropped and recreated a new

Field DREP in VAC2 has also been changed incl. both Triggers for VAC2

Basically: If ED+1 hits on one of those days, the Offset OS gets increased by 1, and DREP bascially boils down to EP+1+OS

[kito]

OUCH!!
Which SQLite-Version do you have?
As i said: It requires 3.31 or later

Can anyone else confirm?

Have you tried build a complte new Database with my SQL's above?
Remember: You need to activate recursive Triggers in your Conncetion
https://www.sqlite.org/pragma.html#pragma_recursive_triggers

sorry my bad I was using an older version of SQLite. after updating the lib  database is connected the fields are displayed
drep is so correct none a single mistake!


but I can edit nothing

Remember: You need to activate recursive Triggers in your connection
https://www.sqlite.org/pragma.html#pragma_recursive_triggers

I don't really know how exactly do that I add in Tzconnection the following property

Code: Pascal  [Select][+][-]

1. PRAGMA recursive_triggers = on;

and also after the database is connected :

Code: Pascal  [Select][+][-]

1.  DataModule1.ZConnection1.ExecuteDirect('PRAGMA recursive_triggers = on;');  

 

[Zvoni]

The PRAGMA execution is correct

Note: in my approach you can’t edit ED or DREP,
Only ST, CO and OS.
Every calculation is done by the database.

Why would you want to EDIT that?

Note: there is a way you can edit ED and DREP, but it involves a bit of redesign and tweaking.

The main purpose of my approach was to show you, that you don’t need that convoluted calculation in your pascal code

[kito]

The PRAGMA execution is correct

Note: in my approach you can’t edit ED or DREP,
Only ST, CO and OS.
Every calculation is done by the database.

Why would you want to EDIT that?

I meant  that The triggers wont executed instantly after editing Co,ST unless I reopen the program 

[Zvoni]

Did you refresh everything?

[kito]

Did you refresh everything?

you are my hero!

Everything worked smoothly and correctly. Now, I can get rid of the visual clutter I created in my program

[alpine]

@Zvoni
There is an issue with the trgVACAftUpd trigger. It only increments the VAC2.OS and if you update VAC2.ST or VAC2.CO several times then VAC2.OS ends up with incorrect value.

Instead of using that clever recursive increase, I would propose the following triggers (using recursive CTE as alternative):

Code: SQL  [Select][+][-]

1. CREATE TRIGGER tVACai
2.          AFTER INSERT
3.             ON VAC2
4.       FOR EACH ROW
5. BEGIN
6.     UPDATE VAC2
7.        SET OS = (
8.            WITH nwd (st)
9.                AS (
10.                    SELECT O.ST
11.                      FROM vwNonWorkingDays AS O
12.                      WHERE DATE(O.ST) = DATE(JulianDay(NEW.ED) + 1)
13.                    UNION ALL
14.                    SELECT O.ST
15.                      FROM vwNonWorkingDays AS O
16.                           INNER JOIN
17.                           nwd ON DATE(O.ST) = DATE(JulianDay(nwd.st) + 1)
18.                )
19.                SELECT COALESCE(JulianDay(MAX(st) ) - JulianDay(NEW.ED), 0)
20.                  FROM nwd
21.            )
22.      WHERE ID = NEW.ID;
23. END;

Code: SQL  [Select][+][-]

1. CREATE TRIGGER tVACau
2.          AFTER UPDATE
3.             ON VAC2
4.       FOR EACH ROW
5.           WHEN OLD.ED <> NEW.ED
6. BEGIN
7.     UPDATE VAC2
8.        SET OS = (
9.            WITH nwd (st)
10.                AS (
11.                    SELECT O.ST
12.                      FROM vwNonWorkingDays AS O
13.                      WHERE DATE(O.ST) = DATE(JulianDay(NEW.ED) + 1)
14.                    UNION ALL
15.                    SELECT O.ST
16.                      FROM vwNonWorkingDays AS O
17.                           INNER JOIN
18.                           nwd ON DATE(O.ST) = DATE(JulianDay(nwd.st) + 1)
19.                )
20.                SELECT COALESCE(JulianDay(MAX(st) ) - JulianDay(NEW.ED), 0)
21.                  FROM nwd
22.            )
23.      WHERE ID = NEW.ID;
24. END;

Computing OS in triggers raises the issue what if the holiday table changed in a way that affects the existing rows in VAC2.

[Zvoni]

Interesting. Didn’t think about that.
Nicely done

[alpine]

Computing OS in triggers raises the issue what if the holiday table changed in a way that affects the existing rows in VAC2.

Still my opinion is that these should be calculated at the time of reading, i.e. not stored, to reflect the current data into the holiday table. A covering VIEW can be used to extend the VAC2 table with the calculated ED, DPREP. Pity is the VIEW is read-only in sqlite and it will require INSTEAD OF triggers to update the underlying data.

[Zvoni]

True, but i had to use STORED for ED to make it available for the New qualifier, otherwise i would have to implement the whole calculation for ED