Home » SQL & PL/SQL » SQL & PL/SQL » help on sql query
help on sql query [message #232850] Mon, 23 April 2007 10:45 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
WORK_REC

ITEM            TREE             CELL                  MONTH

I1              T1                    C1               200605
I1              T1                    C1               200606
I1              T1                    C1               200607
I1              T1                    C2               200608
I1              T1                    C1               200609




APPR_REC

ITEM    TREE           CELL        EFF_FROM    EFF_TO

I1         T1              C1      200401       200607
I1         T1              C2      200608       200608
I1         T1              C1      200609	200609



My work_rec is based on monthly data and appr_rec is based on rows combined into eff_from and eff_to period.


I would like to build an sql query that gives me the result set (joining both the tables to build the eff_from column) …


ITEM  TREE        CELL           MONTH       EFF_FROM
  
I1     T1          C1           200605      200401
I1     T1          C1           200606      200401    
I1     T1          C1           200607      200401 
I1     T1          C2           200608      200608  
I1     T1          C1           200609      200609	



Re: help on sql query [message #232854 is a reply to message #232850] Mon, 23 April 2007 11:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you put this in "SQL Experts" I think you already tested many basic SQL and was unable to find a solution.
So put what you already tried.

AND ALWAYS PUT YOUR ORACLE VERSION.

Regards
Michel
Re: help on sql query [message #232881 is a reply to message #232850] Mon, 23 April 2007 15:23 Go to previous message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

SQL> select * from foo3;

ITE TRE CEL MONTH
--- --- --- -------------------
I1 T1 C1 2006-07-01 00:00:00
I1 T1 C2 2006-08-01 00:00:00
I1 T1 C1 2006-09-01 00:00:00
I1 T1 C1 2006-05-01 00:00:00
I1 T1 C1 2006-06-01 00:00:00


SQL> select * from foo2;

ITE TRE CEL EFF_FROM EFF_TO
--- --- --- ------------------- -------------------
I1 T1 C1 2004-01-01 00:00:00 2006-07-01 00:00:00
I1 T1 C1 2006-08-01 00:00:00 2006-08-01 00:00:00
I1 T1 C1 2006-09-01 00:00:00 2006-09-01 00:00:00





    select item,tree,cell,mont,eff
     from (
    select f1.item,f1.tree,f1.cell,
     to_char(f1.month,'yyyymm') mont ,
     to_char(f2.eff_from,'yyyymm') eff ,
     row_number() over (partition by f1.item,f1.tree,f1.cell,f1.month
       order by f1.month -f2.eff_from) rn
     from foo3 f1,
      foo2 f2
    where f1.month >= f2.eff_from
   ) where rn=1
   order by mont
SQL> /

ITE TRE CEL MONT EFF
--- --- --- ------ ------
I1 T1 C1 200605 200401
I1 T1 C1 200606 200401
I1 T1 C1 200607 200401
I1 T1 C2 200608 200608
I1 T1 C1 200609 200609


Srini


Previous Topic: Triple Outer Join with WHERE and ORDER conditions
Next Topic: Max sal
Goto Forum:
  


Current Time: Fri Dec 09 19:27:42 CST 2016

Total time taken to generate the page: 0.14854 seconds