-3

I have two tables, the first one is:

contract
| id | name | start | end | amount |

contract_evaluation
| id | contract_id | percentage date |

A contract might have more than one record in contract_evaluation table

What I want is a query that retrieves:

id,name,start from contracts == plus == (max)percentage, date of this contract in table contract_evaluation

BecauseGeek
  • 17
  • 1
  • 1
  • 6

2 Answers2

0

This is T-SQL syntax, but it should be usable, or easily modified, for Oracle. 

/* SETUP */
CREATE TABLE contract ( id int, name varchar(10), start date, end date, amount decimal(10,2) );
INSERT INTO contract (id, name, start, end, amount)
VALUES 
      (1,'Foo','1/1/2017','2/1/2017',10000.00)
    , (2,'Bar','4/1/2017','6/1/2017',20000.00)
;

CREATE TABLE contract_evaluation ( id int, contract_id int, percentage decimal(5,2), date date ) ;
INSERT INTO contract_evaluation (id, contract_id, percentage, date)
VALUES 
      (1,1,2.0,'1/1/2016')
    , (2,2,5.0,'1/1/2017')
    , (3,2,2.5,'4/2/2017')
    , (4,1,3.5,'1/2/2017')
;

/* QUERY TIME */
; WITH cte_ce AS (
    SELECT ce1.* 
    FROM (
        SELECT contract_id, percentage
            , ROW_NUMBER() OVER (PARTITION BY contract_id ORDER BY date DESC) AS rn
        FROM contract_evaluation
    ) ce1
    WHERE ce1.rn = 1
)
SELECT c.name, c.start, c.end, c.amount, cte_ce.percentage, ( c.amount + (c.amount*(COALESCE(cte_ce.percentage,0)/100)) ) AS newAmount
FROM contract c
LEFT OUTER JOIN cte_ce ON c.id = cte_ce.contract_id
Shawn
  • 4,758
  • 1
  • 20
  • 29
0

I found a solution for oracle within the following script. This is mainly the same approach as Shawn's but for oracle. Both mainly bases on the analytical function RANK() OVER (partition by ... order by ...). The problem is to get the cur_date corresponding to the percentage which is not possible with a simple aggregation.

Have Fun

create table contract (    
    "ID"     number primary key,
    "NAME"   varchar2(50),     
    "START"  date,     
    "END"    date,     
    amount   number 
);

create table contract_evaluation (
   "ID"         number primary key,
   contract_id  number references contract("ID"),
   percentage   number,
   cur_date     date   
);

insert into contract values ( 1, 'contract 1', sysdate-5, sysdate-2, 500  );
insert into contract values ( 2, 'contract 2', sysdate-10, sysdate-2, 500  );
insert into contract values ( 3, 'contract 3', sysdate-8, sysdate-2, 500  );

insert into contract_evaluation values ( 1, 1, 22, sysdate-5 );
insert into contract_evaluation values ( 2, 1, 44, sysdate-4 );
insert into contract_evaluation values ( 3, 1, 100, sysdate-3 );
insert into contract_evaluation values ( 4, 2, 10, sysdate-8 );
insert into contract_evaluation values ( 5, 2, 20, sysdate-6 );
insert into contract_evaluation values ( 6, 2, 30, sysdate-5 );

select inner_select.* 
  from ( select c."ID", 
                c."NAME", 
                c."START", 
                ce.percentage,
                ce.cur_date,
                rank() over (partition by contract_id order by percentage desc) as rnk
           from contract c
      left join contract_evaluation ce on ce.CONTRACT_ID = c."ID" ) inner_select
  where inner_select.rnk = 1

/*
drop table contract_evaluation;
drop table contract;
*/
Benjamin
  • 139
  • 6