CODEWITHSUNDEEP
oracle-database
αƞjiβ
αƞjiβ

Reputation: 3246

Getting record based on date range

I have table with member enrollment info on different dates. I need a query to find the member info.

Given a date it will check if user enroll in that date or not

There is an additional status column in result which show if member is enrolled or not as of today. I am computing it using CASE statement.

Here a query I have so far to display latest for each member. I need help to modify/in cooperate so that it return me true condition also.

select *
from (
  select id, member_id, enroll_date, end_date,
    CASE 
      WHEN sysdate between enroll_date and end_date THEN 'Active'
      ELSE 'Inactive'
    END
    Status,
    row_number() over (partition by member_id order by enroll_date desc) rn
  from myView
)where rn = 1

Table structure

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

I want to get info and status of the member based on input date.

For example

a) If input date is 2005/1/1 then it should give me 01 and 05

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

b) If input is 2006/1/1 then it should give me 02 and 05

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

c) If input is 2013/1/1 then it should give me 01 and 06

01   123456789   01-JAN-13     31-DEC-13     Inactive
06   987654321   01-JAN-13     31-JAN-13     Inactive

d) If input is 2014/1/1 then it should give me 01 and 05

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

Upvotes: 0

Views: 52

Answers (1)

Multisync
Multisync

Reputation: 8797

select *
from (
  select id, member_id, enroll_date, end_date,
    CASE 
      WHEN sysdate between enroll_date and end_date THEN 'Active'
      ELSE 'Inactive'
    END
    Status,
    row_number() over (partition by member_id 
                       order by case when :input_date between enroll_date and end_date 
                                then 1 else 2 end, enroll_date desc) rn
  from myView
)where rn = 1

This is a tricky order which lets you assign rn=1 for a row which fits the given date. Otherwise the row with max enrol date is assigned rn=1

order by case when :input_date between enroll_date and end_date 
         then 1 else 2 end, enroll_date desc

Upvotes: 1

Related Questions