[Solved] Calculating age

Discuss the database features

[Solved] Calculating age

Postby crainneag » Tue Sep 16, 2014 3:23 pm

Hi, can anyone help me with a technique to calculate a person's age from their date of birth in Base? I would like it to be present in a table and form so that it updates year on year.
Last edited by crainneag on Wed Sep 24, 2014 4:12 pm, edited 1 time in total.
OpenOffice 4.1.1 on Windows 7
crainneag
 
Posts: 16
Joined: Fri Sep 12, 2014 11:06 pm

Re: Calaculating age

Postby Villeroy » Tue Sep 16, 2014 4:12 pm

http://www.hsqldb.org/doc/1.8/guide/ch0 ... ed-section
DATEDIFF('year',"Date Field",CURRENT_DATE)
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: 26980
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calaculating age

Postby Sliderule » Tue Sep 16, 2014 4:19 pm

Since you have NOT indicated which database back-end ( engine ) you are using . . . I will assume you are using HSQL . . . and . . . this technique can be used either with the Embedded Database engine ( HSQL 1.8.0.10 ) as the date ( database pun intended ) I am writing this, OR, a more advanced version of HSQL ( such as HSQL 2.3.2 ).

Additionally, I am assuming, you desire a result as an INTEGER ( not a decimal ). So, for example, if today is '2014-09-16 and the contents of your table has a field, with a name of, "DATE_OF_BIRTH" and the values for that field are:

  1. '1999-09-15' it will return 18
  2. '1996-09-16' it will return 18
  3. '1996-09-17' it will return 17
On the Field line while creating your Query . . . be sure to change "DATE_OF_BIRTH" to whatever is the field in your table for the date of birth:

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

Explanation: Since, you NEVER should define the age as a field in a database table, since, it can change, it should be calculated when needed. The above formula, will exactly that. You can, of course, define a VIEW with the row contents, and, this calculation. :)

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this is resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1176
Joined: Thu Nov 29, 2007 9:46 am

Re: Calaculating age

Postby Sliderule » Tue Sep 16, 2014 4:23 pm

WARNING . . . the calculation displayed by Villeroy above ( using DATEDIFF ), will, provide INCORRECT values. Try it with the examples ( values ) I described in my post above to 'see' them.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1176
Joined: Thu Nov 29, 2007 9:46 am

Re: Calaculating age

Postby crainneag » Tue Sep 16, 2014 5:46 pm

Thanks to both of you, I will check the preferred method once I geet access to my paper based source data, As I read in a csv file from another database Base is arguing about me about the way dates have been transferred. I will get back to you on this presently.

You say Datediff will produce erroneous results, will it be a consistent, predictable error or a variable one?
OpenOffice 4.1.1 on Windows 7
crainneag
 
Posts: 16
Joined: Fri Sep 12, 2014 11:06 pm

Re: Calaculating age

Postby Sliderule » Tue Sep 16, 2014 6:04 pm

crainneag wrote:You say Datediff will produce erroneous results, will it be a consistent, predictable error or a variable one?

Yes, it is "predictable" . . . BUT . . . I do not understand your question. Since it is in error ( wrong results to calculate an age, depending on the MONTH and DAY ) why would you EVER use it to calculate an age, in years ? Put another way, the "predictability" of an error, is totally irrelevant.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this is resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1176
Joined: Thu Nov 29, 2007 9:46 am

Re: Calaculating age

Postby crainneag » Tue Sep 16, 2014 6:16 pm

I ask because I came across a similar problem using Lotus Approach where I used this formula to calculate age from a DOB and display it on the form "If(Year(Today() - patients.DOB) - 1 < 120, Year(Today() - patients.DOB) - 1, 0)". This produced a constant error of one year over the true value and was corrected by subtracting 1, that is why I asked about the nature of the error, if it is constant then adding a negative of the same value will eliminate it.
OpenOffice 4.1.1 on Windows 7
crainneag
 
Posts: 16
Joined: Fri Sep 12, 2014 11:06 pm

Re: Calaculating age

Postby Sliderule » Tue Sep 16, 2014 8:16 pm

The reason you had that error, with the formula you described using with Lotus Approach ( or any other database for that matter ) . . . is because it ( the formula ) does NOT take the current year, the current month, and, the current day, compared to the month and day ( and of course year too ) of the "DATE_OF_BIRTH" date value.

That is exactly, why, the formula ( calculation ) I provided does work, as, I think you want it. If not, let me / us know.

If you upgrade to a SPLIT database, using HSQL ( external to OpenOffice / LibreOffice Base ) . . . for example . . . HSQL 2.3.2 . . . ( NOT the embedded database, HSQL 1.8.0.10 ) I can provide you several user defined functions, so, you can have it ( the database back-end ) calculate AGE as an INTEGER, a DECIMAL, or, a FLOAT number ( 17 decimal place accuracy ), by just entering two dates, for example:

Code: Select all   Expand viewCollapse view
AGE("DATE_OF_BIRTH", CURRENT_DATE)

AGE_DECIMAL("DATE_OF_BIRTH", CURRENT_DATE)

AGE_FLOAT("DATE_OF_BIRTH", CURRENT_DATE)


Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this is resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1176
Joined: Thu Nov 29, 2007 9:46 am

Re: Calaculating age

Postby however » Sat Sep 20, 2014 5:21 pm

Hi,

Sliderule wrote:Since you have NOT indicated which database back-end ( engine ) you are using . . . I will assume you are using HSQL . . .

Since I am interested in adding this little feature to my project, I find it unnecessary to open a new thread with the same title so, hoping that no one would mind, I would like to jump in and ask this:

how/where would it change if I used MySQL as back-end, instead of HSQ?

Regards
ApacheOpenOffice 4.1 on Slackware 14.1
however
 
Posts: 160
Joined: Fri Mar 02, 2012 3:46 pm

Re: Calaculating age

Postby Sliderule » Sat Sep 20, 2014 5:44 pm

however wrote:how/where would it change if I used MySQL as back-end, instead of HSQ?


With MySQL as your database back-end . . . use the following formula, to calculate AGE as an integer, and, `DATE_OF_BIRTH` is the column name in your table ( `DATE_OF_BIRTH` is used three times in formula below -- change to your table column name :super: ) .

Code: Select all   Expand viewCollapse view
YEAR(CURRENT_DATE) - YEAR(`DATE_OF_BIRTH`) - IF(MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE) >= MONTH(`DATE_OF_BIRTH`)*100 + DAYOFMONTH(`DATE_OF_BIRTH`),0,1)

Please do me a favour, let me / us know how the above works for you, when using MySQL . . . that is . . . does it meet your needs, or, not really. :)

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1176
Joined: Thu Nov 29, 2007 9:46 am

Re: Calaculating age

Postby however » Wed Sep 24, 2014 2:16 pm

Sliderule wrote:Please do me a favour, let me / us know how the above works for you, when using MySQL . . . that is . . . does it meet your needs, or, not really.


It works like a charm!creaed a query in SQL view, copied&pasted your calculation code and changed the Date_Of_Birth name with my Birthdate corresponding name and magic happened.

However, if I may sound really silly, I am struggling now to actually add this new column "Age") in the form.

So far I have:
1) added a new 'Age' column to the 'Patient' table, and marked it as INTEGER;
2) created a 'qry_age' and run it, successfully,
3) edited the 'Patient Details' form by adding a 'Age' list box and linking it the 'qry_age', created previously,

when I open the 'PatientDetails' form, no age is shown on the newly added listbox. I have the same result if instead of linking the 'Age' listbox to the 'qry_age', i link it to an SQL command (the calculation).

I havent tried to create a 'Age' subform and adding it to the original 'PatientDetails' form. Would it be the correct way of doing this?

What am i missing?
ApacheOpenOffice 4.1 on Slackware 14.1
however
 
Posts: 160
Joined: Fri Mar 02, 2012 3:46 pm

Re: Calaculating age

Postby Villeroy » Wed Sep 24, 2014 2:27 pm

The information about the age is stored in the date field. Simply do not store any calculated values. Never. Now you store an age of 56 for someone. Next year he is 57 but your age field keeps a stored age of 56.
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: 26980
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calaculating age

Postby however » Wed Sep 24, 2014 2:39 pm

Villeroy wrote:The information about the age is stored in the date field. Simply do not store any calculated values. Never. Now you store an age of 56 for someone. Next year he is 57 but your age field keeps a stored age of 56.

am i right to understand that I should not have a 'Age' column in the 'Patient' table? but rather use the calculated age from the query?

Still quite confused though, on how i'm going to have it to work on the form, as I have just tried adding a 'AgeSubform' in the 'PatientDetails' form, linkying it to the 'qry_age' and still with the same result: no age.
ApacheOpenOffice 4.1 on Slackware 14.1
however
 
Posts: 160
Joined: Fri Mar 02, 2012 3:46 pm

Re: Calaculating age

Postby however » Wed Sep 24, 2014 2:44 pm

[
however wrote:Still quite confused though,

the coin dropped just as I clicke on submit.
I have solved by replacing the list box with a formatted field.
Thank you.
p.s.: unfortunately, I cannot add SOLVED to the post, as it wasn't mine initially, but I'd be happy to add it to my section if that's the correct way of doing it.
Last edited by however on Wed Sep 24, 2014 2:47 pm, edited 1 time in total.
ApacheOpenOffice 4.1 on Slackware 14.1
however
 
Posts: 160
Joined: Fri Mar 02, 2012 3:46 pm

Re: Calaculating age

Postby Villeroy » Wed Sep 24, 2014 2:46 pm

No, you must not have an age field in a table of persons.
On the form you do not "work" with the age. You display the age that results from the stored birth date.

SELECT *,
YEAR(CURRENT_DATE) - YEAR(`DATE_OF_BIRTH`) - IF(MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE) >= MONTH(`DATE_OF_BIRTH`)*100 + DAYOFMONTH(`DATE_OF_BIRTH`),0,1) AS `Age`
FROM `Patients`

selects all the fields ( * ) and the calculated age from the patients table. You can use this query in the exact same manner as the table itself. The age can not be editied. If the age of a patient is wrong, then you've got to correct the birth date.
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: 26980
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calaculating age

Postby Sliderule » Wed Sep 24, 2014 3:54 pm

however:

Thank-you for both understanding correctly, that the calculation / field is NOT a part of the table, but, yes, it can be either in a Query, or, a View. This way, regardless of a Date change . . . for example, the CURRENT_DATE changes, whether with a year of 2004, 2014, or, 2024, the CALCULATED value of "AGE" will be changed. :)

And, however, I thank-you for indicating in the this forum, that the 'calculation' is correct, in your case, when using MySQL as your database back-end, so, other forum users may use it ( calculation of AGE )/find it too. :)

I am however ( use of the word however is a pun with your forum name :) ) still waiting for the forum Original Poster to modify the forum's top title to [Solved] so when other users search the forum, they will find a usable result more easily.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1176
Joined: Thu Nov 29, 2007 9:46 am


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests