## [Solved] Calculated Fields in Base

Discuss the database features

### [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
Last edited by Hagar Delest on Sun Dec 15, 2013 12:51 pm, edited 1 time in total.
Reason: tagged [Solved].
grabro

Posts: 11
Joined: Thu Nov 22, 2007 10:15 pm
Location: Cheshire

### Re: Calculated Fields in Base

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
Arineckaig
Volunteer

Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

### Re: Calculated Fields in Base

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   Expand viewCollapse view
`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 .

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.

Sliderule
Volunteer

Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am

### 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
grabro

Posts: 11
Joined: Thu Nov 22, 2007 10:15 pm
Location: Cheshire

### 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

Sliderule
Volunteer

Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am

### 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
grabro

Posts: 11
Joined: Thu Nov 22, 2007 10:15 pm
Location: Cheshire

### 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:

Code: Select all   Expand viewCollapse view
`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)     orYEAR(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.

Sliderule
Volunteer

Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am

### 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.
Last edited by iqullc on Mon Dec 09, 2013 3:48 pm, edited 1 time in total.
iqullc

Posts: 58
Joined: Thu Mar 28, 2013 5:16 pm

### Re: Calculated Fields in Base

Code: Select all   Expand viewCollapse view
`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, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 27366
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Calculated Fields in Base

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

Villeroy/Sliderule,

I got the following syntax error message:

Unexpected "" expecting From or INTO

When opening the Form.
Windows7 AOO 4.0.0
iqullc

Posts: 58
Joined: Thu Mar 28, 2013 5:16 pm

### Re: Calculated Fields in Base

iqullc:

You said:

"iqullc wrote:I got the following syntax error message:

Unexpected "" expecting From or INTO

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

Sliderule
Volunteer

Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am

### 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"
Windows7 AOO 4.0.0
iqullc

Posts: 58
Joined: Thu Mar 28, 2013 5:16 pm

### Re: Calculated Fields in Base

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

Code: Select all   Expand viewCollapse view
`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   Expand viewCollapse view
`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

Sliderule
Volunteer

Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am

### Re: Calculated Fields in Base

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, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 27366
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### 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
Windows7 AOO 4.0.0
iqullc

Posts: 58
Joined: Thu Mar 28, 2013 5:16 pm

### 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

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

### Re: [Solved] Calculated Fields in Base

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   Expand viewCollapse view
`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

Sliderule
Volunteer

Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am

### 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

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

### 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:

Code: Select all   Expand viewCollapse view
`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   Expand viewCollapse view
`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

Sliderule
Volunteer

Posts: 1191
Joined: Thu Nov 29, 2007 9:46 am

### 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

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

### Who is online

Users browsing this forum: No registered users and 9 guests