Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: How to Query for People Age 70 or Older?

    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?

  2. #2
    Join Date
    Aug 2009
    Posts
    16
    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'

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Why do not use AGE in predicate ?

    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Jan 2009
    Posts
    46
    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!

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why not use "and A.MBR_BIRTH_DT < DATE('07/01/1939') - 70 YEARS"?

  11. #11
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  12. #12
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    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

  13. #13
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    You can see a difference between:

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

    Just run the query:

    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:

    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

  14. #14
    Join Date
    Jan 2009
    Posts
    46
    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?

  15. #15
    Join Date
    Jan 2009
    Posts
    46
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •