Home » SQL & PL/SQL » SQL & PL/SQL » help with sql query using months_between
help with sql query using months_between [message #210650] Thu, 21 December 2006 12:11 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

T1

ITEM   MTH        PRC

 I1   200605      NULL
 I1   200604      NULL
 I1   200603      NULL
 I1   200602       10
 I1   200601      NULL



T2


ITEM    MTH       PRC
 I1    200512     NULL
 I1    200511      10
 I1    200510     NULL
 I1    200509     NULL
 I1    200508     NULL
 I1    200507      11
 I1    200506     NULL
 I1    200505     NULL
 I1    200504     NULL
 I1    200503      11



I want my result set as

item  no_of_months
I1         3



I would like to find out the number of months since there is no price.(meaning null for price). Data is in two different tables. Most of the time, you can find out in t1, but if it is not in t1, we need to go to t2 and find out the how many months before, we have a price.









Re: help with sql query using months_between [message #210743 is a reply to message #210650] Fri, 22 December 2006 03:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think this is what you're looking for:
create table t1 (item  varchar2(20), mth number(6), prc number(4));

create table t2 (item  varchar2(20), mth number(6), prc number(4));


insert into t1 values ('I1',   200605 ,     NULL);
insert into t1 values ('I1',   200604 ,     NULL);
insert into t1 values ('I1',   200603 ,     NULL);
insert into t1 values ('I1',   200602 ,      10);
insert into t1 values ('I1',   200601 ,     NULL);


insert into t2 values ('I1' ,   200512 ,    NULL);
insert into t2 values ('I1',    200511,      10);
insert into t2 values ('I1',    200510,     NULL);
insert into t2 values ('I1',    200509,     NULL);
insert into t2 values ('I1',    200508,     NULL);
insert into t2 values ('I1',    200507,      11);
insert into t2 values ('I1',    200506,     NULL);
insert into t2 values ('I1',    200505,     NULL);
insert into t2 values ('I1',    200504,     NULL);
insert into t2 values ('I1',    200503,      11);

SQL> select distinct
  2         item,
  3         months_between(
  4         to_date(first_value(mth) over (partition by item order by mth desc),'yyyymm')
  5        ,to_date(first_value(mth) over (partition by item order by decode(prc,null,2,1),mth desc),'yyyymm')) months
  6  from   (select * from t1
  7         union all
  8          select * from t2);

ITEM                     MONTHS
-------------------- ----------
I1                            3
Previous Topic: Stored Procedure: Fetch Excel data through oracle
Next Topic: trim with zero leading values
Goto Forum:
  


Current Time: Thu Dec 08 00:20:27 CST 2016

Total time taken to generate the page: 0.14797 seconds