How to Query for People Age 70 or Older?

rockdave35 · #1 (**permalink**) 08-31-09, 13:26

Hey guys,

I am trying to write a query that will return all people who are age 70 or older. The confusing part is I need to see people who have are 70 or older as of 07-01-2009.

My query so far only looks for people who turned 70 within the entire year of 2009. I know I'm close, but just confused on how to query to look at a specific date.

Code:

SELECT A.MBR_SSN_NBR AS SSN,                                                   
          A.MBR_F_NM AS FIRST_NAME,                                               
          A.MBR_L_NM AS LAST_NAME,                                                
          CHAR(A.MBR_BIRTH_DT, USA) AS BIRTH_DATE,                                
          YEAR(A.MBR_BIRTH_DT) AS BIRTH_YEAR,
          YEAR(CURRENT DATE) - YEAR(A.MBR_BIRTH_DT) as AGE
          
 FROM DSNP.PR01_T_MBR A,
      DSNP.PR01_T_MBR_SYS B
 where A.MBR_SSN_NBR = B.MBR_SSN_NBR
 and YEAR(A.MBR_BIRTH_DT) <= 1939  
 and A.MBR_BIRTH_DT <> '01/01/0001'
 and B.mbr_stat_cd = '1'

Can anyone help?

spartiatis · #2 (**permalink**) 08-31-09, 13:38

Try this to see if it works:
SELECT A.MBR_SSN_NBR AS SSN,
A.MBR_F_NM AS FIRST_NAME,
A.MBR_L_NM AS LAST_NAME,
CHAR(A.MBR_BIRTH_DT, USA) AS BIRTH_DATE,
YEAR(A.MBR_BIRTH_DT) AS BIRTH_YEAR,
YEAR(CURRENT DATE) - YEAR(A.MBR_BIRTH_DT) as AGE

FROM DSNP.PR01_T_MBR A,
DSNP.PR01_T_MBR_SYS B
where A.MBR_SSN_NBR = B.MBR_SSN_NBR
and A.MBR_BIRTH_DT < '07/01/1939'
and A.MBR_BIRTH_DT <> '01/01/0001'
and B.mbr_stat_cd = '1'

Lenny77 · #3 (**permalink**) 08-31-09, 14:23

Why do not use AGE in predicate ?

Quote:

SELECT A.MBR_SSN_NBR AS SSN,
A.MBR_F_NM AS FIRST_NAME,
A.MBR_L_NM AS LAST_NAME,
CHAR(A.MBR_BIRTH_DT, USA) AS BIRTH_DATE,
YEAR(A.MBR_BIRTH_DT) AS BIRTH_YEAR,
YEAR(CURRENT DATE) - YEAR(A.MBR_BIRTH_DT) as AGE

FROM DSNP.PR01_T_MBR A,
DSNP.PR01_T_MBR_SYS B
where A.MBR_SSN_NBR = B.MBR_SSN_NBR
YEAR(CURRENT DATE) - YEAR(A.MBR_BIRTH_DT) >= 70
and A.MBR_BIRTH_DT > date('0001-01-01')
and B.mbr_stat_cd = '1'

Lenny

ARWinner · #4 (**permalink**) 08-31-09, 15:08

Using YEAR(CURRENT DATE) - YEAR(DATE_OF_BIRTH) is not accurate. If a person is born on 12/31/1939 and the current date is 08/31/2009, then your expression will say that that person is 70 when in fact they are only 69. A better way is to do this: current date - 70 years >= date_of_birth.

Andy

rockdave35 · #5 (**permalink**) 08-31-09, 15:39

Quote:

Originally Posted by ARWinner

Using YEAR(CURRENT DATE) - YEAR(DATE_OF_BIRTH) is not accurate. If a person is born on 12/31/1939 and the current date is 08/31/2009, then your expression will say that that person is 70 when in fact they are only 69. A better way is to do this: current date - 70 years >= date_of_birth.

Andy

Hi Andy,

You are correct which is why I want my query to only include people who are 70 or older as of 7/01/2009. It looks like your query works. The last birthdate in my return results is 08/27/1939.

I also tried:

and CHAR(A.MBR_BIRTH_DT, USA) <= '07-01-1939'

This did not work. It returned people who had birthdates after 1939. I am puzzled why it failed. Any idea why? Thanks for all the help everyone!

Cougar8000 · #6 (**permalink**) 08-31-09, 15:42

Quote:

Originally Posted by ARWinner

Using YEAR(CURRENT DATE) - YEAR(DATE_OF_BIRTH) is not accurate. If a person is born on 12/31/1939 and the current date is 08/31/2009, then your expression will say that that person is 70 when in fact they are only 69. A better way is to do this: current date - 70 years >= date_of_birth.

Andy

That is true Andy, but can you solve a sudoki puzzle using SQL

BTW. Remove and A.MBR_BIRTH_DT <> '01/01/0001' from the SQL. It is totally redundant check.

Cougar8000 · #7 (**permalink**) 08-31-09, 15:46

Quote:

Originally Posted by rockdave35

Hi Andy,

You are correct which is why I want my query to only include people who are 70 or older as of 7/01/2009. It looks like your query works. The last birthdate in my return results is 08/27/1939.

I also tried:

and CHAR(A.MBR_BIRTH_DT, USA) <= '07-01-1939'

This did not work. It returned people who had birthdates after 1939. I am puzzled why it failed. Any idea why? Thanks for all the help everyone!

Maybe because your format is different

db2 "select CHAR(current date, USA) from sysibm.SYSDUMMY1"

1
----------
08/31/2009

1 record(s) selected.

ARWinner · #8 (**permalink**) 08-31-09, 16:04

Quote:

Originally Posted by rockdave35

I also tried:

and CHAR(A.MBR_BIRTH_DT, USA) <= '07-01-1939'

This did not work. It returned people who had birthdates after 1939. I am puzzled why it failed. Any idea why? Thanks for all the help everyone!

This is because you are now comparing strings and not dates. '06-30-2003' is less than '07-01-1939'.

Andy

Stealth_DBA · #9 (**permalink**) 08-31-09, 16:05

Try this:

Code:

SELECT A.MBR_SSN_NBR AS SSN,                                                   
          A.MBR_F_NM AS FIRST_NAME,                                               
          A.MBR_L_NM AS LAST_NAME,                                                
          CHAR(A.MBR_BIRTH_DT, USA) AS BIRTH_DATE,                                
          YEAR(A.MBR_BIRTH_DT) AS BIRTH_YEAR,
          YEAR(CURRENT DATE - A.MBR_BIRTH_DT) as AGE
          
 FROM DSNP.PR01_T_MBR A,
      DSNP.PR01_T_MBR_SYS B
 where A.MBR_SSN_NBR = B.MBR_SSN_NBR
 and YEAR(CURRENT DATE - A.MBR_BIRTH_DT) >= 70  
 and B.mbr_stat_cd = '1'

When you subtract a date from a date you get a date duration. this is an interger that contains the number of year, months and days the dates are different. For example:
CURRENT DATE - DATE('1920-10-11') returns 881020 which is 88 years 10 months and 20 days. You can use the YEAR function to extract just the year since that is all you need.

PS You can, of course, use any starting date instead of CURRENT DATE that you like or need.

tonkuma · #10 (**permalink**) 08-31-09, 16:14

Why not use "and A.MBR_BIRTH_DT < DATE('07/01/1939') - 70 YEARS"?

Stealth_DBA · #11 (**permalink**) 08-31-09, 16:31

tonkuma, there is nothing wrong with your example (just as there is probably nothing wrong with most of the other suggestions) in the WHERE CLAUSE. But the request also returns the Age in the Select and you can't use that (or most of the others) in the Select and ensure a correct answer. This may not be true when the Where clause if factored in but subtracting the Year of one date from the year of another date is not an accurate way to get someone's age. If you birth date was Jun 30, that would indicate you are the same age on Jan 1 as you would be on Dec 31.

