Home » SQL & PL/SQL » SQL & PL/SQL » SQL query
SQL query [message #214697] Wed, 17 January 2007 11:03 Go to next message
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Not sure whether similar type of question was raised before...


TYPE_NM 	|	  VALUE_VL	|	     START_DT	|	     END_DT
    A			     Val1			Date1			Date2
    A			     Val2			Date2			Date3
    A			     Val3			Date3			Date4
    A			     Val4			Date5			Date6
    A			     Val5			Date6			Date7
    A			     Val6			Date8	

Required Output:

TYPE_NM       DATE_DT		       OLD_VL			      NEW_VL

     A        Date1			NULL				Val1
     A        Date2			Val1				Val2
     A        Date3			Val2				Val3
     A        Date4			Val3				NULL
     A        Date5			NULL				Val4
     A        Date6			Val4				Val5
     A        Date7			Val5				NULL
     A        Date8			NULL				Val6

Hope, the result set is self explanatory...if not let me know will explain....


[Updated on: Wed, 17 January 2007 11:06]

Report message to a moderator

icon14.gif  Re: SQL query [message #214699 is a reply to message #214697] Wed, 17 January 2007 11:32 Go to previous messageGo to next message
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
Hope this helps you.

create table Table1 (TYPE_NM varchar2(10),VALUE_VL varchar2(10),START_DT date, END_DT date)

insert into Table1 values ('A','Val1',sysdate + 1,sysdate + 2);
insert into Table1 values ('A','Val2',sysDate + 2,sysdate + 3);
insert into Table1 values ('A','Val3',sysdate + 3,sysdate + 4);
insert into Table1 values ('A','Val4',sysdate + 5,sysdate + 6);
insert into Table1 values ('A','Val5',sysdate + 6,sysdate + 7);
insert into Table1 values ('A','Val6',sysdate + 8,null);

select * from table1;
type_nm value_vl start_dt end_dt
A Val1 18/01/2007 19/01/2007
A Val2 19/01/2007 20/01/2007
A Val3 20/01/2007 21/01/2007
A Val4 22/01/2007 23/01/2007
A Val5 23/01/2007 24/01/2007
A Val6 25/01/2007

select Type_NM,date_dt,
(select nvl(VALUE_VL,'NULL') from Table1 where START_DT= (date_dt-1)) Old_Val,
(select nvl(VALUE_VL,'NULL') from Table1 where START_DT= (date_dt)) New_val
dt+rownum-1 date_dt
(select min(start_dt) dt from table1) a,
user_objects b,
table1 a
(dt+rownum-1) <= (select max(start_dt) from table1));

type_nm date_dt old_val new_val
A 18/01/2007 NULL Val1
A 19/01/2007 Val1 Val2
A 20/01/2007 Val2 Val3
A 21/01/2007 Val3 NULL
A 22/01/2007 NULL Val4
A 23/01/2007 Val4 Val5
A 24/01/2007 Val5 NULL
A 25/01/2007 NULL Val6

Anil Sinare
Re: SQL query [message #214701 is a reply to message #214697] Wed, 17 January 2007 11:42 Go to previous messageGo to next message
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
create table test_dummy (tn varchar2(1),val number,sd varchar2(10),ed varchar2(10))

a	1	d1	d2
a	2	d2	d3
a	3	d3	d4
a	4	d5	d6
a	5	d6	d7
a	6	d8	

select main.tn,main.sd,child.val old_val, main.val new_val 
from test_dummy main, test_dummy child
where main.sd=child.ed(+)
select child.tn,child.ed,child.val,main.val
from test_dummy child,test_dummy main
where child.ed=main.sd(+)
and child.ed is not null


a	d1		1
a	d2	1	2
a	d3	2	3
a	d4	3	
a	d5		4
a	d6	4	5
a	d7	5	
a	d8		6

Re: SQL query [message #214727 is a reply to message #214697] Wed, 17 January 2007 15:56 Go to previous message
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Let us try and do it with a single scan of the table .

SQL> select * from foo;

---------- ---------- --------- ---------
A Val1 18-JAN-07 19-JAN-07
A Val2 19-JAN-07 20-JAN-07
A Val3 20-JAN-07 21-JAN-07
A Val4 22-JAN-07 23-JAN-07
A Val5 23-JAN-07 24-JAN-07
A Val6 25-JAN-07

6 rows selected.

select type_nm,dt ,oldval,newval
select type_nm,dt ,oldval,newval
from foo ,
( select rownum from dual connect by rownum < 3) dl
model return all rows
main grr
partition by (type_nm)
dimension by (row_number()
over (partition by type_nm order by start_dt ) as rn )
measures ( value_vL , start_dt,end_dt ,
cast (null as date) dt ,
cast(null as varchar2(100)) as newval ,
cast (null as varchar2(100)) as oldval,
row_number() over (partition by type_nm,value_vL,start_dt
order by end_dt) as altn )
unique dimension
rules sequential order
dt[any] order by rn = case when altn[cv()]=1 then start_dt[cv()]
when altn[cv()]=2 and end_dt[cv()] < start_dt[cv()+1]
then end_dt[cv()] else null end ,
value_vL[any] order by rn =value_vL[cv()],
newval[any] order by rn = case when altn[cv()]=1 then value_vL[cv()]
else null end
, oldval[any] order by rn = case when dt[cv()]=end_dt[cv()-1] then value_vL[cv()-1]
else null end
) where dt is not null
order by dt

SQL> /

---------- --------- -------- --------
A 18-JAN-07 Val1
A 19-JAN-07 Val1 Val2
A 20-JAN-07 Val2 Val3
A 21-JAN-07 Val3
A 22-JAN-07 Val4
A 23-JAN-07 Val4 Val5
A 24-JAN-07 Val5
A 25-JAN-07 Val6

8 rows selected.


Previous Topic: Updating and Deleting from collections
Next Topic: Getting Consecutive Numbers
Goto Forum:

Current Time: Mon Oct 24 17:19:37 CDT 2016

Total time taken to generate the page: 0.09668 seconds