Let’s say I have a “NbDays” field in a form where I can manually insert a number, like 5 for 5 days.
.
I would like this number to autodecrement when opening the DB. Autodecrement should only be done based on the number of days passed. For example, if 3 days have passed before opening the DB, when it is opened again this field will be decremented by 3 automatically and will display 2. If all days have passed, the field will simply be empty. Obviously no decrement can be made within the same day.
.
Is it possible to implement this type of behavior?
Does this require additional fields?
If so, can anyone give me a solution to accomplish this task?
You will need a field for a date and a field for the days to add.
Simple code with internal Firebird:
SELECT DATEDIFF(DAY, CURRENT_DATE, "Datefield"+"DatesToAdd") AS "RestDays" FROM "Table"
Now you get the difference.
You could get the difference down to 0 by
SELECT CASE WHEN DATEDIFF(DAY, CURRENT_DATE, "Datefield"+"DatesToAdd") > 0 THEN DATEDIFF(DAY, CURRENT_DATE, "Datefield"+"DatesToAdd") > 0 ELSE 0 END AS "RestDays" FROM "Table"
And now a possibility with internal HSQLDB:
SELECT (DATEDIFF('dd', '1899-12-30', "Datefield") + "Datefield"+"DatesToAdd") - DATEDIFF('dd', '1899-12-30', CURRENT_DATE) AS "RestDays" FROM "Table"
Code is a little bit longer, but it will give the days as the first query in Firebird. Procedure for getting only values down to 0 will work also with CASE WHEN ….
@RobertG,
.
I’m sorry, I forgot to specify that for now I was using integrated LO Base HSQLBD.
.
Version: 7.6.4.1 (X86_64) / LibreOffice Community
Build ID: e19e193f88cd6c0525a17fb7a176ed8e6a3e2aa1
CPU threads: 16; OS: Windows 10.0 Build 22631; UI render: Skia/Raster; VCL: win
Locale: fr-CA (fr_CA); UI: fr-FR
Calc: CL threaded