Home » SQL & PL/SQL » SQL & PL/SQL » Converting Date of Birth to Age
Converting Date of Birth to Age [message #202405] Thu, 09 November 2006 10:34 Go to next message
lisafitz
Messages: 10
Registered: November 2006
Junior Member
I am currently writing a report in which i have to firstly get the age of the person from their date of birth . Then i have to group the ages into ranges ie 20-29, 30-39 and also sort by gender.

Does anyone have any ideas

Thanks
Re: Converting Date of Birth to Age [message #202409 is a reply to message #202405] Thu, 09 November 2006 11:14 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
select sum(case when age_in_months between 240 and 348 then 1 else 0 end) as count20_29,
...
from
(select months_between(sysdate,dob) as age_in_months
from table)
Re: Converting Date of Birth to Age [message #202478 is a reply to message #202409] Fri, 10 November 2006 00:23 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
select sum(case when age between 20 and 29 then 1 else 0 end) as AGE_CAT20-29....
from
(select round(months_between(sysdate,dob)/12,0) as age
from table)

Re: Converting Date of Birth to Age [message #202976 is a reply to message #202478] Mon, 13 November 2006 08:08 Go to previous messageGo to next message
lisafitz
Messages: 10
Registered: November 2006
Junior Member
Thank you
Re: Converting Date of Birth to Age [message #203213 is a reply to message #202405] Tue, 14 November 2006 06:11 Go to previous messageGo to next message
lisafitz
Messages: 10
Registered: November 2006
Junior Member
this is the code i have written already but finding it difficult to group them together with age range and gender


select date_of_birth, gender,
months_between (sysdate, date_of_birth) / 12 as age_in_years,
round (months_between (sysdate, date_of_birth) / 12) as age,
case
when (months_between (sysdate, date_of_birth) / 12) BETWEEN 20 AND 29.5 THEN '20-29'
when (months_between (sysdate, date_of_birth) / 12) BETWEEN 30 AND 39.5 THEN '30-39'
when (months_between (sysdate, date_of_birth) / 12) BETWEEN 40 AND 49.5 THEN '40-49'
when (months_between (sysdate, date_of_birth) / 12) BETWEEN 50 AND 59.5 THEN '50-59'
when (months_between (sysdate, date_of_birth) / 12) BETWEEN 60 AND 100 THEN '60 and More'
ELSE 'No Birth Date Entered’
END as AgeRange
from hr_person


My report should look something like this :


Age Male Female
20-29 5 3
30-39 10 2
so on ......
Re: Converting Date of Birth to Age [message #203230 is a reply to message #203213] Tue, 14 November 2006 06:59 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
The statement for your report could look something like
(please note that if you are evaluating the same expression several times in a case when statement, you can put it once between the case and the when, instead of repeating it after each when):

SELECT sub.agerange
      ,SUM(decode(sub.gender
                 ,'M'
                 ,1
                 ,0)) male
      ,SUM(decode(sub.gender
                 ,'F'
                 ,1
                 ,0)) female
FROM   (SELECT date_of_birth
              ,gender
              ,months_between(SYSDATE
                             ,date_of_birth) / 12 AS age_in_years
              ,round(months_between(SYSDATE
                                   ,date_of_birth) / 12) AS age
              ,CASE (months_between(SYSDATE
                               ,date_of_birth) / 12)
                 WHEN BETWEEN 20 AND 29.99 THEN
                  '20-29'
                 WHEN BETWEEN 30 AND 39.99 THEN
                  '30-39'
                 WHEN BETWEEN 40 AND 49.99 THEN
                  '40-49'
                 WHEN BETWEEN 50 AND 59.99 THEN
                  '50-59'
                 WHEN BETWEEN 60 AND 100 THEN
                  '60 and More'
                 ELSE
                  'No Birth Date Entered'
               END agerange
        FROM   hr_person) sub
Re: Converting Date of Birth to Age [message #203264 is a reply to message #202405] Tue, 14 November 2006 09:26 Go to previous messageGo to next message
lisafitz
Messages: 10
Registered: November 2006
Junior Member
Thank you

But it gives me an error message ERROR at line 18
Ora-00936: missing expression
When * between
Re: Converting Date of Birth to Age [message #203268 is a reply to message #203264] Tue, 14 November 2006 09:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Sadly, you can't use BETWEEN with a simple CASE statement. In fact, you can't use anything except a straight equals.
You have to use a Searched CASE statement, like this:
              ,CASE 
                 WHEN TRUNC(MONTHS_BETWEEN(SYSDATE,date_of_birth)/120) BETWEEN 20 AND 29.99 THEN '20-29'
                 WHEN MONTHS_BETWEEN(SYSDATE,date_of_birth)/12 BETWEEN 30 AND 39.99 THEN '30-39'
                 WHEN MONTHS_BETWEEN(SYSDATE,date_of_birth)/12 BETWEEN 40 AND 49.99 THEN '40-49'
                 WHEN MONTHS_BETWEEN(SYSDATE,date_of_birth)/12 BETWEEN 50 AND 59.99 THEN '50-59'
                 WHEN MONTHS_BETWEEN(SYSDATE,date_of_birth)/12 BETWEEN 60 AND 100 THEN '60 and More'
                 ELSE'No Birth Date Entered'
               END agerange

You also need a
GROUP BY sub.agerange
at the end of the query.
Re: Converting Date of Birth to Age [message #203278 is a reply to message #203268] Tue, 14 November 2006 10:09 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Hm, it was kind of close... Embarassed
Re: Converting Date of Birth to Age [message #203420 is a reply to message #203278] Wed, 15 November 2006 02:01 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Close enough for government work, as they say Cool
Previous Topic: Basic Questions
Next Topic: DO We Require Index on Foreign Keys
Goto Forum:
  


Current Time: Fri Dec 09 14:01:03 CST 2016

Total time taken to generate the page: 0.63511 seconds