[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

get count based on age in oracle

I want to get count of collections in the year 2013 for age group 16.

But in the where clause I cannot use max(coll_date)
If the donor has donated multiple times  

   select coll_date
    from donations_don d,donors_don dn
   where d.donor_id = dn.donor_id
     and coll_date between '01-jan-2013' and '31-dec-2013'
     and unit_id is not null  
    and dn.donor_id = 'DN20093654'
      order by coll_date

COLL_DATE
3/1/2013
5/2/2013
7/5/2013
9/20/2013
11/22/2013

Now how do I calculate count for age group 16.

 select count(*)
    from donations_don d,donors_don dn
   where d.donor_id = dn.donor_id
     and coll_date between '01-jan-2013' and '31-dec-2013'
     and unit_id is not null  
       and TRUNC(MONTHS_BETWEEN(max(coll_date), date_of_birth)/12)  = 16
0
anumoses
Asked:
anumoses
1 Solution
 
Geert GruwezOracle dbaCommented:
why not use today to calculate the age ?

and TRUNC(MONTHS_BETWEEN(sysdate, date_of_birth)/12)  = 16

or the last date of the year at that time
or the date the donation was done  > colldate

and TRUNC(MONTHS_BETWEEN(coll_date, date_of_birth)/12)  = 16
0
 
anumosesAuthor Commented:
yes I did the same and before I could realize you had answered. Thanks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now