Java EE (Java Enterprise Edition) General Discussion

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

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

and yours is for certain a remarkable answer ... hats off

Regards

Etbin

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

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

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

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

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