Home » SQL & PL/SQL » SQL & PL/SQL » Value for the closest or equal date (Oracle 11.2)
Value for the closest or equal date [message #594101] Fri, 23 August 2013 08:43 Go to next message
pointers
Messages: 355
Registered: May 2008
Senior Member
Hi,

I have two tables e.g. test_bb and test_sub

I would like to select test_sub.price as per the below conditions.

If test_bb.value_date, test_bb.vehicle matches to test_sub.value_date,test_sub.vehicle then display test_sub.price

If there is no match then as above
find the closest test_sub.value_date which is less than test_bb.value_date and select corresponding test_sub.price for the same vehicle combination.



e.g. 

select * from test_sub;

   VEHICLE VALUE_DAT      PRICE
---------- --------- ----------
        10 12-APR-12          2
        10 08-JAN-10          4
        10 14-APR-14          6
        10 06-AUG-47          8
        20 24-JAN-14         10
        20 20-FEB-06         12
        20 18-FEB-04         14
        20 28-FEB-06         16
        20 22-DEC-07         19 
8 rows selected.

select * from test_bb;

   VEHICLE VALUE_DAT
---------- ---------
        10 12-APR-12
        10 10-FEB-10
        20 24-JAN-14
        20 22-FEB-08

Required output:

VEHICLE      PRICE VALUE_DAT
---------- ---------- ---------
        10          2 12-APR-12
        10          4 08-JAN-10
        20         10 24-JAN-14
        20         19 22-DEC-07




create table test_bb(vehicle number, value_date date);

begin
insert into test_bb values(10,to_date('12-04-2012','dd-mm-yyyy'));
insert into test_bb values(10,to_date('10-02-2010','dd-mm-yyyy'));
insert into test_bb values(20,to_date('24-01-2014','dd-mm-yyyy'));
insert into test_bb values(20,to_date('22-02-2008','dd-mm-yyyy'));
end;
/

create table test_sub(vehicle number, value_date date,price number);

begin
insert into test_sub values(10,to_date('12-04-2012','dd-mm-yyyy'),2);
insert into test_sub values(10,to_date('08-01-2010','dd-mm-yyyy'),4);
insert into test_sub values(10,to_date('14-04-2014','dd-mm-yyyy'),6);
insert into test_sub values(10,to_date('06-08-1947','dd-mm-yyyy'),8);
insert into test_sub values(20,to_date('24-01-2014','dd-mm-yyyy'),10);
insert into test_sub values(20,to_date('20-02-2006','dd-mm-yyyy'),12);
insert into test_sub values(20,to_date('18-02-2004','dd-mm-yyyy'),14);
insert into test_sub values(20,to_date('28-02-2006','dd-mm-yyyy'),16);
insert into test_sub values(20,to_date('22-DEC-2007','dd-mm-yyyy'),19);
end;
/

I could write as below but I would like to know if there is a better way of doing it.

select bb.vehicle
      ,sub.price
      ,bb.value_date
   from test_bb bb
       ,test_sub sub
where bb.vehicle=sub.vehicle
  and bb.value_date=sub.value_date
union
select o.vehicle
      ,o.price
      ,o.value_date 
 from 
     (select i.vehicle, max(t.value_date) f_date
     from 
       (
     select bb.vehicle
           ,bb.value_date v_date
        from test_bb bb
            ,test_sub sub
        where bb.vehicle=sub.vehicle(+)
          and bb.value_date=sub.value_date(+)
          and sub.value_date is null
      ) i
      ,test_sub t
where i.vehicle=t.vehicle
  and i.v_date>t.value_date
   group by i.vehicle
    )f
  , test_sub o
where f.f_date=o.value_date
and   f.vehicle=o.vehicle


Regards,
Pointers
Re: Value for the closest or equal date [message #594102 is a reply to message #594101] Fri, 23 August 2013 09:12 Go to previous messageGo to next message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's one option:
SQL> with differ as
  2    (select b.vehicle,
  3            b.value_date b_date,
  4            s.value_date s_date,
  5            b.value_date - s.value_date date_diff,
  6            s.price
  7     from test_bb b,
  8          test_sub s
  9     where b.vehicle = s.vehicle
 10    )
 11  select b.vehicle,
 12         d.price,
 13         d.s_date
 14  from differ d,
 15       test_bb b
 16  where b.vehicle = d.vehicle
 17    and d.date_diff = (select min(abs(d1.date_diff))
 18                       from differ d1
 19                       where d1.vehicle = d.vehicle
 20                         and d1.b_date = b.value_date
 21                      );

   VEHICLE      PRICE S_DATE
---------- ---------- ----------
        10          2 12.04.2012
        10          4 08.01.2010
        20         10 24.01.2014
        20         19 22.12.2007

SQL>
Re: Value for the closest or equal date [message #594103 is a reply to message #594101] Fri, 23 August 2013 09:19 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've over complicated that.

SQL> SELECT r.vehicle, s.price,r.max_date
  2  FROM test_sub s,
  3       (SELECT b.vehicle, b.value_date, max(s.value_date) max_date
  4        FROM test_sub s, test_bb b
  5        WHERE s.vehicle = b.vehicle
  6        AND s.value_date <= b.value_date
  7        GROUP BY b.vehicle, b.value_date) r
  8  WHERE r.vehicle = s.vehicle
  9  AND r.max_date = s.value_date
 10  ORDER BY 1,3;
 
   VEHICLE      PRICE MAX_DATE
---------- ---------- -----------
        10          4 08/01/2010
        10          2 12/04/2012
        20         19 22/12/2007
        20         10 24/01/2014
 
SQL> 


There should also be a way with analytics

[Updated on: Fri, 23 August 2013 09:19]

Report message to a moderator

Re: Value for the closest or equal date [message #594111 is a reply to message #594103] Fri, 23 August 2013 10:55 Go to previous messageGo to next message
DrabJay
Messages: 32
Registered: May 2013
Member
Here is a way with analytics
SELECT DISTINCT
       b.vehicle
      ,b.value_date
      ,last_value(s.value_date) OVER (PARTITION BY b.vehicle, b.value_date ORDER BY s.value_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_value_date
      ,last_value(s.price) OVER (PARTITION BY b.vehicle, b.value_date ORDER BY s.value_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) price
FROM test_bb b
     INNER JOIN test_sub s
       ON (   s.vehicle = b.vehicle
          AND s.value_date <= b.value_date)
ORDER BY 1
        ,2

Re: Value for the closest or equal date [message #594116 is a reply to message #594111] Fri, 23 August 2013 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 60055
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DISTINCT + analytic function means you are wrong in your query.

Regards
Michel
Re: Value for the closest or equal date [message #594120 is a reply to message #594111] Fri, 23 August 2013 13:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
ANALYTIC function with DISTINCT is wasteful. Have a look at this asktom thread where T.Kyte says It would be wasteful to use analytics and then distinct when a poster does the same erroneous thing which you posted.

Go through the thread and research yourself about WHY?
Re: Value for the closest or equal date [message #594141 is a reply to message #594101] Fri, 23 August 2013 18:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2086
Registered: January 2010
Senior Member
This task is about aggregation. Also, I used outer join to display vehicle with null price when no match exists. You can remove it if in such scenario you don't want to display vehicle at all:

select  bb.vehicle,
        max(price) keep(dense_rank last order by sub.value_date) price,
        max(sub.value_date) value_date
  from  test_bb bb,
        test_sub sub
  where sub.vehicle(+) = bb.vehicle
    and sub.value_date(+) <= bb.value_date
  group by bb.vehicle,
           bb.value_date
/

   VEHICLE      PRICE VALUE_DAT
---------- ---------- ---------
        10          4 08-JAN-10
        10          2 12-APR-12
        20         19 22-DEC-07
        20         10 24-JAN-14

SQL>


SY.
Re: Value for the closest or equal date [message #594142 is a reply to message #594141] Fri, 23 August 2013 18:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2086
Registered: January 2010
Senior Member
And if you want anayltic solution:

with t as (
           select  bb.vehicle,
                   first_value(price) over(partition by bb.vehicle,bb.value_date order by sub.value_date desc) price,
                   max(sub.value_date) over(partition by bb.vehicle,bb.value_date) value_date,
                   row_number() over(partition by bb.vehicle,bb.value_date order by sub.value_date desc) rn
             from  test_bb bb,
                   test_sub sub
             where sub.vehicle(+) = bb.vehicle
               and sub.value_date(+) <= bb.value_date
          )
select  vehicle,
        price,
        value_date
  from  t
  where rn = 1
/

   VEHICLE      PRICE VALUE_DAT
---------- ---------- ---------
        10          4 08-JAN-10
        10          2 12-APR-12
        20         19 22-DEC-07
        20         10 24-JAN-14

SQL>


But compare execution plans:

SQL> explain plan for
  2  select  bb.vehicle,
  3          max(price) keep(dense_rank last order by sub.value_date) price,
  4          max(sub.value_date) value_date
  5    from  test_bb bb,
  6          test_sub sub
  7    where sub.vehicle(+) = bb.vehicle
  8      and sub.value_date(+) <= bb.value_date
  9    group by bb.vehicle,
 10             bb.value_date
 11  /

Explained.

SQL> select  *
  2    from  table(dbms_xplan.display)
  3  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2528759321

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     4 |   228 |     8  (25)| 00:00:01 |
|   1 |  SORT GROUP BY      |          |     4 |   228 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |          |     4 |   228 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST_BB  |     4 |    88 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST_SUB |     9 |   315 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SUB"."VEHICLE"(+)="BB"."VEHICLE")
       filter("SUB"."VALUE_DATE"(+)<="BB"."VALUE_DATE")

Note
-----
   - dynamic sampling used for this statement (level=2)

21 rows selected.

SQL> explain plan for
  2  with t as (
  3             select  bb.vehicle,
  4                     first_value(price) over(partition by bb.vehicle,bb.value_date order by sub.value_date desc) price,
  5                     max(sub.value_date) over(partition by bb.vehicle,bb.value_date) value_date,
  6                     row_number() over(partition by bb.vehicle,bb.value_date order by sub.value_date desc) rn
  7               from  test_bb bb,
  8                     test_sub sub
  9               where sub.vehicle(+) = bb.vehicle
 10                 and sub.value_date(+) <= bb.value_date
 11            )
 12  select  vehicle,
 13          price,
 14          value_date
 15    from  t
 16    where rn = 1
 17  /

Explained.

SQL> select  *
  2    from  table(dbms_xplan.display)
  3  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2701476954

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     4 |   192 |     8  (25)| 00:00:01 |
|*  1 |  VIEW                |          |     4 |   192 |     8  (25)| 00:00:01 |
|   2 |   WINDOW SORT        |          |     4 |   228 |     8  (25)| 00:00:01 |
|*  3 |    HASH JOIN OUTER   |          |     4 |   228 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TEST_BB  |     4 |    88 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| TEST_SUB |     9 |   315 |     3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"=1)
   3 - access("SUB"."VEHICLE"(+)="BB"."VEHICLE")
       filter("SUB"."VALUE_DATE"(+)<="BB"."VALUE_DATE")

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)

23 rows selected.

SQL>


SY.
Re: Value for the closest or equal date [message #594445 is a reply to message #594101] Wed, 28 August 2013 09:32 Go to previous message
pointers
Messages: 355
Registered: May 2008
Senior Member
Thank you Solomon Yakobson that was brilliant.
Thanks to Cookiemonster, littlefoot and DrabJay as well.

Regards,
Pointers
Previous Topic: Delete duplicate records without Rowid and drop (2 Merged)
Next Topic: Getting ORA : 00905 while synonym creation
Goto Forum:
  


Current Time: Thu Dec 25 11:52:20 CST 2014

Total time taken to generate the page: 0.08672 seconds