select deals.deal_date as d_date, orders.order_id ,orders.buysell, orders.ccy_pair, orders.amount as o_amount, orders.rate as o_rate, 
deals.amount as d_amount, deals.rate as d_rate
from orders 


inner join deals on

and orders.status = 'CANCELED'
--trying to find deal 
and deals.id = (SELECT id
        FROM (SELECT id,
               rank() over (partition by ccy_pair,buysell
                                order by deal_date) rnk 
          FROM deals  where  ccy_pair= orders.ccy_pair and deal_date >  orders.sent_date 
          and  buysell = orders.buysell
          )
        WHERE rnk = 1)
order by orders.sent_date desc

select o.*,
case when o.status = 'CANCELLED' THEN d1.id ELSE d.id END AS DealID,
case when o.status = 'CANCELLED' THEN d1.date_date ELSE d.date_date END AS DealDate,
...
from Orders o
left join Deals d
ON d.order_id = o.order_id
outer apply (select *
             from Deals
             where ccy_pair = o.ccy_pair
             and  buyOrSell = o.buyOrSell 
             and date_date > o.sent_date
             order by date_date asc)d1