Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

dates and compare them to get next value

elmasduroApr 30 2010 — edited May 2 2010
hi all, i am having problem with getting dates that is greater than others. i am using oracle 9i
here is my data sample

WITH table1 AS
(
SELECT To_Date('3/13/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT To_Date('3/17/2010','mm/dd/yyyy') date1, 'smith' name FROM dual

),
table2 AS
(
SELECT To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/20/2010','mm/dd/yyyy') date1 FROM dual
)


these two tables need to be join in such a way that this logic should apply
for example, i want to take one row in table1 in this case it will be 3/13/2010 and go to table2 and get all the
rows that are less or equal to 3/13/2010.

so FOR 3/13/2010 the output should be 
DATE1    	NAME
1/16/2010	john   --since this dates are less than 3/13/2010, take the name value from 3/13/2010
3/12/2010	john     --same as above
then oracle will move on to 3/17/2010 row and again it should go to table2 and take all the dates that are LESS OR equal than 3/17/2010 but greater than 3/12/2010
i said greater THAN 3/12/2010 because 1/16 AND 3/12 were pick up BY 3/13/2010 previously

in this case the output for 3/17/2010 will be 
DATE1    	NAME
3/16/2010	smith
if you notice there is not more date in table1 and table2 has one more date which is 3/20/2010 in this case, this row should be display
as well and output should be 
DATE1    	NAME
3/20/2010	smith   -- take name from max date in table1 that is <= to this row( 3/20/2010)
i try using some analytic function like lastvalue but since i am using oracle 9i the ignore null is not supported.
so i was trying to get the max date and do >= or <= but i wasnt successfuly

the final output should be. 
DATE1    	NAME
1/16/2010	john
3/12/2010	john
3/16/2010	smith
3/20/2010	smith
also i could have the following data IN table2
SELECT To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/20/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/25/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual

IN this dataset output should be 
DATE1    	NAME
1/16/2010	john
3/12/2010	john
3/16/2010	smith
3/20/2010	smith   --take smith name from max date in table1 that is <= to this row( 3/20/2010)
3/25/2010	smith   --take smith name from max date in table1 that is <= to this row
4/20/2010	smith   --take smith name from max date in table1 that is <= to this row
another dataset could be AS follow

also i could have the following data IN table2
SELECT To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual

IN this dataset output should be 
DATE1    	NAME
1/16/2010	john
3/12/2010	john
3/16/2010	smith
if you notice there is no date in table2 that is greater or equal to a date in table1.
in this case the output should be like above.

also table 1 might contain 1 row such as 3/13/2010 and table2 can contain two dates such as 1/16/2010 and 3/12/2010
then in that case the output should be 
DATE1    	NAME
1/16/2010	john   --since this dates are less than 3/13/2010, take the name value from 3/13/2010
3/12/2010	john     --same as above
these are different scenario that can happen with the data.
can somebody help write a query with the outputs listed above for the different dataset
remember i am using 9i and some feature of oracle especially analytic function ignore null is not supported.

thanks a lot everyone IN advance

Comments

Eight Six
Try this not tested


select y.date, case when x.dt1 is null then max_name ELSE x.name END
from
(
select case when lag(date) over( order by date1) is null then to_date('01/01/0001','mm/dd/yyyy') ELSE END as dt1, date1 as dt2 , name ,
max(name) keep (dense_rank first order by date1 desc) as max_name
from table1
)x
Right Outer Join
table2 y
on (y.date1 >= x.dt1 and y.date1<=x.dt2)



Thanks
Aketi Jyuuzou
I think this is a very nice question :-)
It is very important to mension Oracle version at OP.

I like ScalarSubQuery B-) 
WITH table1 AS(
SELECT To_Date('3/13/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT To_Date('3/17/2010','mm/dd/yyyy') date1, 'smith' name FROM dual ),
table2 AS(
SELECT To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/20/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/25/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual)
select date1,
(select
 case max(case when b.date1 >= a.date1 then 1 else 0 end)
 when 1 then max(b.name) Keep(Dense_Rank First order by case when b.date1 >= a.date1
                                                             then 0 else 1 end,b.date1)
 else max(b.name) Keep(Dense_Rank Last order by b.date1) end
   from table1 b) as NAME
from table2 a;

DATE1     NAME
--------  -----
10-01-16  john
10-03-12  john
10-03-16  smith
10-03-20  smith
10-03-25  smith
10-04-20  smith
Boolean algebra in some Rows tutorial by Aketi Jyuuzou
1007678
Etbin
Aketi Jyuuzou wrote:
I think this is a very nice question :-)
and yours is for certain a remarkable answer ... hats off

Regards

Etbin
elmasduro
Aketi, this is great query thanks. i have one more questions. I changed my data in such a way that i introduce a new field call IND.
so my data now looks like this 
WITH table1 AS(
SELECT 111 ind, To_Date('3/13/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT 111 ind,To_Date('3/17/2010','mm/dd/yyyy') date1, 'smith' name FROM dual UNION all
SELECT 112 ind, To_Date('3/12/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT 112 ind,To_Date('3/18/2010','mm/dd/yyyy') date1, 'smith' name FROM dual
),
table2 AS(
SELECT 111 ind,To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 111 ind,To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT 111 ind,To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 111 ind,To_Date('3/20/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 111 ind,To_Date('3/25/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT 111 ind,To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual union ALL

SELECT 112 ind,To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 112 ind,To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT 112 ind,To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 112 ind,To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual 

)
as you can see, each id in table1 has conrresponsdent dates in table2. so each id in table1 should match to
its correspondent id in table2. so the output for the data above should be
IND  DATE1     NAME
---- --------  -----
111  10-01-16  john
111  10-03-12  john
111  10-03-16  smith
111  10-03-20  smith
111  10-03-25  smith
111  10-04-20  smith

112  10-01-16  john
112  10-03-12  john
112  10-03-16  smith
112  10-04-20  smith
how can i change your query so it gives me the output above. as i mentioned above, each ind in table1 should match to the ind in table2.
will the current query take care of this scenario.
thanks

Edited by: elmasduro on May 1, 2010 12:09 PM
Aketi Jyuuzou
This morning of Japan.
I realized this using row_number solution :-)
This solution avoid cross join of my previous solution B-)
WITH table1 AS(
SELECT To_Date('3/13/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT To_Date('3/17/2010','mm/dd/yyyy') date1, 'smith' name FROM dual ),
table2 AS(
SELECT To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/20/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/25/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual)
select date1,name
from (select a.date1,b.name,
      Row_Number() over(partition by a.date1
                        order by b.date1) as rn
        from table2 a
        Join (select date1,name,
              max(date1) over() as maxData1
                from table1) b
          on a.date1 <= b.date1 or b.date1=b.maxData1)
where rn=1;

DATE1     NAME
--------  -----
10-01-16  john
10-03-12  john
10-03-16  smith
10-03-20  smith
10-03-25  smith
10-04-20  smith
Aketi Jyuuzou
how can i change your query so it gives me the output above. as i mentioned above, each ind in table1 should match >to the ind in table2.
will the current query take care of this scenario.
In that case, we have to use inner Join and partition by of OLAP :-)

create table table1(ind,date1,name) as
SELECT 111,To_Date('3/13/2010','mm/dd/yyyy'),'john' name FROM dual UNION ALL
SELECT 111,To_Date('3/17/2010','mm/dd/yyyy'),'smith' name FROM dual UNION all
SELECT 112,To_Date('3/12/2010','mm/dd/yyyy'),'john' name FROM dual UNION ALL
SELECT 112,To_Date('3/18/2010','mm/dd/yyyy'),'smith' name FROM dual;

create table table2(ind,date1) as
SELECT 111,To_Date('1/16/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 111,To_Date('3/12/2010','mm/dd/yyyy') FROM dual UNION all
SELECT 111,To_Date('3/16/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 111,To_Date('3/20/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 111,To_Date('3/25/2010','mm/dd/yyyy') FROM dual UNION all
SELECT 111,To_Date('4/20/2010','mm/dd/yyyy') FROM dual union ALL
SELECT 112,To_Date('1/16/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 112,To_Date('3/12/2010','mm/dd/yyyy') FROM dual UNION all
SELECT 112,To_Date('3/16/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 112,To_Date('4/20/2010','mm/dd/yyyy') FROM dual;
select IND,date1,
(select
 case max(case when b.date1 >= a.date1 then 1 else 0 end)
 when 1 then max(b.name) Keep(Dense_Rank First order by case when b.date1 >= a.date1
                                                             then 0 else 1 end,b.date1)
 else max(b.name) Keep(Dense_Rank Last order by b.date1) end
   from table1 b
  where b.ind = a.ind) as NAME
from table2 a
order by IND,DATE1;

IND  DATE1     NAME
---  --------  -----
111  10-01-16  john
111  10-03-12  john
111  10-03-16  smith
111  10-03-20  smith
111  10-03-25  smith
111  10-04-20  smith
112  10-01-16  john
112  10-03-12  john
112  10-03-16  smith
112  10-04-20  smith
Aketi Jyuuzou
of course we can use below solution,too
select ind,date1,name
from (select a.ind,a.date1,b.name,
      Row_Number() over(partition by a.ind,a.date1
                        order by b.date1) as rn
        from table2 a
        Join (select date1,name,ind,
              max(date1) over(partition by ind) as maxData1
                from table1) b
          on a.ind = b.ind
         and (a.date1 <= b.date1 or b.date1=b.maxData1))
where rn=1
order by IND,DATE1;

IND  DATE1     NAME 
---  --------  -----
111  10-01-16  john 
111  10-03-12  john 
111  10-03-16  smith
111  10-03-20  smith
111  10-03-25  smith
111  10-04-20  smith
112  10-01-16  john 
112  10-03-12  john 
112  10-03-16  smith
112  10-04-20  smith
elmasduro
Aketi, thanks a lot for your help. i really appreciate it. keep up the good work. the last query you send me is exactly what i am looking for. thanks again
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 30 2010
Added on Apr 30 2010
8 comments
2,480 views