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

# Calculating min Age

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

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
>
, ROUND( (SYSDATE - S.START_DTE)/365.24,0) AS AGE_ST_DATE
>

Incorrect
• ###### 3. Re: Calculating min Age
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
``````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
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
Thanks Frank Kulash
Once agin you are the only one that makes sense.
• ###### 7. Re: Calculating min Age
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
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