8 Replies Latest reply on May 2, 2010 7:02 PM by elmasduro

    dates and compare them to get next value

    elmasduro
      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
        • 1. Re: dates and compare them to get next value
          user10857924
          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
          • 2. Re: dates and compare them to get next value
            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
            Boolean algebra in some Rows tutorial by Aketi Jyuuzou
            • 3. Re: dates and compare them to get next value
              Etbin
              Aketi Jyuuzou wrote:
              I think this is a very nice question :-)
              and yours is for certain a remarkable answer ... hats off

              Regards

              Etbin
              • 4. Re: dates and compare them to get next value
                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
                • 5. Re: dates and compare them to get next value
                  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
                  • 6. Re: dates and compare them to get next value
                    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
                    • 7. Re: dates and compare them to get next value
                      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
                      • 8. Re: dates and compare them to get next value
                        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