Home » SQL & PL/SQL » SQL & PL/SQL » access previous records (10.)
access previous records [message #427131] Wed, 21 October 2009 05:08 Go to next message
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 #427136 is a reply to message #427131] Wed, 21 October 2009 05:25 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Use LAG/LEAD , CASE WHEN and GROUP BY..
Re: access previous records [message #427137 is a reply to message #427131] Wed, 21 October 2009 05:25 Go to previous messageGo to next message
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;
Re: access previous records [message #427503 is a reply to message #427137] Thu, 22 October 2009 23:58 Go to previous message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
thanx JRowbottom,

it was helpful.

-maheshmhs
Previous Topic: Somebody please explain this query for me.
Next Topic: Code Review
Goto Forum:
  


Current Time: Sun Sep 25 16:17:59 CDT 2016

Total time taken to generate the page: 0.13804 seconds