[Solved] Calculated Fields in Base

Discuss the database features
Post Reply
grabro
Posts: 11
Joined: Thu Nov 22, 2007 10:15 pm
Location: Cheshire

[Solved] Calculated Fields in Base

Post by grabro »

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
Last edited by Hagar Delest on Sun Dec 15, 2013 12:51 pm, edited 1 time in total.
Reason: tagged [Solved].
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Calculated Fields in Base

Post by Arineckaig »

Some functions for Base and HSQL can be found at:
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
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Calculated Fields in Base

Post by Sliderule »

You said / asked:
grabro wrote:I would like the AGE to be a calculated field based upon the DOB and the current date.
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 ).

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)
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 :crazy: .

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.
grabro
Posts: 11
Joined: Thu Nov 22, 2007 10:15 pm
Location: Cheshire

Re: Calculated Fields in Base

Post by grabro »

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
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Calculated Fields in Base

Post by Sliderule »

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
grabro
Posts: 11
Joined: Thu Nov 22, 2007 10:15 pm
Location: Cheshire

Re: Calculated Fields in Base

Post by grabro »

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
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Calculated Fields in Base

Post by Sliderule »

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:

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)
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.
iqullc
Posts: 58
Joined: Thu Mar 28, 2013 5:16 pm

Re: Calculated Fields in Base

Post by iqullc »

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.
Last edited by iqullc on Mon Dec 09, 2013 3:48 pm, edited 1 time in total.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculated Fields in Base

Post by Villeroy »

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"
Returns everything from "YourTable" plus a calculated field "Age". Replace your form source with this query.
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
iqullc
Posts: 58
Joined: Thu Mar 28, 2013 5:16 pm

Re: Calculated Fields in Base

Post by iqullc »

Wow thanks Villeroy for the quick reply...trying it out. Will let you know. :)
Windows7 AOO 4.0.0
iqullc
Posts: 58
Joined: Thu Mar 28, 2013 5:16 pm

Re: Calculated Fields in Base

Post by iqullc »

Villeroy/Sliderule,

I got the following syntax error message:

Unexpected "" expecting From or INTO

When opening the Form.
Windows7 AOO 4.0.0
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Calculated Fields in Base

Post by Sliderule »

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
iqullc
Posts: 58
Joined: Thu Mar 28, 2013 5:16 pm

Re: Calculated Fields in Base

Post by iqullc »

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"
Windows7 AOO 4.0.0
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Calculated Fields in Base

Post by Sliderule »

I asked you to show us EXACTLY the SQL you wrote . . . and . . . you replied above:

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"
Change the above to:

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"
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculated Fields in Base

Post by Villeroy »

Try the joker with table name:
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
iqullc
Posts: 58
Joined: Thu Mar 28, 2013 5:16 pm

Re: Calculated Fields in Base

Post by iqullc »

Fabulous, thanks Sliderule and Villeroy. Everything is working. :bravo:

On the calculation script and error message...duh on my part :knock:

See you around the Forum.

Stephanie
Windows7 AOO 4.0.0
Quadium
Posts: 3
Joined: Sat Mar 11, 2017 3:31 am

Re: [Solved] Calculated Fields in Base

Post by Quadium »

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
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Calculated Fields in Base

Post by Sliderule »

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! ;>)
Welcome to the OpenOffice / LibreOffice Base forum.

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"
Explanation: Using the COALESCE function, which says, if the first argument is NOT NULL, use it, otherwise, return the second argument . . . in this case . . . NULL .

Sliderule
Quadium
Posts: 3
Joined: Sat Mar 11, 2017 3:31 am

Re: [Solved] Calculated Fields in Base

Post by Quadium »

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
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Calculated Fields in Base

Post by Sliderule »

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:

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"
Or, perhaps the following code . . . that MUST be run withOUT the Base Parser:

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"
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
Quadium
Posts: 3
Joined: Sat Mar 11, 2017 3:31 am

Re: [Solved] Calculated Fields in Base

Post by Quadium »

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
Post Reply