Page 1 of 1

[Solved] Calculated Fields in Base

Posted: Sun Nov 21, 2010 1:09 pm
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

Re: Calculated Fields in Base

Posted: Sun Nov 21, 2010 2:42 pm
by Arineckaig
Some functions for Base and HSQL can be found at:
http://wiki.services.openoffice.org/wik ... _Functions

Re: Calculated Fields in Base

Posted: Sun Nov 21, 2010 6:12 pm
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.

Re: Calculated Fields in Base

Posted: Sun Nov 21, 2010 7:22 pm
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

Re: Calculated Fields in Base

Posted: Sun Nov 21, 2010 7:38 pm
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

Re: Calculated Fields in Base

Posted: Mon Nov 22, 2010 12:32 am
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

Re: Calculated Fields in Base

Posted: Mon Nov 22, 2010 1:17 am
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.

Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 3:13 pm
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.

Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 3:27 pm
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.

Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 3:56 pm
by iqullc
Wow thanks Villeroy for the quick reply...trying it out. Will let you know. :)

Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 4:07 pm
by iqullc
Villeroy/Sliderule,

I got the following syntax error message:

Unexpected "" expecting From or INTO

When opening the Form.

Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 5:17 pm
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

Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 7:06 pm
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"

Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 7:17 pm
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

Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 7:22 pm
by Villeroy
Try the joker with table name:
SELECT "Patients".*, ...etc...

Re: Calculated Fields in Base

Posted: Thu Dec 12, 2013 6:34 pm
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

Re: [Solved] Calculated Fields in Base

Posted: Sat Mar 11, 2017 3:41 am
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! ;>)

Re: [Solved] Calculated Fields in Base

Posted: Sat Mar 11, 2017 7:29 am
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

Re: [Solved] Calculated Fields in Base

Posted: Sun Mar 12, 2017 6:14 pm
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?

Re: [Solved] Calculated Fields in Base

Posted: Sun Mar 12, 2017 6:25 pm
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

Re: [Solved] Calculated Fields in Base

Posted: Sun Mar 12, 2017 7:21 pm
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.