SQL Script to show latest record from another table based on date.
Select t1.Project_ID as id
, t1.Technical_ID as tid
, t3.Name
, isnull(dbo.charsAscii(dbo.asciiChars(a.E_Rate)) * t1.Hours,0) as Labour
, t1.Date_added
, t2.RD as rd
from Technical_Cost_Labour as t1
cross apply (
select top 1 t2.*
from Project_Cost_Workers_Rates as t2
where t1.Worker_ID = t2.WorkerID
and t2.Rate_Date <= t1.Date_added
order by t2.Rate_Date desc
) a
inner join Project_Technical_Header as t2
on t1.Technical_ID = t2.Technical_ID
left join Project_Cost_Workers as t3
on t1.Worker_ID = t3.WorkerID
