with contract_table as (
	select level id_contract from dual connect by level <= 5
),
table_history as (
	select 1 id_contract, date'2000-01-01' history_date, 'a' contract_status from dual
	union all select 2, date'2000-01-01', 'b' from dual
	union all select 2, date'2000-02-01', 'c' from dual
	union all select 3, date'2000-01-01', 'd' from dual
	union all select 3, date'2000-01-02', 'e' from dual
	union all select 3, date'2000-02-01', 'f' from dual
	union all select 4, date'2000-03-01', 'g' from dual
)
select ct.id_contract,
		max(th.contract_status) keep (dense_rank last order by th.history_date) as contract_status
	from contract_table ct
		left join table_history th
			on th.id_contract = ct.id_contract
				and th.history_date < date'2000-02-05'
	group by ct.id_contract;