Myujth

I have a table named movement_history (that represenents movements of cars (register, sell, suspended, repaired, destroyed)). I want to get the information of a company
that has sold each cars and to who.

This is my table (select *)

code_car date_movement type_movement current_company_code
 6000 01/01/2010 NEW 5
 6000 01/01/2012 REPARATION 5
 6000 01/11/2015 SOLD 8
 6000 01/01/2017 DESTROYED 8
 4444 01/05/2000 NEW 10
 4444 01/05/2000 SUSPENDED 10
 4444 01/05/2015 SOLD 5
 4444 01/07/2015 RENOVATION 5
 4444 09/12/2015 SOLD 18
 .... ... ... ...

So if i want all the sales of cars from the company 5 in a determinated time (01-01-2015 to 31-02-2015) this would be the result:

 code_car date_movement type_movement current_company_code
 6000 01/11/2015 SOLD 8
 4444 09/12/2015 SOLD 18

That's my query. First I obtain all the cars that someday have been from the company 5. Then I obtain all the 'SOLD' movements and I want to obtain for each car sold if some day has been part of the company 5.

select 
 code_car,date_movement,type_movement,current_company_code 
from 
 movement_history where code_car in (
 (select code_car from movement_history where current_company_code = 5)) and 
code_car IN 
 (select code_car from movement_history where type_movement = 
 'SOLD' and code_car <> 5 and date_movement > to_Date('01-01-2015','dd/mm/yyyy') and date_movement < to_Date('31-12-2015','dd/mm/yyyy'));

I guess am doing badly the part to obtain if each car sold some day has been part of the company 5 al least one time.

Any suggestion? Thanks a lot.

I see. The current_company_code is the company that got the car. So, you want to use LAG():

select mh.*
from (select mh.*,
 lag(mh.current_company_code) over (partition by mh.code_car order by date_movement) as prev_ccc
 from movement_history mh
 ) mh
where mh.type_movement = 'SOLD' and
 mh.date_movement >= date '2015-01-01' and
 mh.date_movement < date '2016-01-01' and
 mh.prev_ccc = 5;