CODEWITHSUNDEEP
sqloracle-database
αƞjiβ
αƞjiβ

Reputation: 3256

Pick latest record from table using date in Oracle

I have a table with some records like this:

ID     MEMBER_ID     ENROLL_DATE     END_DATE
01     123456789     01-JAN-13       31-DEC-13
02     123456789     01-JAN-06       31-DEC-06
03     123456789     01-JAN-11       31-DEC-11
04     987654321     01-JAN-08       31-DEC-12
05     987654321     01-FEB-13       31-DEC-99
06     987654321     01-JAN-13       31-JAN-13

Now if I give some date (e.g. 12/01/2014) it should return me latest enroll date (i.e. 01 and 05) with the status. So output like:

01     123456789     01-JAN-13     31-DEC-13 Inactive
05     987654321     01-FEB-13     31-DEC-99 Active

I have SQL which calculate status but not able to get only one record. How can I get that?

select id, member_id, enroll_date, end_date,
  case 
    when TO_DATE(:dateOfCall, 'MM/DD/YYYY') between enroll_date and end_date then 'Active'
    else 'Inactive'
  end
  Status
from myView;

Upvotes: 0

Views: 109

Answers (1)

sgeddes
sgeddes

Reputation: 62861

You can use row_number -- something like this:

with cte as (
  select id, member_id, enroll_date, end_date, 
         row_number() over (partition by member_id order by enroll_date desc) rn
  from myView
  where enroll_date <= to_date('2014/12/01','yyyy/mm/dd')
  )
select *
from cte
where rn = 1

This presumes you want to group each result by member_id. If that's not needed, remove the partition by clause.

Upvotes: 1

Related Questions