CODEWITHSUNDEEP
sqloracle-database
Skn
Skn

Reputation: 101

Oracle SQL: issue with age calculation

I need to segregate my data into the following categories per age: 1-6,6-12,12-18,>18.

The issue I have is when the age calculates as 6, the record is not showing up in any of the category. I expect it should be in 6-12 per my logic below.

To avoid duplicates, I have written my query as: 1-6: age<=5 6-12: age >=6 <=11 12-18:age >=12 <=17 >18:age >=18

why am I missing the records that have age 6?

EDIT: here is my age calculation: (date1-DOB)/365.25 >= 6 (date1-DOB)/ 365.25 <= 11

I also tried 1-6 as <6, 6-12 as >6 <12 and 12-18 as >12 <18 but in some cases, age 6 shows up in both 1-6 and 6-12. And 12 in 6-12 and 12-18. Is there a way to avoid this?

Upvotes: 0

Views: 91

Answers (2)

Aleksej
Aleksej

Reputation: 22949

You can use a CASE where you can expicitly write the conditions to match each range; for example:

select x, 
       case
        when x >=  1 and x <  6 then '1-6'
        when x >=  6 and x < 12 then '6-12'
        when x >= 12 and x < 18 then '12-18'
        when x >= 18            then '>18'
        else 'not matched'
       end as range
from ( select level / 2 as x from dual connect by level < 20 )

You can do the same thing in different ways, but I believe this is one of the most readable.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You have ..not covered range in always range eg: 

1-6:  age <6          not <=5 and 
6-12: age >=6 <=12    not age >=6 <=11  
12-18:age >=12 <=18   not >=12 <=17 
>18:age >=18

Upvotes: 0

Related Questions