CODEWITHSUNDEEP
sqlplsqloracle10g
user2490726
user2490726

Reputation: 21

How to do Oracle date ranking?

I have a table about members subscriptions which has four main columns :

1-member_id 
2-subscription_no 
3-subscription_start_date 
4-subscription_end_Date

And every member(has an unique member id) may have more than one subscription so I need to get the latest subscription date for every member .

Now my scenario is like this:

I need to get the members who did not subscribed again after 5 months of the his active subscription during 2012.

I.e I need to know the members who the company has lost them as subscribers as the criteria of being lost is not subscribe after 5 months of the end of his last active subscription during 2012.

Upvotes: 0

Views: 492

Answers (4)

myalc
myalc

Reputation: 133

I did some tests depending on your schenario as bellow

 create table table1 (id integer, nm integer, stdate date, endate date);

inserted some test data

Insert into TABLE1(ID, NM, STDATE, ENDATE)
 Values (1, 2, TO_DATE('04/09/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/17/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TABLE1(ID, NM, STDATE, ENDATE)
 Values(1, 1, TO_DATE('03/25/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/14/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TABLE1(ID, NM, STDATE, ENDATE)
 Values(2, 3, TO_DATE('03/05/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/15/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TABLE1(ID, NM, STDATE, ENDATE)
 Values(2, 2, TO_DATE('03/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/28/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

And the query is

with q1 as (
  select id, nm, stdate, endate,
    row_number() over (partition by id order by endate desc) as rn 
  from table1
) select * from q1 where rn=1
and <last5 months criteria using endate>;

result of the query

select id, nm, stdate, endate,
    row_number() over (partition by id order by endate desc) as rn 
  from table1

will give the following result, then you can filter the result using the rn column and last 5 months criteria.

id  nm  sdate       endate      rn
1   2   09.04.2013  17.06.2013  1
1   1   25.03.2013  14.06.2013  2
2   2   06.03.2013  28.06.2013  1
2   3   05.03.2013  15.06.2013  2

Upvotes: 1

Denisa
Denisa

Reputation: 330

You could use the following select.

select member_id, max(subscription_end_Date)
from yourTable
where subscription_end_Date>= trunc(trunc(sysdate,'year')-1,'year')
group by member_id having months_between(sysdate,max(subscription_end_Date))>=5;

where the trunc(trunc(sysdate,'year')-1,'year') returns the first day of last year.

By using the trunc condition, the query selects only the members that have at least one subscription with subscription_end_Date > '01 - JAN - 2012' and by using the months_between(sysdate,max(subscription_end_Date))>=5 it returns only those that have not subscribed for at least 5 months from last subscription_end_Date.

HTH!

Upvotes: 1

Lokesh
Lokesh

Reputation: 7940

This should do your job [Assuming that query will be executed now as 5 months have passed this year], whole logic is that max subscription end date lies in 2012:

select member_id from (
select member_id, max(subscription_end_Date) ed, max(subscription_start_Date) sd
  from yourTable
group by member_id) a
where trunc(ed, 'YEAR') = 2012 and sd <= add_months(ed,5)

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20794

Something like this should work.

select old.member_id
from subscription old left join subscription renew on old.member_id = renew.member_id
and renew.start_date <= old.end_date + 165 -- (roughly 5 months)
where old.end_date >= {d '2012-01-01'} 
and old.end_date < {d '2013-01-01'}
and renew.start_date is null

oracle might not like that date format, but this should be the correct logic.

Upvotes: 0

Related Questions