8 Replies Latest reply: May 18, 2012 12:07 AM by Chloe_19 RSS

    Calculating min Age

    Chloe_19
      I want to get the age of the ID from their minimum U as well as their current age.
      So ID 1 lowest U is 5 which points to the date in TABLE 2 5-Apr-05.
      The birthdate of the ID is in TABLE 3.

      So i want the age of the ID when their U was at the lowest.
      Current age i have using this code:
      SELECT      DISTINCT ID
      , ROUND( (SYSDATE - S.BIRTHDATE)/365.24,0) AS AGE_NOW 
      FROM TABLE_ONE E, TABLE_THREE S        
      WHERE E.ID = S.ID 
      TABLE_ONE
      CREATE TABLE TABLE_ONE
      (ID     VARCHAR2(8),
      UNIT    VARCHAR2(8)) 
      
      INSERT INTO TABLE_ONE (ID, UNIT) VALUES (1,'9');
      INSERT INTO TABLE_ONE (ID, UNIT) VALUES (1,'8');
      INSERT INTO TABLE_ONE (ID, UNIT) VALUES (1,'5');
      INSERT INTO TABLE_ONE (ID, UNIT) VALUES (1,'10');
      INSERT INTO TABLE_ONE (ID, UNIT) VALUES (2,'7');
      INSERT INTO TABLE_ONE (ID, UNIT) VALUES (2,'11');
      INSERT INTO TABLE_ONE (ID, UNIT) VALUES (2,'23');
      
      ID     U
      1     9
      1     8
      1     5
      1     10
      2     7
      2     11
      2     23
      TABLE_TWO
      CREATE TABLE TABLE_TWO
      (UNIT     VARCHAR2(8),
      START_DTE    VARCHAR2(16)) 
      
      INSERT INTO TABLE_TWO (UNIT,START_DTE) VALUES ('9', TO_DATE( '13-MAR-2009', 'DD-MON-YYYY' ) );
      INSERT INTO TABLE_TWO (UNIT,START_DTE) VALUES ('8', TO_DATE( '13-JAN-2008', 'DD-MON-YYYY' ) );
      INSERT INTO TABLE_TWO (UNIT,START_DTE) VALUES ('5', TO_DATE( '13-APR-2005', 'DD-MON-YYYY' ) );
      INSERT INTO TABLE_TWO (UNIT,START_DTE) VALUES ('10', TO_DATE( '13-JUN-2010', 'DD-MON-YYYY' ) );
      INSERT INTO TABLE_TWO (UNIT,START_DTE) VALUES ('7', TO_DATE( '13-MAY-2009', 'DD-MON-YYYY' ) );
      INSERT INTO TABLE_TWO (UNIT,START_DTE) VALUES ('11', TO_DATE( '13-JAN-2008', 'DD-MON-YYYY' ) );
      INSERT INTO TABLE_TWO (UNIT,START_DTE) VALUES ('23', TO_DATE( '13-MAR-2009', 'DD-MON-YYYY' ) );
      
      U     START_DTE
      9     13-Mar-09
      8     12-Jan-08
      5     5-Apr-05
      10     20-Jun-10
      7     3-May-09
      11     12-Jan-08
      23     13-Mar-09
      TABLE_THREE
      CREATE TABLE TABLE_THREE
      (ID     VARCHAR2(8),
      B_DTE    VARCHAR2(16)) 
      
      INSERT INTO TABLE_THREE (ID,B_DTE) VALUES ('1', TO_DATE( '19-JAN-1985', 'DD-MON-YYYY' ) );
      INSERT INTO TABLE_THREE (ID,B_DTE) VALUES ('2', TO_DATE( '12-JUL-1966', 'DD-MON-YYYY' ) );
      
      ID     B_DTE
      1     19-Jan-85
      2     12-Jul-66
      WANT:
      ID     AGE_NOW     AGE_ST_DATE
      1     27     20
      2     46     43
        • 1. Re: Calculating min Age
          HuaMin Chen
          Answer is:

          SELECT     DISTINCT ID
          , ROUND( (SYSDATE - S.BIRTHDATE)/365.24,0) AS AGE_NOW
          , ROUND( (SYSDATE - S.START_DTE)/365.24,0) AS AGE_ST_DATE
          FROM TABLE_ONE E, TABLE_THREE S, TABLE_TWO B
          WHERE E.ID = S.ID
          and e.unit=b.unit;
          • 2. Re: Calculating min Age
            Chloe_19
            >
            , ROUND( (SYSDATE - S.START_DTE)/365.24,0) AS AGE_ST_DATE
            >

            Incorrect
            • 3. Re: Calculating min Age
              HuaMin Chen
              Sorry

              SELECT DISTINCT ID
              , ROUND( (SYSDATE - S.BIRTHDATE)/365.24,0) AS AGE_NOW
              , ROUND( (SYSDATE - B.START_DTE)/365.24,0) AS AGE_ST_DATE
              FROM TABLE_ONE E, TABLE_THREE S, TABLE_TWO B
              WHERE E.ID = S.ID
              and e.unit=b.unit;
              • 4. Re: Calculating min Age
                696547
                WITH t1 AS
                  (SELECT 1 ID , 9 unit FROM dual
                  UNION ALL
                  SELECT 1 , 8 FROM dual
                  UNION ALL
                  SELECT 1 , 8 FROM dual
                  UNION ALL
                  SELECT 1 , 5 FROM dual
                  UNION ALL
                  SELECT 1 , 10 FROM dual
                  UNION ALL
                  SELECT 2 , 7 FROM dual
                  UNION ALL
                  SELECT 2 , 11 FROM dual
                  UNION ALL
                  SELECT 2 , 23 FROM dual
                  ),
                  t2 AS
                  (SELECT 9 unit , to_date( '13-MAR-2009', 'DD-MON-YYYY' ) start_date FROM dual
                  UNION ALL
                  SELECT 8 , to_date( '13-JAN-2008', 'DD-MON-YYYY' ) FROM dual
                  UNION ALL
                  SELECT 5 , to_date( '13-APR-2005', 'DD-MON-YYYY' ) FROM dual
                  UNION ALL
                  SELECT 10 , to_date( '13-JUN-2010', 'DD-MON-YYYY' ) FROM dual
                  UNION ALL
                  SELECT 7 , to_date( '13-MAY-2009', 'DD-MON-YYYY' ) FROM dual
                  UNION ALL
                  SELECT 11 , to_date( '13-JAN-2008', 'DD-MON-YYYY' ) FROM dual
                  UNION ALL
                  SELECT 23 , to_date( '13-MAR-2009', 'DD-MON-YYYY' ) FROM dual
                  ),
                  t3 AS
                  (SELECT 1 ID, to_date( '19-JAN-1985', 'DD-MON-YYYY' ) b_date FROM dual
                  UNION ALL
                  SELECT 2 , to_date( '12-JUL-1966', 'DD-MON-YYYY' ) FROM dual
                  )
                SELECT ID ,
                  round( (SYSDATE    - b_date)/365,0) AS age_now ,
                  round( (start_date - b_date)/365,0) AS age_st_date
                FROM
                  (SELECT t1.ID ,
                    t3.b_date ,
                    MIN(t2.start_date) start_date
                  FROM t1 ,
                    t2 ,
                    t3
                  WHERE t1.ID = t3.ID
                  AND t1.unit =t2.unit
                  GROUP BY t1.ID,
                    t3.b_date
                  );
                
                ID AGE_NOW AGE_ST_DATE
                1     27     20
                2     46     42
                • 5. Re: Calculating min Age
                  Frank Kulash
                  Hi,

                  Thanks for posting the CREATE TABLE and INSERT statements; that helps.
                  Always say which version of Oracle you're using. The query below works in Oracle 9.1 and higher.

                  The normal sort order for VARCHAR2s puts the following strings in the following order:
                  '10'
                  '5'
                  '8'
                  '9'
                  with '10' first. Why do you want '5' first? If it has to do with the number 5 being smaller than the numbers 8, 9 and 10, why are they stored in a VARCHAR2 column? Would '10' come before or after 'A'? Would 'A5' come before 'A10'? Would 'A5' come before 'B'? Depending on your answers, you may need to change the sub-query. If my guesses about these questions were right, then you can do this:
                  WITH     table_one_summary     AS
                  (
                       SELECT       id
                       ,       MIN (unit) KEEP (DENSE_RANK FIRST ORDER BY LPAD (unit, 8))     AS first_unit
                       FROM       table_one
                       GROUP BY  id
                  )
                  SELECT       t1.id
                  ,       ROUND ( MONTHS_BETWEEN (SYSDATE, t3.b_dte)
                              / 12
                            )     AS age_now
                  ,       ROUND ( MONTHS_BETWEEN (t2.start_dte, t3.b_dte)
                              / 12
                            )     AS age_st_date
                  FROM       table_one_summary  t1
                  JOIN       table_two          t2  ON   t1.first_unit     = t2.unit
                  JOIN       table_three          t3  ON   t1.id          = t3.id
                  ORDER BY  t1.id
                  ;
                  I used MONTHS_BETWEEN to get the ages, because there are exactly 12 months in a year. There are approximately 365.24 days in a year, so the way you calculated ages will almost always be accurate when dealing with spans no longer than a human lifetime, and, when it is off, it will only be off by 1 year, but why use a formula that's almost always right when you can just as easily use one that's always right?
                  • 6. Re: Calculating min Age
                    Chloe_19
                    Thanks Frank Kulash
                    Once agin you are the only one that makes sense.
                    • 7. Re: Calculating min Age
                      sb92075
                      Chloe_19 wrote:
                      Thanks Frank Kulash
                      Once agin you are the only one that makes sense.
                      If so, then why do you never actually learn & repeatedly required repeated spoonfeeding?
                      • 8. Re: Calculating min Age
                        Chloe_19
                        For your sake i really hope you are a woman sb92075...and its that time of the month.

                        Edited by: Chloe_19 on 17/05/2012 22:06