I have a table with a date of birth column, a date of death column and the date the dataset was extracted.
I want to calculate each person's age as either the date of their death or their age at the date of extract - not by the current date - with a new 'Age' column. I can't quite work it out at the moment.
Example data:
Current table
| person_id | birth_datetimetime | death_datetime | extract_date |
|---|---|---|---|
| 1234 | 1980-04-01T00:00:00 | null | 2021-12-31 |
| 8765 | 1925-05-04T00:00:00 | 2018-05-T00:00:00 | 2021-12-31 |
| 9102 | 1974-05-17T00:00:00 | 2021-01-31T00:00:00 | 2021-12-31 |
| 5678 | 2019-09-01T00:00:00 | null | 2021-12-31 |
| 3456 | 1947-04-01T00:00:00 | 2016-06-14T00:00:00 | 2021-12-31 |
Desired output
| person_id | birth_datetimetime | death_datetime | extract_date | Age |
|---|---|---|---|---|
| 1234 | 1980-04-01T00:00:00 | null | 2021-12-31 | 41 |
| 8765 | 1925-05-04T00:00:00 | 2018-05-T00:00:00 | 2021-12-31 | 93 |
| 9102 | 1974-05-17T00:00:00 | 2021-01-31T00:00:00 | 2021-12-31 | 47 |
| 5678 | 2019-09-01T00:00:00 | null | 2021-12-31 | 2 |
| 3456 | 1947-04-01T00:00:00 | 2016-06-14T00:00:00 | 2021-12-31 | 69 |
CodePudding user response：
Consider below approach
select *,
date_diff(ifnull(death_datetime, extract_date), date(birth_datetimetime), year) as age
from your_table
if applied to sample data in your question - output is
