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

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

Table:

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....


Naveen

[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
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
Hi,
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
from
(select
a.Type_NM,
dt+rownum-1 date_dt
from
(select min(start_dt) dt from table1) a,
user_objects b,
table1 a
where
(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


regards,
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
prashant_pathak
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))

TN	VAL	SD	ED
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(+)
union
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

Output


TN	SD	OLD_VAL	NEW_VAL
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
srinivnp
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;

TYPE_NM VALUE_VL START_DT END_DT
---------- ---------- --------- ---------
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.




Quote:
select type_nm,dt ,oldval,newval
from
(
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> /

TYPE_NM DT OLDVAL NEWVAL
---------- --------- -------- --------
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.

Srini

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


Current Time: Sun Dec 04 02:34:28 CST 2016

Total time taken to generate the page: 0.08441 seconds