Query to calculate Age in brackets when person is dead. (Libre Office Base)

[Quaresma]

Once again, I come here to ask for your help in building a query to calculate a person’s age.
However, as the table includes living and deceased people, I would like the result of the formula to include in square brackets the age the person was when they died, to distinguish it from that of others who are still alive (current age).
I managed to make the formula with square brackets, in Excel, although I didn’t get the result in red colour, as this would be the intended result for the Libre Office Base query.
Can you help me?
Thank you my friends!
Members.odb (5.3 KB)
Age.xlsx (6.6 KB)

[RobertG]

Switch the query to the following code:

SELECT "tblMembers".*, 
CASE WHEN "DOD" IS NULL THEN 
CAST(DATEDIFF( 'YY', "DOB", CURDATE( ) ) AS VARCHAR(10)) 
ELSE '['||DATEDIFF( 'YY', "DOB", "DOD" )||']' END AS "Age" 
FROM "tblMembers"

When setting brackets round a integer it will be automatically text content. But the first part will produce integer content. So you have to switch the datatype there.

You would never get a colored result in a query except you have set a special format for a decimal or integer value - not for characters. And the color will be decided by the value…

[Quaresma]

Thank you for your great help, RobertG. The red color of the query result for the age of deceased members would be a very interesting signal but not crucial.

[cpb]

@Quaresma,

the DATEDIFF function in embedded hsqldb has a number of issues.

DATEDIFF('YY', '2023-04-01', '2024-03-01') = 1
it should return 0 because the period covered is only 11 months therefore using DATEDIFF to calculate age in years is impractical.
it simply subtracts year(‘2023-04-01’) from year(‘2024-03-01’)
.
the same is true in regard to months where the value of day number must be taken into account.
.
you have a total of 26 records but there are 17 discrepancies, compare this codes result set with your own.
in the sql I used TO_CHAR to format the dates as in portuguese because I felt it important to view the value of year.

select
	"MemberID",
	"Name",
	to_char(DOB, 'DD-MM-yyyy') DOB,
	to_char(DOD, 'DD-MM-yyyy') DOD
	,
	case
		when DOD is null then
			cast(
				year(current_date) - year(DOB)
				-
				case
					when month(DOB) < month(current_date) or (month(DOB) = month(current_date) and dayofmonth(DOB) <= dayofmonth(current_date)) then
						0
					else
						1
				end
				as varchar(5)
			)
	else
		'[' ||
			year(DOD) - year(DOB)
			-
			case
				when month(DOB) < month(DOD) or (month(DOB) = month(DOD) and dayofmonth(DOB) <= dayofmonth(DOD)) then
					0
				else
					1
			end
		|| ']'
	end age_all
from
	"tblMembers"
order by
	"Name"

@Quaresma

didn’t get the result in red

a report can do this just use conditional formatting, see the attachment.

1. open report for edit.
2. select text box [age_all]
3. menu:>Format>Conditional Formatting
   Expression = ‘LEFT([age_all];1)="["’
4. hit Character Formatting
   hit Font Effects
   select Font color
   Age_In_Years.odb (11.5 KB)

[Quaresma]

cpb,
I only saw your reply a few minutes ago and I can’t thank you enough for your fantastic explanation and help. However, I must tell you that the discrepancies had already worried me and I was trying to find a solution on my own, but I was having some difficulty. You came with your help at the right time.
Thank you my friend.
Quaresma

[cpb]

@Quaresma,
all of your members are middle to old age so showing their ages in round years is probably ok.
.
I think that it’s more meaningful to show age in the format of years, months and days especially for infants or youngsters.
.
download the attachment and take a look at the query “qAge_Union_YMD” which is the data-source for the report ‘rAge_Union_YMD’, if you discover any errors then please let me know.
Age_In_Years_2.odb (17.4 KB)

[Quaresma]

Yes, you’re right! My database targets the names and lives of some important politicians from the last century, so the vast majority of them are already deceased therefore showing their ages in round numbers it’s the only accetable way. There are no infants, here!
Your new query “qAge_Union_YMD” is very well done, however, as we like to complicate things, mainly to avoid typing errors, I suggest showing “empty space” or “error” instead of negative ages (if DOD<=DOB) .
Once again you have demonstrated that you are a great human being by sharing your knowledge and fine work, with us people.
Thank you cpb.
Quaresma (Lent)

[cpb]

@Quaresma

to avoid typing errors

we avoid incorrect input by using table constraints.
first ensure that DOB <= DOD for every record in the table “tblMembers”, you can use this sql to check:

select "MemberID" from "tblMembers" where DOB > DOD;

then run this from menu:>Tools>SQL:

alter table "tblMembers" add constraint "DOB is greater than DOD" CHECK ("DOB"<="DOD");

now using the table “tblMembers” try to input a new or update an existing record setting the value of DOB greater than the value of DOD.
.
if ever you wish to drop the constraint then execute this via menu:>Tools>SQL:

alter table "tblMembers" drop constraint "DOB is greater than DOD";