[SOLVED]DATASET Doesn't Filter as Expected

[Zvoni]

And read the SQLite-documentation

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

1. CREATE TABLE "VAC" (
2.         "ID"    INTEGER,
3.         "ST"    VARCHAR(50) ,
4.         "ED"    VARCHAR(50) ,
5.         "CO"    INTEGER,
6.         "DREP"  VARCHAR(50) ,
7.         PRIMARY KEY("ID" AUTOINCREMENT)
8. )

There is no VARCHAR-DataType
VARCHAR(50) --> the "50" is completly ignored by SQLite.
Use TEXT and be done with it

If I use text type every text field turns to the word "memo" rather than the string in the database and the number besides Varchar it represents the max length   (I tested it  )and  SQLite behaves according to it. 

Ah. Damn. Forgot about that one.
Well it's easy to solve

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

1. SELECT CHAR(SomeTextField) AS MyField1 FROM MyTable....

And all those "Memos" go away

As for the "maximum" length: SQLite ignores it. Even if you define a Column as VARCHAR(50), SQLite will not prevent you from inserting a String with 200 Characters

EDIT2: And don't use AUTOINCREMENT on your Integer-Primary Key

I do not prefer manual input for the primary key. Can you tell me why, if you please?

You don't have to provide a "manual" Primary Key. Without AUTOINCREMENT SQLite will still "count up" your Primary Key.
The Difference being, that if in case you reach the upper end of the available Value-Range (as unlikely that might be), SQLite will start back at the bottom again, and use deleted values

Just as a question Zvoni will kill me , what's the harm in preferring to deal with type string instead of dates, when no one complains about the texts?   Especially since Sqlite does not differentiate between them why does the function date to string exist at all?
as SymbolicFrank said

Because SQLite has NO dedicated DateTime DataType.
With SQLite you have three options for Dates:
TEXT --> "2023-07-21" --> ISO-DateFOrmat
REAL --> 42536.25689 --> JulianDay
INTEGER --> 123456789 --> UNIXEPOCH

DateToStr is for Situations you want to Display a Date (which in the Background is a Double) in a Control, which accepts only Strings.

[Zvoni]

OK, next.
I'm trying to figure out, what he's actually trying to achieve in his CalcFields-Routine.
Could you explain (with examples) what you're trying here?

