[Solved] DateCalc HSQL2.5 backend or H2 backend

Creating tables and queries
Post Reply
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

[Solved] DateCalc HSQL2.5 backend or H2 backend

Post by gkick »

Hi,

My contacts table has several fields for date alerts like
Flagtype LookupTable, Birthday date datatype, Flag boolean, RemindMePriorTo integer
What I would like to achieve is to show a set like...

FlagType Contact NextBD Turning FlagDate
Birthday Joe Blow 17/01/2020 62 NextBD-days priorto

Having problems using the value of a calculated field in a secondary calculation (unknown field errors)

Another more tricky one is the FollowUp for contact where there is only a boolean follow up field and a in x day field, the idea being follow up in 20 days from now, guess this could be done with a timestamp in a dummy table or putting CURDATE() into some string where now will remain static?
Thanks for your thoughts.
GK
Last edited by gkick on Fri Aug 30, 2019 5:19 pm, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DateCalc HSQL2.5 backend or H2 backend

Post by Villeroy »

HSQL 2.5:

Code: Select all

SELECT "Table".*, dateadd( 'mm', 15, "D" ) AS "+15 months" FROM "Table" WHERE DATEDIFF( 'dd', "D", CURRENT_DATE ) <= 30
selects all fields plus a calculated date field "+15 months" not older than 30 days.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DateCalc HSQL2.5 backend or H2 backend

Post by Villeroy »

Another example converting German banking dates, imported as text from csv, into valid date values:

Code: Select all

SELECT CAST(REPLACE(REPLACE(SUBSTR("VALUTA",7,4)||'-'||SUBSTR("VALUTA",4,2)||'-'||SUBSTR("VALUTA",1,2),'-02-29','-02-28'),'-02-30','-02-28')AS DATE)AS "DATUM"
"VALUTA" has German date strings like 03.12.2018 including invalid February dates 29.2. and 30.2. because the banksters calculate with months of 30 days.
The expression replaces 29.2. and 30.2. with 28.2. (which is OK for my purposes) and concatenates 03.12.2018 to ISO date 2018-12-03 which can be converted to a true date value by means of CAST(ISO_Date_String AS DATE).
Calculated dates may show up as integers in the Base GUI. This is not a problem. Just format these day numbers when they appear in form controls or report cells.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: DateCalc HSQL2.5 backend or H2 backend

Post by gkick »

Thank you very much Villeroy,

I am almost getting there,

I need to modify the code below that the Age will be the Age on the next Birthday, in the case of Kick it would be 62 as January has already passed, same with the nextdb calculation which should show 17/01/2020

Code: Select all

SELECT "LastName", "DateOfBirth", "nPrior", "BdR",YEAR( NOW( ) ) - YEAR( "DateOfBirth" ) as "Age",DATEADD(YEAR,DATEDIFF(YEAR,"DateOfBirth",CURDATE()),"DateOfBirth") 
AS "nextdb" FROM "tblContacts" WHERE "BdR" = TRUE
mfg
GK
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DateCalc HSQL2.5 backend or H2 backend

Post by Villeroy »

Searching for keywords "Birthday Age" in the Base forum: viewtopic.php?f=13&t=36069&p=412463&hil ... ge#p412463
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: DateCalc HSQL2.5 backend or H2 backend

Post by Sliderule »

If you are using an HSQL database, that is NOT an Embedded HSQL NOR an External HSQL that is Version 1.8.0.10, you can easily write your own AGE function(s). Below are examples:
  • AGE that returns an INTEGER, with TWO DATE inputs: STARTDATE and ENDDATE
  • AGE_FLOAT that returns a FLOAT NUMBER, with TWO DATE inputs: STARTDATE and ENDDATE
The function(s) are created once for the database, and, available afterwords with any Query, Insert, Update, Merge, Delete statement.

From the OpenOffice / LibreOffice Base File ( *.odb ) Menu: Tools -> SQL...

Code: Select all

