[Solved] DateCalc HSQL2.5 backend or H2 backend

Creating tables and queries

[Solved] DateCalc HSQL2.5 backend or H2 backend

Postby gkick » Thu Aug 29, 2019 5:56 pm

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.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 85
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: DateCalc HSQL2.5 backend or H2 backend

Postby Villeroy » Thu Aug 29, 2019 6:15 pm

HSQL 2.5:
Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27216
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DateCalc HSQL2.5 backend or H2 backend

Postby Villeroy » Thu Aug 29, 2019 8:01 pm

Another example converting German banking dates, imported as text from csv, into valid date values:
Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27216
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DateCalc HSQL2.5 backend or H2 backend

Postby gkick » Thu Aug 29, 2019 10:42 pm

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   Expand viewCollapse view
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.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 85
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: DateCalc HSQL2.5 backend or H2 backend

Postby Villeroy » Fri Aug 30, 2019 11:48 am

Searching for keywords "Birthday Age" in the Base forum: viewtopic.php?f=13&t=36069&p=412463&hilit=birthday+age#p412463
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27216
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DateCalc HSQL2.5 backend or H2 backend

Postby Sliderule » Fri Aug 30, 2019 3:52 pm

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   Expand viewCollapse view
-- 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   Expand viewCollapse view
-- 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   Expand viewCollapse view
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.
User avatar
Sliderule
Volunteer
 
Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am

Re: DateCalc HSQL2.5 backend or H2 backend

Postby gkick » Fri Aug 30, 2019 4:23 pm

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   Expand viewCollapse view
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.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 85
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: DateCalc HSQL2.5 backend or H2 backend

Postby Sliderule » Fri Aug 30, 2019 4:43 pm

Below is just a SAMPLE Query.

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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.
User avatar
Sliderule
Volunteer
 
Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am

Re: DateCalc HSQL2.5 backend or H2 backend

Postby gkick » Fri Aug 30, 2019 4:55 pm

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.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 85
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: DateCalc HSQL2.5 backend or H2 backend

Postby Sliderule » Fri Aug 30, 2019 5:09 pm

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   Expand viewCollapse view
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.
User avatar
Sliderule
Volunteer
 
Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am

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

Postby gkick » Fri Aug 30, 2019 5:20 pm

Thank you!
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 85
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest