access previous records [message #427131] |
Wed, 21 October 2009 05:08  |
maheshmhs
Messages: 93 Registered: October 2008 Location: Nepal
|
Member |
|
|
Hello All,
I have been stuck at a point.
I have a table with 2 rows memid and refdate.
For each memid, maximum continuous period for a member is to be found. The table is follows
MEMID REFDATE
111114Sj37S 01.10.2005 00:00:00
111114Sj37S 01.11.2005 00:00:00
111114Sj37S 01.12.2005 00:00:00
111114Sj37S 01.03.2006 00:00:00
111114Sj37S 01.04.2006 00:00:00
111114Sj37S 01.05.2006 00:00:00
221114Sj37S 01.05.2004 00:00:00
221114Sj37S 01.06.2004 00:00:00
now i want it as below
MEMID PERIOD
111114Sj37S 3
221114Sj37S 2
here for memid 111114Sj37S has period 3 because the memid has maximum of continuous 3 months of refdate but 221114Sj37S has continuous 2 months of period.
I looked at this problem for a really long time and am still clueless as how to do. Please help
Thank you
|
|
|
|
Re: access previous records [message #427137 is a reply to message #427131] |
Wed, 21 October 2009 05:25   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
This should point you in a new and fruitful direction:with src as (
select '111114Sj37S' MEMID, to_date('01.10.2005 00:00:00','dd-mm-yyyy hh24:mi:ss') refdate from dual union all
select '111114Sj37S' MEMID, to_date('01.11.2005 00:00:00','dd-mm-yyyy hh24:mi:ss') refdate from dual union all
select '111114Sj37S' MEMID, to_date('01.12.2005 00:00:00','dd-mm-yyyy hh24:mi:ss') refdate from dual union all
select '111114Sj37S' MEMID, to_date('01.03.2006 00:00:00','dd-mm-yyyy hh24:mi:ss') refdate from dual union all
select '111114Sj37S' MEMID, to_date('01.04.2006 00:00:00','dd-mm-yyyy hh24:mi:ss') refdate from dual union all
select '111114Sj37S' MEMID, to_date('01.05.2006 00:00:00','dd-mm-yyyy hh24:mi:ss') refdate from dual union all
select '221114Sj37S' MEMID, to_date('01.05.2004 00:00:00','dd-mm-yyyy hh24:mi:ss') refdate from dual union all
select '221114Sj37S' MEMID, to_date('01.06.2004 00:00:00','dd-mm-yyyy hh24:mi:ss') refdate from dual)
select memid
,refdate
,add_months(refdate,-1*row_number() over (partition by memid order by refdate)) grp
from src;
|
|
|
|