By using the Date - Date, the date duration handles all of that for you. Since I use it in the Select, I didn't see any reason to use different logic in the Where clause.

But as with most SQL, there are many different ways to get a result.

Lenny77 · #12 (**permalink**) 08-31-09, 16:38

Quote:

Originally Posted by ARWinner

Using YEAR(CURRENT DATE) - YEAR(DATE_OF_BIRTH) is not accurate. If a person is born on 12/31/1939 and the current date is 08/31/2009, then your expression will say that that person is 70 when in fact they are only 69. A better way is to do this: current date - 70 years >= date_of_birth.

Andy

In this case YEAR(CURRENT DATE) - YEAR(DATE_OF_BIRTH) AS AGE on SELECT clause is not good, too.

But anyway, thanks

Lenny77 · #13 (**permalink**) 08-31-09, 17:06

You can see a difference between:

Quote:

year(current date) - year(date('1962-10-12'))

and

Quote:

year(current date - date('1962-10-12'))

....

Just run the query:

Quote:

select
year(current date) - year(date('1962-10-12')) wrong,
year(current date - date('1962-10-12')) righ
from sysibm.sysdummy1

So, just make the right change and you'll the right query:

Quote:

SELECT A.MBR_SSN_NBR AS SSN,
A.MBR_F_NM AS FIRST_NAME,
A.MBR_L_NM AS LAST_NAME,
CHAR(A.MBR_BIRTH_DT, USA) AS BIRTH_DATE,
YEAR(A.MBR_BIRTH_DT) AS BIRTH_YEAR,
YEAR(CURRENT DATE - date(A.MBR_BIRTH_DT)) as AGE

FROM DSNP.PR01_T_MBR A,
DSNP.PR01_T_MBR_SYS B
where A.MBR_SSN_NBR = B.MBR_SSN_NBR
YEAR(CURRENT DATE - date(A.MBR_BIRTH_DT)) >= 70
and A.MBR_BIRTH_DT > date('0001-01-01')
and B.mbr_stat_cd = '1'

Lenny

rockdave35 · #14 (**permalink**) 08-31-09, 17:09

Quote:

Originally Posted by Lenny77

In this case YEAR(CURRENT DATE) - YEAR(DATE_OF_BIRTH) AS AGE on SELECT clause is not good, too.

But anyway, thanks

Ok, I'm confused again. How can I get their actual age in the Select statement?

rockdave35 · #15 (**permalink**) 08-31-09, 17:11

Quote:

Originally Posted by Stealth_DBA

Try this:

Code:

SELECT A.MBR_SSN_NBR AS SSN,                                                   
          A.MBR_F_NM AS FIRST_NAME,                                               
          A.MBR_L_NM AS LAST_NAME,                                                
          CHAR(A.MBR_BIRTH_DT, USA) AS BIRTH_DATE,                                
          YEAR(A.MBR_BIRTH_DT) AS BIRTH_YEAR,
          YEAR(CURRENT DATE - A.MBR_BIRTH_DT) as AGE
          
 FROM DSNP.PR01_T_MBR A,
      DSNP.PR01_T_MBR_SYS B
 where A.MBR_SSN_NBR = B.MBR_SSN_NBR
 and YEAR(CURRENT DATE - A.MBR_BIRTH_DT) >= 70  
 and B.mbr_stat_cd = '1'

When you subtract a date from a date you get a date duration. this is an interger that contains the number of year, months and days the dates are different. For example:
CURRENT DATE - DATE('1920-10-11') returns 881020 which is 88 years 10 months and 20 days. You can use the YEAR function to extract just the year since that is all you need.

PS You can, of course, use any starting date instead of CURRENT DATE that you like or need.

Thank you Stealth for your explanation. Just to clarify, in DB2, I can not subtract two string values. However, the date functions converts to integer values which can then be subtracted. Is this correct?

Internet Services

Web Development

Digital Marketing

Consumer Tech