-- Below function calculates an AGE in YEARS by input of two Database Date values

CREATE FUNCTION AGE(IN STARTDATE DATE, IN ENDDATE DATE) 
RETURNS INTEGER  
LANGUAGE SQL NOT DETERMINISTIC 
CONTAINS SQL 
CALLED ON NULL INPUT 
RETURN YEAR(ENDDATE) - YEAR(STARTDATE) - CASE WHEN MONTH(ENDDATE)*100+DAYOFMONTH(ENDDATE)>=MONTH(STARTDATE)*100+DAYOFMONTH(STARTDATE)THEN 0 ELSE 1 END

Code: Select all

-- Below function calculates an AGE in YEARS, as a FLOAT ( integer with decimal places ) by input of two Database Date values

CREATE FUNCTION AGE_FLOAT(IN STARTDATE DATE, IN ENDDATE DATE) 
RETURNS DOUBLE 
LANGUAGE SQL NOT DETERMINISTIC 
CONTAINS SQL 
CALLED ON NULL INPUT 
RETURN CASE WHEN TO_CHAR(ENDDATE,'MMDD')=TO_CHAR(STARTDATE,'MMDD')THEN YEAR(ENDDATE)-YEAR(STARTDATE)WHEN YEAR(ENDDATE)=YEAR(STARTDATE)AND TO_CHAR(ENDDATE,'MMDD')<TO_CHAR(STARTDATE,'MMDD')THEN CAST(YEAR(ENDDATE)-YEAR(STARTDATE)+1-(DAYOFYEAR(STARTDATE)*1.0E0/DAYOFYEAR(ENDDATE))AS FLOAT)WHEN YEAR(ENDDATE)>YEAR(STARTDATE)THEN CAST(YEAR(ENDDATE)-YEAR(STARTDATE)-((DAYOFYEAR(STARTDATE)-DAYOFYEAR(ENDDATE))/365.0E0)AS FLOAT)WHEN YEAR(ENDDATE)=YEAR(STARTDATE)THEN CAST(((DAYOFYEAR(ENDDATE)-DAYOFYEAR(STARTDATE))/365.0E0)AS FLOAT) END
So, try following Query, to see how it works:

Code: Select all

Select 
   INFORMATION_SCHEMA.TABLES.TABLE_NAME,
   AGE(NULL, CURRENT_DATE) as "AGE_NULL",
   AGE( CAST(TO_DATE('30.12.99', 'DD.MM.YY') as DATE), CURRENT_DATE) as "AGE_CUR_DATE",
   AGE("MY_BIRTH_DATE_COLUMN", CURRENT_DATE) as "AGE_BIRTH_DATE",
   AGE_FLOAT(NULL, CURRENT_DATE) as "AGE_FLOAT_NULL",
   AGE_FLOAT( CAST(TO_DATE('30.12.99', 'DD.MM.YY') as DATE), CURRENT_DATE) as "AGE_FLOAT_CUR_DATE"
   AGE_FLOAT("MY_BIRTH_DATE_COLUMN", CURRENT_DATE) as "AGE_FLOAT_BIRTH_DATE",
From INFORMATION_SCHEMA.TABLES
Where INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = CURRENT_SCHEMA 
Order By INFORMATION_SCHEMA.TABLES.TABLE_NAME ASC
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: DateCalc HSQL2.5 backend or H2 backend

Post by gkick »

Thank you for your help Sliderule, very much appreciated. Loaded both functions via Tools, SQL, however running the query produces an error message.

Code: Select all

unexpected token: AGE_FLOAT : line: 8 in statement [Select
   INFORMATION_SCHEMA.TABLES.tblContacts,
   AGE(NULL, CURRENT_DATE) as "AGE_NULL",
   AGE( CAST(TO_DATE('30.12.19', 'DD.MM.YY') as DATE), CURRENT_DATE) as "AGE_CUR_DATE",
   AGE("DateOfBirth", CURRENT_DATE) as "DateOfBirth",
   AGE_FLOAT(NULL, CURRENT_DATE) as "AGE_FLOAT_NULL",
   AGE_FLOAT( CAST(TO_DATE('30.12.20', 'DD.MM.YY') as DATE), CURRENT_DATE) as "AGE_FLOAT_CUR_DATE"
   AGE_FLOAT("DateOfBirth", CURRENT_DATE) as "AGE_FLOAT_BIRTH_DATE",
From INFORMATION_SCHEMA.TABLES
Where INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = CURRENT_SCHEMA
Order By INFORMATION_SCHEMA.TABLES.tblContacts ASC]
Do I need to put the table name in quotes?

kind regards
GK
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: DateCalc HSQL2.5 backend or H2 backend

Post by Sliderule »

Below is just a SAMPLE Query.

Code: Select all

Select 
   INFORMATION_SCHEMA.TABLES.TABLE_NAME,
   AGE(NULL, CURRENT_DATE) as "AGE_NULL",
   AGE( CAST(TO_DATE('30.12.99', 'DD.MM.YY') as DATE), CURRENT_DATE) as "AGE_CUR_DATE",
   AGE( {D '1999-12-30'}, CURRENT_DATE) as "AGE_BIRTH_DATE",
   AGE_FLOAT(NULL, CURRENT_DATE) as "AGE_FLOAT_NULL",
   AGE_FLOAT( CAST(TO_DATE('30.12.99', 'DD.MM.YY') as DATE), CURRENT_DATE) as "AGE_FLOAT_CUR_DATE",
   AGE_FLOAT({D '1999-12-30'}, CURRENT_DATE) as "AGE_FLOAT_BIRTH_DATE"
From INFORMATION_SCHEMA.TABLES
Where INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = CURRENT_SCHEMA 
Order By INFORMATION_SCHEMA.TABLES.TABLE_NAME ASC
To run it with a Query from YOUR TABLES, and, COLUMN NAME, this is an Example:

Code: Select all

Select 
   "MY_TABLE_NAME".*,
   AGE("MY_TABLE_NAME"."BIRTH_DATE", CURRENT_DATE) as "AGE_INTEGER",
   -- This Returns integar with 13 decimals
   AGE_FLOAT("MY_TABLE_NAME"."BIRTH_DATE", CURRENT_DATE) as "AGE_FLOAT" 
From "MY_TABLE_NAME"
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Last edited by Sliderule on Fri Aug 30, 2019 5:09 pm, edited 1 time in total.
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: DateCalc HSQL2.5 backend or H2 backend

Post by gkick »

Yuhuu, thanks a million, works like a charm, now could I use a similar function to return the date of my next birthday which I need for another query to filter upcoming birthdays. My bd is 17/01/1958,since we are now in September the query should show 17/01/2020 as the next birthday without hard coding next year. Thanks a lot!
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: DateCalc HSQL2.5 backend or H2 backend

Post by Sliderule »

gkick wrote:Yuhuu, thanks a million, works like a charm, now could I use a similar function to return the date of my next birthday which I need for another query to filter upcoming birthdays. My bd is 17/01/1958,since we are now in September the query should show 17/01/2020 as the next birthday without hard coding next year. Thanks a lot!
Yes, you can use it, like any other HSQL DATABASE function.

For EXAMPLE:

Code: Select all

Select 
   "MY_TABLE_NAME".*,
   AGE("MY_TABLE_NAME"."BIRTH_DATE", DATEADD('month',3,CURRENT_DATE) ) as "AGE_INTEGER",
   -- This Returns integar with 13 decimals
   AGE_FLOAT("MY_TABLE_NAME"."BIRTH_DATE", DATEADD('month', 3, CURRENT_DATE) ) as "AGE_FLOAT" 
From "MY_TABLE_NAME"
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved]DateCalc HSQL2.5 backend or H2 backend

Post by gkick »

Thank you!
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Post Reply