Skip to Main Content
  • Questions

  • How do I qualify on age between 12 and 17 years?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 11, 2024 - 2:16 am UTC

Last updated: January 15, 2024 - 5:02 am UTC

Version: Oracle SQL Developer Version 22.2.1.234

Viewed 1000+ times

You Asked

Hello, I have the following simple query, yet, how do I factor in the age of between 12 and 17 years of age? Currently the code gives me all ages. I only need 12 to 17 years of age. 

select ea.alias 
    , p.name_full_formatted as "NAME"
    , to_char(p.birth_dt_tm, 'MM/DD/YYYY') "Birth_Date"
    , months_between(TRUNC(e.reg_dt_tm),to_date(p.birth_dt_tm))/12 as "Age_Admin" 
    , TO_CHAR(C.PERFORMED_DT_TM, 'MM/DD/YY HH:MI:SS') "Performed_Date"
    , cv.description
    , C.RESULT_VAL as "result"

from v500.clinical_event c, code_value cv,  ENCNTR_ALIAS   EA,encounter e, person p
where c.event_cd = cv.code_value
and c.event_cd in (12345)
and c.view_level = 1
and C.PERFORMED_DT_TM >= '01-MAY-23'
and C.PERFORMED_DT_TM <= '31-MAY-23'
and c.encntr_id = ea.encntr_id
and ea.encntr_alias_type_cd = 1077
and e.encntr_id = ea.encntr_id
and p.person_id = e.person_id

order by NAME desc;

and Connor said...

Take a look at this post

https://asktom.oracle.com/ords/asktom.search?tag=calculation-of-age

to get an accurate calculation of age.

Once you've done that, you could do

select ea.alias 
    , p.name_full_formatted as "NAME"
    , to_char(p.birth_dt_tm, 'MM/DD/YYYY') "Birth_Date"
    , months_between(TRUNC(e.reg_dt_tm),to_date(p.birth_dt_tm))/12 as "Age_Admin" 
    , TO_CHAR(C.PERFORMED_DT_TM, 'MM/DD/YY HH:MI:SS') "Performed_Date"
    , cv.description
    , C.RESULT_VAL as "result"
from v500.clinical_event c, code_value cv,  ENCNTR_ALIAS   EA,encounter e, person p
where c.event_cd = cv.code_value
and c.event_cd in (12345)
and c.view_level = 1
and C.PERFORMED_DT_TM >= '01-MAY-23'
and C.PERFORMED_DT_TM <= '31-MAY-23'
and c.encntr_id = ea.encntr_id
and ea.encntr_alias_type_cd = 1077
and e.encntr_id = ea.encntr_id
and p.person_id = e.person_id
and trunc((to_number(to_char(sysdate,'YYYYMMDD'))-      to_number(to_char(p.birth_dt_tm,'YYYYMMDD')))/10000) between 12 and 17
and p.birth_dt_tm between sysdate - 365*20   and sysdate - 365*10   -- just quickly eliminate a bunch of rows
order by NAME desc;

Rating

  (3 ratings)

Comments

N K Gupta, January 11, 2024 - 11:17 am UTC

age can be calculated simply by
trunc(months_between(sysdate, birth_dt_tm) /12) age
Chris Saxon
January 11, 2024 - 2:34 pm UTC

Did you read the post Connor linked to?

At least by UK law, if you're born on 29th Feb, you become one year older on 1st March in non-leap years.

Months_between thinks otherwise:

select months_between ( date'2023-02-28', date'2020-02-29' ) / 12 
from   dual;

MONTHS_BETWEEN(DATE'2023-02-28',DATE'2020-02-29')/12
----------------------------------------------------
                                                   3

Legal age for leaplings

mathguy, January 11, 2024 - 6:45 pm UTC

At least by UK law, if you're born on 29th Feb, you become one year older on 1st March in non-leap years.

Thankfully the UK seems to have this settled. In the US there are different rules for different states, and even within the same state, for different uses (even in cases where uses may come together to create contradictions).

True story told on Quora: leapling born on Feb. 29, 2000, turned 21 in 2021. 21 is the legal drinking age in most of the US, and age must be proven with a valid user id (generally driver's license). This guy got his license on his birthday in 2016; the dept. of motor vehicles had the license valid for 5 years, expiring on Feb. 28, 2021. (NOTE - not on March 1!). The guy went to the bar on Feb. 28, 2021, to celebrate his ability to drink alcohol. DENIED - for "legal drinking age" purposes, in the same state, the relevant day is March 1, not Feb. 28. The guy then said "OK, I'll hang around with my older friends till midnight; then you can serve me." Reply? "Sorry, that won't work either; your driver's license expires at midnight, and we aren't allowed to consider an expired id as proof of legal age." Kafka had nothing on us!

So - the first task for anyone who has to deal with "age" is to ask the business user for very clear instructions on how to handle. Not important if the specification is "age 17 AS OF JUNE 15 of the relevant year", but for things like driver's license expiration date and legal drinking age, Feb. 28 or March 1 should be made crystal clear. Thankfully not in the UK, where it's set by law!
Connor McDonald
January 15, 2024 - 5:02 am UTC

I remember flying on a plane that lost a day, and the pilot comes on the PA and says:

"I've been told that a passenger little Timmy turns 12 on the 15th... Too bad Timmy, no birthday presents for you this year".

Brutal :-)

Adriana, January 12, 2024 - 3:09 pm UTC

Connor - your recommendation worked like a charm! I slightly tweaked for my purposes. 
and trunc((to_number(to_char(e.reg_dt_tm,'YYYYMMDD'))- to_number(to_char(p.birth_dt_tm,'YYYYMMDD')))/10000) between 12 and 17

Connor McDonald
January 15, 2024 - 5:01 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.