[Solved] Calculated Fields in Base
[Solved] Calculated Fields in Base
Hi
In my Members table I have a DOB category and an AGE category and I would like the AGE to be a calculated field based upon the DOB and the current date. In a spreadsheet the formula is =YEAR(NOW()-"the DOB cell")-1900 but I do not think there is an equivalent formula in SQL.
The DATEDIFF function using the DOB and the current date appears to be the way forward but bearing mind I need a number not a date i am unsure how to achieve this in SQL.
Any help or guidance will be appreciated.
grabro
In my Members table I have a DOB category and an AGE category and I would like the AGE to be a calculated field based upon the DOB and the current date. In a spreadsheet the formula is =YEAR(NOW()-"the DOB cell")-1900 but I do not think there is an equivalent formula in SQL.
The DATEDIFF function using the DOB and the current date appears to be the way forward but bearing mind I need a number not a date i am unsure how to achieve this in SQL.
Any help or guidance will be appreciated.
grabro
Last edited by Hagar Delest on Sun Dec 15, 2013 12:51 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Calculated Fields in Base
Some functions for Base and HSQL can be found at:
http://wiki.services.openoffice.org/wik ... _Functions
http://wiki.services.openoffice.org/wik ... _Functions
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Re: Calculated Fields in Base
You said / asked:
Important Note: The formula you showed above ( =YEAR(NOW()-"the DOB cell")-1900 ) for a Spreadsheet . . . will provide invalid values. For example . . . assume today in YYYY-MM-DD format is 2010-11-21 . . . if "the DOB cell" is 2008-11-20 or 2008-11-21 . . . incorrect values are returned .
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.
Assuming the name of the field in your database is "Date of Birth" ( if not, change the code below to reflect the Field name in your Database Table ) . . . the following calculation will return an Age in years ( whole number ).grabro wrote:I would like the AGE to be a calculated field based upon the DOB and the current date.
Code: Select all
DATEDIFF('YY', "Date of Birth", CURRENT_DATE) - CASEWHEN((MONTH("Date of Birth")*100 + DAYOFMONTH("Date of Birth")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0)
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.
Re: Calculated Fields in Base
Sliderule
Thank you for your help. I am sure this will work if only I can now work out the SQL syntax for it. Those I have trie keep giving me an error.
Incidentally in MYSQL the following query works OK
SELECT Members SET AGE=((DATEDIFF(CURDATE(),DOB))/365 where ID<609
I'll get there eventually
grabro
Thank you for your help. I am sure this will work if only I can now work out the SQL syntax for it. Those I have trie keep giving me an error.
Incidentally in MYSQL the following query works OK
SELECT Members SET AGE=((DATEDIFF(CURDATE(),DOB))/365 where ID<609
I'll get there eventually
grabro
Re: Calculated Fields in Base
grabro:
You comment above does not make sense to be. Please be specific . . . ¿ ¿ ¿ what is your database engine . . . is it HSQL ( the default used by OpenOffice Base ) . . . or . . . MySQL ( which you mentioned in your above post ) . . . or some other Database Engine ? ? ?
Please tell me EXACTLY what the status bar at the bottom displays, after you open your OpenOffice *.odb file?
Sliderule
You comment above does not make sense to be. Please be specific . . . ¿ ¿ ¿ what is your database engine . . . is it HSQL ( the default used by OpenOffice Base ) . . . or . . . MySQL ( which you mentioned in your above post ) . . . or some other Database Engine ? ? ?
Please tell me EXACTLY what the status bar at the bottom displays, after you open your OpenOffice *.odb file?
Sliderule
Re: Calculated Fields in Base
Sliderule
Sorry i was unclear. I am using HSQL. The database was developed by Openoffice Base. My reference to Mysql was mainly because I was under the impression that the SQL syntax was standard throughout all databases.
Originally I was using Mysql but I could not get it to work through oOo Base; it does not recognize the JBDC files. Accordingly I set up the oOoBase system as i am hoping that eventually it will be set up so that people used to Microsoft Access can use it without any problems.
So, Ignore my reference to Mysql.
grabro
Sorry i was unclear. I am using HSQL. The database was developed by Openoffice Base. My reference to Mysql was mainly because I was under the impression that the SQL syntax was standard throughout all databases.
Originally I was using Mysql but I could not get it to work through oOo Base; it does not recognize the JBDC files. Accordingly I set up the oOoBase system as i am hoping that eventually it will be set up so that people used to Microsoft Access can use it without any problems.
So, Ignore my reference to Mysql.
grabro
Re: Calculated Fields in Base
Since you are using HSQL ( my original assumption ) as your database engine . . . you may use EITHER of code samples below to calculate AGE . . . but change "Date of Birth" ( three times ) to the actual field in your database table:
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.
Code: Select all
DATEDIFF('YY', "Date of Birth", CURRENT_DATE) - CASEWHEN((MONTH("Date of Birth")*100 + DAYOFMONTH("Date of Birth")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0)
or
YEAR(CURRENT_DATE) - YEAR("Date of Birth") - CASEWHEN(MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE) >= MONTH("Date of Birth")*100 + DAYOFMONTH("Date of Birth"),0,1)
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: Calculated Fields in Base
Hello Sliderule,
Your sample code looks like it would solve my challenge, not quite sure. I want the same as the originator of the thread, a number/age. The difference is I want it to display in a form. Where would I put it...in the Control or on the form with the Control? As usual your help is appreciated.
Your sample code looks like it would solve my challenge, not quite sure. I want the same as the originator of the thread, a number/age. The difference is I want it to display in a form. Where would I put it...in the Control or on the form with the Control? As usual your help is appreciated.
Last edited by iqullc on Mon Dec 09, 2013 3:48 pm, edited 1 time in total.
Re: Calculated Fields in Base
Code: Select all
SELECT *,
DATEDIFF('YY', "Date of Birth", CURRENT_DATE) - CASEWHEN((MONTH("Date of Birth")*100 + DAYOFMONTH("Date of Birth")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0) AS "Age"
FROM "YourTable"
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Calculated Fields in Base
Wow thanks Villeroy for the quick reply...trying it out. Will let you know.
Windows7 AOO 4.0.0
Re: Calculated Fields in Base
Villeroy/Sliderule,
I got the following syntax error message:
Unexpected "" expecting From or INTO
When opening the Form.
I got the following syntax error message:
Unexpected "" expecting From or INTO
When opening the Form.
Windows7 AOO 4.0.0
Re: Calculated Fields in Base
iqullc:
You said:
[quote=""iqullc"]I got the following syntax error message:
Unexpected "" expecting From or INTO[/quote]
Therefore, you made a mistake. Since, you have elected NOT to inform us exactly what your Query looks like, the only answer, based on the information you have provided is, you made a mistake.
Sliderule
You said:
[quote=""iqullc"]I got the following syntax error message:
Unexpected "" expecting From or INTO[/quote]
Therefore, you made a mistake. Since, you have elected NOT to inform us exactly what your Query looks like, the only answer, based on the information you have provided is, you made a mistake.
Sliderule
Re: Calculated Fields in Base
Oops...I thought you could see the thread.
SELECT *,
DATEDIFF('YY', "Date of Birth", CURRENT_DATE) - CASEWHEN((MONTH("Date of Birth")*100 + DAYOFMONTH("Date of Birth")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0) AS "Age"
FROM "YourTable"
I replaced "Date of Birth" with "Bday" and "your table" with "Patients"
SELECT *,
DATEDIFF('YY', "Date of Birth", CURRENT_DATE) - CASEWHEN((MONTH("Date of Birth")*100 + DAYOFMONTH("Date of Birth")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0) AS "Age"
FROM "YourTable"
I replaced "Date of Birth" with "Bday" and "your table" with "Patients"
Windows7 AOO 4.0.0
Re: Calculated Fields in Base
I asked you to show us EXACTLY the SQL you wrote . . . and . . . you replied above:
Change the above to:
Explanation: Since, you want to include all of the columns ( fields ) from the table "Patients" in the RESULT SET, you should include that table name BEFORE the * in the Query. I replaced the table name to "Patients, and, the column containing the date to, "Bday" .
Sliderule
Code: Select all
SELECT *,
DATEDIFF('YY', "Date of Birth", CURRENT_DATE) - CASEWHEN((MONTH("Date of Birth")*100 + DAYOFMONTH("Date of Birth")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0) AS "Age"
FROM "YourTable"
Code: Select all
SELECT "Patiens".*,
DATEDIFF('YY', "Bday", CURRENT_DATE) - CASEWHEN((MONTH("Bday")*100 + DAYOFMONTH("Bday")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0) AS "Age"
FROM "Patients"
Sliderule
Re: Calculated Fields in Base
Try the joker with table name:
SELECT "Patients".*, ...etc...
SELECT "Patients".*, ...etc...
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Calculated Fields in Base
Fabulous, thanks Sliderule and Villeroy. Everything is working.
On the calculation script and error message...duh on my part
See you around the Forum.
Stephanie
On the calculation script and error message...duh on my part
See you around the Forum.
Stephanie
Windows7 AOO 4.0.0
Re: [Solved] Calculated Fields in Base
Thanks Sliderule! Just downloaded Base today and needed the Age calculation. Worked perfect first time. Just need something that will return 'blank' when there is no birthday. Steep learning curve! ;>)
Apache OpenOffice 4.1.3 Windows 7
Re: [Solved] Calculated Fields in Base
Welcome to the OpenOffice / LibreOffice Base forum.Quadium wrote:Thanks Sliderule! Just downloaded Base today and needed the Age calculation. Worked perfect first time. Just need something that will return 'blank' when there is no birthday. Steep learning curve! ;>)
Use the following code:
Code: Select all
SELECT
"Patients".*,
COALESCE(DATEDIFF('YY', "Bday", CURRENT_DATE) - CASEWHEN((MONTH("Bday")*100 + DAYOFMONTH("Bday")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0),NULL) AS "Age"
FROM "Patients"
Sliderule
Re: [Solved] Calculated Fields in Base
Sliderule - When I use DATEDIFF( 'YY', "G_DateOfBirth", CURRENT_DATE ) - CASEWHEN( ( MONTH( "G_DateOfBirth" ) * 100 + DAYOFMONTH( "G_DateOfBirth" ) ) > ( MONTH( CURRENT_DATE ) * 100 + DAYOFMONTH( CURRENT_DATE ) ), 1, 0 ) in the Field of a Query, with Age as the Alias, it returns the Age properly, but Age is 0 when DateOfBirth is blank. When I try to modify it to COALESCE(same as previous,NULL) I get an error: The column 'COALESCE(DATEDIFF( 'YY', "G_DateOfBirth", CURRENT_DATE ) - CASEWHEN( ( MONTH( "G_DateOfBirth" ) * 100 + DAYOFMONTH( "G_DateOfBirth" ) ) > ( MONTH( CURRENT_DATE ) * 100 + DAYOFMONTH( CURRENT_DATE ) ), 1, 0 ),NULL)' is unknown. What am I doing wrong?
Apache OpenOffice 4.1.3 Windows 7
Re: [Solved] Calculated Fields in Base
Please note, in my opinion, your database should NEVER have a NULL ( undefined ) value, whether with a TEXT column, a DATE column, a TIME column, a TIMESTAMP column, a BOOLEAN column, nor, a NUMERIC column.
Try the following code:
Or, perhaps the following code . . . that MUST be run withOUT the Base Parser:
Explanation: Changed from the previously posted COALASE function to a CASE WHEN . . . THEN . . . ELSE . . . END CASE statement.
I hope this helps, please be sure to let me / us know.
Sliderule
Try the following code:
Code: Select all
SELECT
"Patients".*,
CASE WHEN "Bday" IS NULL THEN NULL ELSE DATEDIFF('YY', "Bday", CURRENT_DATE) - CASEWHEN((MONTH("Bday")*100 + DAYOFMONTH("Bday")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0) END CASE AS "Age"
FROM "Patients"
Code: Select all
SELECT
"Patients".*,
CASEWHEN ("Bday" IS NULL, NULL, DATEDIFF('YY', "Bday", CURRENT_DATE) - CASEWHEN((MONTH("Bday")*100 + DAYOFMONTH("Bday")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0)) AS "Age"
FROM "Patients"
I hope this helps, please be sure to let me / us know.
Sliderule
Re: [Solved] Calculated Fields in Base
Sliderule - Same error message for both new suggested formulas. If you can access my e-mail address, send me a quick note, and I'll forward the database. It's a small one I created just to start learning what I need. Thx.
Apache OpenOffice 4.1.3 Windows 7