EDIT: OK, trying to make sense of it
in his sample DB the first record is as follows:
ST = 2023-01-02
CO = 18 (Where's that value coming from?)
I'm going to ignore any Values in the DB for ED and DREP

If i understand it correctly, it goes like this:
ED = ST + CO-1 --> 2023-01-02 + 18-1 = 2023-01-19
DREP = ED+1 --> 2023-01-19 + 1 = 2023-01-20

[INSERT]
https://www.freepascal.org/docs-html/rtl/sysutils/dayofweek.html

DayOfWeek returns the day of the week from DateTime. Sunday is counted as day 1, Saturday is counted as day 7.

In your CalcFields you check if DayOfWeek(DREP) = 6 --> You check for Friday
[INSERT END]

If DREP = Friday Then DREP = DREP + 2 = 2023-01-20 + 2 = 2023-01-22 (Which would make it a Sunday)--> Which is also in his DB
(The Value in the DB for ED makes no sense whatsoever)

Correct so far?

I'm pretty sure this calculation can be solved in SQLite directly as a computed column.
Then you could throw your CalcField routine into the bin

[TRon]

@ZVoni:
My "to human language" translated attempt:

"something" has a start and end date, occupying a certain amount of days between them.

After the amount of days that 'something' needs to be resumed (I thought of it as for example sending a notice or resuming payments)

If the end date is at the start of a weekend then 'resuming' can't take place (people are not present in weekends) in which case the end-date (or actually resume date) has to  shift over an x amount of days so that resuming takes place f.e. during office hours/days (people are present again).

I'm pretty sure this calculation can be solved in SQLite directly as a computed column.
Then you could throw your CalcField routine into the bin

Exactly my thought as well. No idea why that is a separate column stored in the DB unless that is the whole point for the code to exist (to update a database with additional/corrected information) or perhaps someone does not trust the calculation(s) and need to be stored as intermediate result.

[Zvoni]

@ZVoni:
My "to human language" translated attempt:

"something" has a start and end date, occupying a certain amount of days between them.

After the amount of days that 'something' needs to be resumed (I thought of it as for example sending a notice or resuming payments)

If the end date is at the start of a weekend then 'resuming' can't take place (people are not present in weekends) in which case the end-date (or actually resume date) has to  shift over an x amount of days so that resuming takes place f.e. during office hours/days (people are present again).

I'm pretty sure this calculation can be solved in SQLite directly as a computed column.
Then you could throw your CalcField routine into the bin

Exactly my thought as well. No idea why that is a separate column stored in the DB unless that is the whole point for the code to exist (to update a database with additional/corrected information) or perhaps someone does not trust the calculation(s) and need to be stored as intermediate result.

Ron, i understood that, too.
But his DayOfWeek=6 doesn't make sense in that case

this is mystifying me completly:

If i understand it correctly, it goes like this:
ED = ST + CO-1 --> 2023-01-02 + 18-1 = 2023-01-19
DREP = ED+1 --> 2023-01-19 + 1 = 2023-01-20

Why not just
DREP = ST + CO
Because that's what math is for

That said: I understand that if a Startdate is e.g.
2023-07-01 and you add 18 days to it, math says that EndDate is 2023-07-19
BUT
If something starts on 2023-07-01 that day counts for it, too!
So EndDate would be StartDate + 18-1 = 2023-07-18
Something starts on the First, it takes 18 days, so it finishes on 18th

Understood so far, but then i don't understand, why he adds a Day again?
Because that would be a "new" StartDate, right? resp. his "Resuming Date"?

If the end date is at the start of a weekend then 'resuming' can't take place (people are not present in weekends) in which case the end-date (or actually resume date) has to  shift over an x amount of days so that resuming takes place f.e. during office hours/days (people are present again).

Uhmm, no.
As far as i inderstood: If the "resuming Day" is on a Weekend (why isn't he checking if it's a Sunday?), then move forward 2 days
He's checking DREP=6  not ED=6

[TRon]

But his DayOfWeek=6 doesn't make sense in that case

True, unless 'their weekend' starts at the 6-th day. But I am tempted to go with your thought in thinking it might perhaps have been a mistake. Noting that some countries also use sunday and others monday as first day of the week.

...
Why not just
DREP = ST + CO
Because that's what math is for

true. But that could have been a honest mistake in not realizing if the resulting math needs to include or exclude the last day.

Understood so far, but then i don't understand, why he adds a Day again?
Because that would be a "new" StartDate, right? resp. his "Resuming Date"?

Same reason as above. Does the resume takes place on the day itself or a day later and did the previous calculation include that day already ?

Uhmm, no.
As far as i inderstood: If the "resuming Day" is on a Weekend (why isn't he checking if it's a Sunday?), then move forward 2 days
He's checking DREP=6  not ED=6

True, in that regards I made a mistake so thank you for correcting. But tbh just like you I am puzzled.

If I have a choice then I would prefer to use your tagline 

But perhaps TS is able to shed a (more detailed) light ?

[kito]

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

1.    DataModule1.ZQuery1.Filter:='DREP = '+ QuotedStr(FormatDateTime('YYYY-MM-DD', DateTimePicker1.date)) ;
2. end;

I understood your initial post such that you want to filter on the calculated field. Looking at the fields in the Object Inspector, I see that the calculated field is ED, DREP has FieldKind = fkData, but you are changing it in the OnCalcFields event of the dataset. This looks strange, and I've never seen that.

Next: when I change the filter condition above such that the field ED is filtered, filtering does not work any more. The same happens with the field DREP when I redeclare its FieldKind as fkCalculated.

I think because the  Fieldkind :fkCalculated . Only the fkInternalCalc and fkAggregate fields can be used in filtering .
and yes  DREP changed and updated  when the dataset is not even in update or edit mode !

[Zvoni]

But his DayOfWeek=6 doesn't make sense in that case

True, unless 'their weekend' starts at the 6-th day. But I am tempted to go with your thought in thinking it might perhaps have been a mistake. Noting that some countries also use sunday and others monday as first day of the week.

That wouldn't matter, since DayOfWeek checks against a (hardcoded) enumeration. At least i think it's hardcoded, because otherwise the documentation doesn't make sense.
To compare:
SQLite treats "Weekdays"  as Sunday=0......Saturday=6

Ah, well depending on OP's answer, i already have my "solution" as computed columns done.
"ED = ST+CO-1"
"If the next resuming day (ED+1) is a Saturday or a Sunday, move forward to the next monday, else next resuming day is ED+1"
see attached (Table VAC2)
EDIT: Needs SQLite 3.31 or later

[TRon]

That wouldn't matter, since DayOfWeek checks against a (hardcoded) enumeration. At least i think it's hardcoded, because otherwise the documentation doesn't make sense.

To compare:
SQLite treats "Weekdays"  as Sunday=0......Saturday=6

All true. But think of it as being an american all your life and for the first time reading an european date notation (or vise verse). First line of thought would be to make sense of it how it was learned and used for all;those years.

Same goes for first day of the week (even though the documentation explicitly states otherwise). I have no idea where TS originates from (and it also does not matter to know) but it might be a simple misunderstanding or miscommunication. We do not all share the same believes (thank heavens for that, and thank writers for their documentation  ).

Ah, well depending on OP's answer, i already have my "solution" as computed columns done.

Bravo, nicely done !

[kito]

... and that is exactly when/where all the horror starts happening.
If you are not aware that every user can set his/her own date-time format and not account for it by explicitly having the strings formatted in a format that is consistent throughout your application then things go wrong very fast.

Have you (n)ever noticed how f.e. americans visualize dates in comparison to europeans ? It is a nightmare to work with if you do not know how to handle such things properly in a database and with every conversion you make be it from string to datetime or vise verse or trying to represent the date as a string in another display format then also there things go wrong very quick.

Hello TRon
I am now convinced this is indisputable and cannot be denied. It is reason enough to set things right


 

In your specific case you do not even require the time portion so you could just as well have used integers to store the actual date, f.e. 20031202 for december 2 2003 as that would allow for easy sorting, checking for ranges or checking if one date is before or after another date.

interesting!

But the important part to remember is to never ever work with dates as strings in order to not run into such issues to begin with.

I understand now. It  should be a comprehensive review and take into account various circumstances before writing codes, thank you !

[Zvoni]

In your specific case you do not even require the time portion so you could just as well have used integers to store the actual date, f.e. 20031202 for december 2 2003 as that would allow for easy sorting, checking for ranges or checking if one date is before or after another date.

interesting!

Yes for sorting, No for everything else.
In the company i work for, we actually have exactly that Format.
And if you don't know, that it's NOT of DataType DATE, you fall afoul of

20041202 - 20031202 --> expected Result being 366 (2004 was LeapYear).... instead of 10000

You'd just replace one problem with another

Has been driving me up the wall to write the SELECT's for that, CASTing to/from and whatnot

[kito]

OK, next.
I'm trying to figure out, what he's actually trying to achieve in his CalcFields-Routine.
Could you explain (with examples) what you're trying here?

Here is the story, in brief, starting from the beginning:

"There is a group of workers who are employed on fixed-term contracts. Each worker is entitled to days off each month(at least 16 days from the first month) in contract = 3.33 days off, that is where CO comes from CO = Total of days off.

 

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

1.   function TDataModule1.CONJ(A:TDate;B:TDate):float   ;
2.     var
3.       s:float   ;
4.         v:string;
5.     begin
6.  
7.    
8.      if (DayOf(A)) < 16 then
9.       begin
10.      v := ((MonthsBetween(B,A,True)+1)*3.33).ToString;
11.  
12.       end
13.      else
14.       v := ((MonthsBetween(B,A,True))*3.33).ToString;
15.      s:= round(StrToFloat(v));
16.     Result := s ;
17.  
18.  
19.     end;        
20.  

 the vacation begins on ST and ends on ED, while DREP (date_de_reprise) is the day of resumption and must be a working day. Taking into account that the weekend in my region consists of Friday and Saturday, the worker cannot resume work during these two days.

 I wanted the interface to be as simple as possible, where interaction is done through a dbgrid. Therefore, I avoided making the user go up or down to edit or add anything, just like how we deal with tables in spreadsheets

[kito]

in his sample DB the first record is as follows:
ST = 2023-01-02
CO = 18 (Where's that value coming from?)
I'm going to ignore any Values in the DB for ED and DREP

If i understand it correctly, it goes like this:
ED = ST + CO-1 --> 2023-01-02 + 18-1 = 2023-01-19
DREP = ED+1 --> 2023-01-19 + 1 = 2023-01-20

[INSERT]
https://www.freepascal.org/docs-html/rtl/sysutils/dayofweek.html

DayOfWeek returns the day of the week from DateTime. Sunday is counted as day 1, Saturday is counted as day 7.

In your CalcFields you check if DayOfWeek(DREP) = 6 --> You check for Friday
[INSERT END]

If DREP = Friday Then DREP = DREP + 2 = 2023-01-20 + 2 = 2023-01-22 (Which would make it a Sunday)--> Which is also in his DB
(The Value in the DB for ED makes no sense whatsoever)

Correct so far?

I'm pretty sure this calculation can be solved in SQLite directly as a computed column.
Then you could throw your CalcField routine into the bin

@ZVoni:
My "to human language" translated attempt:

"something" has a start and end date, occupying a certain amount of days between them.

After the amount of days that 'something' needs to be resumed (I thought of it as for example sending a notice or resuming payments)

If the end date is at the start of a weekend then 'resuming' can't take place (people are not present in weekends) in which case the end-date (or actually resume date) has to  shift over an x amount of days so that resuming takes place f.e. during office hours/days (people are present again).

I'm pretty sure this calculation can be solved in SQLite directly as a computed column.
Then you could throw your CalcField routine into the bin

Exactly my thought as well. No idea why that is a separate column stored in the DB unless that is the whole point for the code to exist (to update a database with additional/corrected information) or perhaps someone does not trust the calculation(s) and need to be stored as intermediate result.

I am very impressed with your ability to analyze the system through just a few lines of code.

[Zvoni]

Here is the story, in brief, starting from the beginning:

"There is a group of workers who are employed on fixed-term contracts. Each worker is entitled to days off each month(at least 16 days from the first month) in contract = 3.33 days off, that is where CO comes from CO = Total of days off.

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

1.   function TDataModule1.CONJ(A:TDate;B:TDate):float   ;
2.     var
3.       s:float   ;
4.         v:string;
5.     begin
6.      if (DayOf(A)) < 16 then
7.       begin
8.      v := ((MonthsBetween(B,A,True)+1)*3.33).ToString;
9.       end
10.      else
11.       v := ((MonthsBetween(B,A,True))*3.33).ToString;
12.      s:= round(StrToFloat(v));
13.     Result := s ;
14.     end;        
15.  

the vacation begins on ST and ends on ED, while DREP (date_de_reprise) is the day of resumption and must be a working day. Taking into account that the weekend in my region consists of Friday and Saturday, the worker cannot resume work during these two days.

Now we're getting somewhere!
Meaning, your first working day of the week is actually the Sunday!

I wanted the interface to be as simple as possible, where interaction is done through a dbgrid. Therefore, I avoided making the user go up or down to edit or add anything, just like how we deal with tables in spreadsheets

adjusted VAC2-Table in attachment
How to use: You only INSERT/UPDATE Fields ST and CO,  ED and DREP are recalculated everytime you ACCESS those two fields (a.k.a reading/SELECTing them).
You don't need youor "CalcField" anymore
Just look at VAC2 directly with any viewer of your choice.
As said: Requires SQLite 3.31 or later

[wp]

Only the fkInternalCalc and fkAggregate fields can be used in filtering .

I would not call myself a database guru, but I doubt that this statement is correct in general. The attached project uses a TBufDataset which has three predefined fields, IntField, Floatfield and TextField, plus a calculated field, IntFieldPlus1, which adds 1 to the IntField value. As you can see, filtering on the fkCalculated IntFieldPlus1 field works perfectly here.

The next step would be to study a SQLite3 database in the same way, in both LCL SqlDB and in the ZEOS implementations.

Unfortunately working with persistent fields in the IDE is quite a mess...

[alpine]

"There is a group of workers who are employed on fixed-term contracts. Each worker is entitled to days off each month(at least 16 days from the first month) in contract = 3.33 days off, that is where CO comes from CO = Total of days off.

 

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

1.   function TDataModule1.CONJ(A:TDate;B:TDate):float   ;
2.     var
3.       s:float   ;
4.         v:string;
5.     begin
6.  
7.    
8.      if (DayOf(A)) < 16 then
9.       begin
10.      v := ((MonthsBetween(B,A,True)+1)*3.33).ToString;
11.  
12.       end
13.      else
14.       v := ((MonthsBetween(B,A,True))*3.33).ToString;
15.      s:= round(StrToFloat(v));
16.     Result := s ;
17.  
18.  
19.     end;        
20.  

Side note: why v:=(<exp>).ToString and then s:= round(StrToFloat(v)) ?

the vacation begins on ST and ends on ED, while DREP (date_de_reprise) is the day of resumption and must be a working day. Taking into account that the weekend in my region consists of Friday and Saturday, the worker cannot resume work during these two days.

I don't want to complicate things further, but 'workday' is not the same as 'not weekend'. There are also official holidays, some of them not fixed at a date.
Additional 'workday' calendar should be maintained separately.