-- 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;
I was searching to group financial spending on patients by age groups and came across this post.
ReplyDeleteWhen 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