Home » SQL & PL/SQL » SQL & PL/SQL » Help with query to break down the effective dates..
Help with query to break down the effective dates.. [message #269334] Fri, 21 September 2007 13:40 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
I have table like the following.


ITEM     TERM_EFF_IRM	COL1    COL2    COL3    COL4
100363096  200801	A	B	C	D
100363096  200803	A	C	C	D
100363096  200804	A	C	D	D
100363096  200807	A	C	D	E


create table tab1
(item number,
 term_eff_irm  varchar2(6),
 col1  varchar2(1),
 col2  varchar2(1),
 col3  varchar2(1),
 col4  varchar2(1));

insert into tab1
values(100363096,'200801','A','B','C','D');
insert into tab1
values(100363096,'200803','A','C','C','D');
insert into tab1
values(100363096,'200804','A','C','D','D');
insert into tab1
values(100363096,'200807','A','C','D','E');
commit;



I would like the output to include the individual column's effctive irm.

output should be


ITEM     TERM_EFF_IRM	COL1 COL1_EFF  COL2 COL2_EFF  COL3  COL3_EFF COL4 COL3_EFF
100363096  200801	A    200801     B     200801   C    200801     D   200801 
100363096  200803	A    200801     C     200803   C    200801     D   200801 	
100363096  200804	A    200801     C     200803   D    200804     D   200801 	
100363096  200807	A    200801     C     200803   D    200804     E   200807 	





Re: Help with query to break down the effective dates.. [message #269335 is a reply to message #269334] Fri, 21 September 2007 13:43 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

just want to add that each column value should be compared with the previous rows value and then based on that effective irms are found out.

Re: Help with query to break down the effective dates.. [message #269338 is a reply to message #269334] Fri, 21 September 2007 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain the rules that give you the result from the original data.
And give your Oracle version (4 decimals).

Regards
Michel
Re: Help with query to break down the effective dates.. [message #269339 is a reply to message #269334] Fri, 21 September 2007 14:10 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
ITEM     TERM_EFF_IRM	COL1 COL1_EFF  COL2 COL2_EFF  COL3  COL3_EFF COL4 COL4_EFF
100363096  200801	A    200801     B     200801   C    200801     D   200801 
100363096  200803	A    200801     C     200803   C    200801     D   200801 	
100363096  200804	A    200801     C     200803   D    200804     D   200801 	
100363096  200807	A    200801     C     200803   D    200804     E   200807 	


Explanation :
-------------

Col1 stayed same in all 4 term_eff_irm's so its col1_eff is the base month which is '200801'.'200801' can be treated as base date that's why i want '200801' as col1_eff,col2_eff,col3_eff,col4_eff for the term_eff_irm record - '200801'.

Col2 changed from 'B' to 'C' where term_eff_irm is '200803'. so col2_eff for record with Col2 as 'C' should be set to '200803'. Col2 staysed as 'C' till term_eff_irm is '200807'. so all there records except the base record needs to be set to '200803' for col2_eff. col3 stayed as 'C' in both term_eff_irm's '200801' and '200803', so Col3_eff should be set to '200801' for the '200801' & '200803' record. Because Col3 changed from 'C' to 'D' in term_eff_irm '200804', Col3_eff should be set to '200804'.

Hope this helps.

My oracle version is 10.2.0.2
Re: Help with query to break down the effective dates.. [message #269345 is a reply to message #269334] Fri, 21 September 2007 15:10 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member


Select item, term_eff_irm,
       col1, first_value(term_eff_irm) over(partition by item,col1 order by item_sid,term_eff_irm, col1) col1_eff,
       col2, first_value(term_eff_irm) over(partition by item,col2 order by item_sid,term_eff_irm, col2) col2_eff,
       col3, first_value(term_eff_irm) over(partition by item,col3 order by item_sid,term_eff_irm, col3) col3_eff,
       col4, first_value(term_eff_irm) over(partition by item,col4 order by item_sid,term_eff_irm, col4) col4_eff       
from tab1



I came up with the above solution. it seems to work. if any one have a different solution, please let me know.

Re: Help with query to break down the effective dates.. [message #269352 is a reply to message #269345] Fri, 21 September 2007 15:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
A possible alternate solution (all depends on what you want and the data). There is a difference in the output between your solution and this one which i have highlighted in Bold. Ofcourse i have added the data myself. It may be correct, may not be correct. But still i just want to highlight the difference between first_value and this solution.

Quote:

SQL> l
1 select item, term_eff_irm, col1,
2 max(case when col1 != lag_col1 or rn = 1 then term_eff_irm else null end) over(partition by item order by term_eff_irm) col1_Eff, col2,
3 max(case when col2 != lag_col2 or rn = 1 then term_eff_irm else null end) over(partition by item order by term_eff_irm) col2_eff, col3,
4 max(case when col3 != lag_col3 or rn = 1 then term_eff_irm else null end) over(partition by item order by term_eff_irm) col3_Eff, col4,
5 max(case when col4 != lag_col4 or rn = 1 then term_eff_irm else null end) over(partition by item order by term_eff_irm) col4_Eff
6 from
7 (
8 select
9 item, term_eff_irm, col1, row_number() over(partition by item order by term_Eff_irm) rn,
10 lag(col1) over(partition by item order by term_eff_irm) lag_col1, col2,
11 lag(col2) over(partition by item order by term_eff_irm) lag_col2, col3,
12 lag(col3) over(partition by item order by term_eff_irm) lag_col3, col4,
13 lag(col4) over(partition by item order by term_eff_irm) lag_col4 from tab1
14* )

SQL> /

ITEM TERM_E C COL1_E C COL2_E C COL3_E C COL4_E
---------- ------ - ------ - ------ - ------ - ------
100363096 200801 A 200801 B 200801 C 200801 D 200801
100363096 200803 A 200801 C 200803 C 200801 D 200801
100363096 200804 A 200801 C 200803 D 200804 D 200801
100363096 200807 A 200801 C 200803 D 200804 E 200807


SQL>
SQL> insert into tab1
2 values(100363097,'200902','A','B','C','D');

1 row created.

SQL> insert into tab1
2 values(100363097,'200903','A','C','C','C');

1 row created.

SQL> insert into tab1
2 values(100363097,'200906','A','D','E','D');

1 row created.

SQL> insert into tab1
2 values(100363097,'200909','A','C','D','E');

1 row created.

SQL> select * from tab1;

ITEM TERM_E C C C C
---------- ------ - - - -
100363096 200801 A B C D
100363096 200803 A C C D
100363096 200804 A C D D
100363096 200807 A C D E
100363097 200902 A B C D
100363097 200903 A C C C
100363097 200906 A D E D
100363097 200909 A C D E

8 rows selected.

1 select item, term_eff_irm, col1,
2 max(case when col1 != lag_col1 or rn = 1 then term_eff_irm else null end) over(partition by item order by term_eff_irm) col1_Eff, col2,
3 max(case when col2 != lag_col2 or rn = 1 then term_eff_irm else null end) over(partition by item order by term_eff_irm) col2_eff, col3,
4 max(case when col3 != lag_col3 or rn = 1 then term_eff_irm else null end) over(partition by item order by term_eff_irm) col3_Eff, col4,
5 max(case when col4 != lag_col4 or rn = 1 then term_eff_irm else null end) over(partition by item order by term_eff_irm) col4_Eff
6 from
7 (
8 select
9 item, term_eff_irm, col1, row_number() over(partition by item order by term_Eff_irm) rn,
10 lag(col1) over(partition by item order by term_eff_irm) lag_col1, col2,
11 lag(col2) over(partition by item order by term_eff_irm) lag_col2, col3,
12 lag(col3) over(partition by item order by term_eff_irm) lag_col3, col4,
13 lag(col4) over(partition by item order by term_eff_irm) lag_col4 from tab1
14* )
SQL> /

ITEM TERM_E C COL1_E C COL2_E C COL3_E C COL4_E
---------- ------ - ------ - ------ - ------ - ------
100363096 200801 A 200801 B 200801 C 200801 D 200801
100363096 200803 A 200801 C 200803 C 200801 D 200801
100363096 200804 A 200801 C 200803 D 200804 D 200801
100363096 200807 A 200801 C 200803 D 200804 E 200807
100363097 200902 A 200902 B 200902 C 200902 D 200902
100363097 200903 A 200902 C 200903 C 200902 C 200903
100363097 200906 A 200902 D 200906 E 200906 D 200906
100363097 200909 A 200902 C 200909 D 200909 E 200909

8 rows selected.



1 Select item, term_eff_irm,
2 col1, first_value(term_eff_irm) over(partition by item,col1 order by item,term_eff_irm, col1) col1_eff,
3 col2, first_value(term_eff_irm) over(partition by item,col2 order by item,term_eff_irm, col2) col2_eff,
4 col3, first_value(term_eff_irm) over(partition by item,col3 order by item,term_eff_irm, col3) col3_eff,
5 col4, first_value(term_eff_irm) over(partition by item,col4 order by item,term_eff_irm, col4) col4_eff
6* from tab1
SQL> /

ITEM TERM_E C COL1_E C COL2_E C COL3_E C COL4_E
---------- ------ - ------ - ------ - ------ - ------
100363096 200801 A 200801 B 200801 C 200801 D 200801
100363096 200803 A 200801 C 200803 C 200801 D 200801
100363096 200804 A 200801 C 200803 D 200804 D 200801
100363096 200807 A 200801 C 200803 D 200804 E 200807
100363097 200902 A 200902 B 200902 C 200902 D 200902
100363097 200903 A 200902 C 200903 C 200902 C 200903
100363097 200906 A 200902 D 200906 E 200906 D 200902
100363097 200909 A 200902 C 200903 D 200909 E 200909

8 rows selected.




Regards

Raj
Re: Help with query to break down the effective dates.. [message #269355 is a reply to message #269334] Fri, 21 September 2007 15:59 Go to previous message
jinga
Messages: 115
Registered: January 2003
Senior Member
Yes. i realized my query is wrong.

Thanks for your solution...

Anu
Previous Topic: ORA-01890: NLS error detected
Next Topic: problem while using select statememt
Goto Forum:
  


Current Time: Fri Dec 09 17:15:22 CST 2016

Total time taken to generate the page: 0.18765 seconds