[Solved] Calculated Fields in Base

Discuss the database features

[Solved] Calculated Fields in Base

Postby grabro » Sun Nov 21, 2010 1:09 pm

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

Postby Arineckaig » Sun Nov 21, 2010 2:42 pm

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

Postby Sliderule » Sun Nov 21, 2010 6:12 pm

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

Re: Calculated Fields in Base

Postby grabro » Sun Nov 21, 2010 7:22 pm

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

Postby Sliderule » Sun Nov 21, 2010 7:38 pm

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

Re: Calculated Fields in Base

Postby grabro » Mon Nov 22, 2010 12:32 am

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

Postby Sliderule » Mon Nov 22, 2010 1:17 am

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)

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

Re: Calculated Fields in Base

Postby iqullc » Mon Dec 09, 2013 3:13 pm

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

Postby Villeroy » Mon Dec 09, 2013 3:27 pm

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

Re: Calculated Fields in Base

Postby iqullc » Mon Dec 09, 2013 3:56 pm

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

Postby iqullc » Mon Dec 09, 2013 4:07 pm

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

Postby Sliderule » Mon Dec 09, 2013 5:17 pm

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

Re: Calculated Fields in Base

Postby iqullc » Mon Dec 09, 2013 7:06 pm

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

Postby Sliderule » Mon Dec 09, 2013 7:17 pm

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

Re: Calculated Fields in Base

Postby Villeroy » Mon Dec 09, 2013 7:22 pm

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

Re: Calculated Fields in Base

Postby iqullc » Thu Dec 12, 2013 6:34 pm

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

Re: [Solved] Calculated Fields in Base

Postby Quadium » Sat Mar 11, 2017 3:41 am

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

Re: [Solved] Calculated Fields in Base

Postby Sliderule » Sat Mar 11, 2017 7:29 am

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

Re: [Solved] Calculated Fields in Base

Postby Quadium » Sun Mar 12, 2017 6:14 pm

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

Re: [Solved] Calculated Fields in Base

Postby Sliderule » Sun Mar 12, 2017 6:25 pm

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

Re: [Solved] Calculated Fields in Base

Postby Quadium » Sun Mar 12, 2017 7:21 pm

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


Return to Base

Who is online

Users browsing this forum: No registered users and 1 guest