select barkod
,alisfiyati
,tarih
from (select d.barkod
,p.alisfiyati
,d.tarih
,row_number() over(partition by d.barkod
order by p.tarih desc
)
as rn
from details as d
left outer join prices as p
on p.barkod=d.brakod
and p.tarih<=d.tarih
) as t
where rn=1
;
or this:
select d.barkod
,p.alisfiyati
,d.tarih
from details as d
left outer join (select a.barkod
,min(p.tarih) as tarih
from details as d
inner join prices as p
on p.barkod=d.brakod
and p.tarih<=d.tarih
group by a.barkod
) as t
on t.barkod=d.barkod
left outer join prices as p
on p.barkod=t.barkod
and p.tarih=t.tarih
;
select barkod
,alisfiyati
,tarih
from (select d.barkod
,p.alisfiyati
,d.tarih
,row_number() over(partition by d.id
,d.barkod
order by p.tarih desc
)
as rn
from details as d
left outer join prices as p
on p.barkod=d.barkod
and p.tarih<=d.tarih
) as t
where rn=1
order by barkod
,tarih
;
Or this:
select d.barkod
,p.alisfiyati
,d.tarih
from details as d
left outer join (select d.id
,d.barkod
,max(p.tarih) as tarih
from details as d
inner join prices as p
on p.barkod=d.barkod
and p.tarih<=d.tarih
group by d.id
,d.barkod
) as t
on t.id=d.id
and t.barkod=d.barkod
left outer join prices as p
on p.barkod=t.barkod
and p.tarih=t.tarih
order by barkod
,tarih
;