Script categories

Thursday, 22 September 2011

Calculating Age and grouping into Age groups

-- Grouping people in a specific age group
with t as
(
select 'john' name,to_date('01.01.1990','dd.mm.yyyy') birth_date from dual union
select 'sue',      to_date('02.05.2000','dd.mm.yyyy')  from dual union
select 'abraham',  to_date('04.07.1998','dd.mm.yyyy')  from dual union
select 'Tim',      to_date('15.09.1998','dd.mm.yyyy')  from dual union
select 'Rob',      to_date('16.09.1998','dd.mm.yyyy')  from dual union
select 'Mary',     to_date('14.09.1990','dd.mm.yyyy')  from dual union
select 'Larry',    to_date('16.09.1995','dd.mm.yyyy')  from dual union
select 'Sally',    to_date('16.09.1965','dd.mm.yyyy')  from dual
)
--
select age_group , count(*)
from
(
  select
    case
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 17 then
        '0-17'
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 24 then
        '18-24'
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 34 then
        '25-34'
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 44 then
        '35-44'
      when trunc( months_between(sysdate, birth_date) / 12 ) <= 54 then
        '45-54'
      else 'n/a'
    end age_group
  from t
)
group by age_group
order by age_group;

1 comment:

  1. I was searching to group financial spending on patients by age groups and came across this post.
    When you write the CASE statement this way the >=24 group will include the <=17 group too.
    So I changed the CASE statement to be like:

    select (
    select sum(invoice_amount)
    from table_name tn
    where tn.mrn in(select st.mrn from second_table st where trunc(months_between(sysdate, dateofbirth)/12) <= 18)
    and to_date(tn.doservice) >= 'start_date' and to_date(tn.doservice) <= 'end_date') as "0 --18",
    (
    select sum(invoice_amount)
    from table_name tn
    where tn.mrn in (select st.mrn from second_table st where trunc(months_between(sysdate, dateofbirth)/12) >=19 and trunc(months_between(sysdate, dateofbirth)/12) <= 24)
    and to_date(tn.doservice) >= 'start_date' and to_date(tn.doservice) <= 'end_date') as "19--24",
    (
    select sum(invoice_amount)
    from table_name tn
    where tn.mrn in (select st.mrn from second_table st where trunc(months_between(sysdate, dateofbirth)/12) >=25 and trunc(months_between(sysdate, dateofbirth)/12) <= 39)
    and to_date(tn.doservice) >= 'start_date' and to_date(tn.doservice) <= 'end_date') as "25--39",
    (
    select sum(invoice_amount)
    from table_name tn
    where tn.mrn in (select st.mrn from second_table st where trunc(months_between(sysdate, dateofbirth)/12) >=40 and trunc(months_between(sysdate, dateofbirth)/12) <= 54)
    and to_date(tn.doservice) >= 'start_date' and to_date(tn.doservice) <= 'end_date') as "40--54",
    (
    select sum(invoice_amount)
    from table_name tn
    where tn.mrn in (select st.mrn from second_table st where trunc(months_between(sysdate, dateofbirth)/12) >=55)
    and to_date(tn.doservice) >= 'start_date' and to_date(tn.doservice) <= 'end_date') as "above--55"
    from dual;
    This one works perfect for me.

    Thank you

    ReplyDelete