You just need to add the table in a CROSS APPLY or OUTER APPLY and select TOP(1) with an ORDER BY date DESC
...
From Beaufort.dbo.DPIB010 t1
CROSS...

frederico is right, you should change the table definition to store date type values. But you can order that string in date order
SELECT TOP(1) *
  FROM...

SQL Server will use all the memory you allow it to if it can, it buffers data in memory.
You should limit the amount of memory SQL Server can...

I had to do something similar. I ended up having job2 (that's dependant on job1) scheduled to run at frequent intervals, the first step would test the latest row on...

Are you sure it's the sort that's taking the time? Have you tried it without the sort and if so is it much faster?

SELECT t.RecordId,
        t.dateId FromDate,
        x.dateID ToDate,
        t.Quantity
   FROM @T t
  CROSS APPLY(SELECT TOP(1)...

Very impressive!

It might be my lack of understanding about the MERGE statement, I've always found performance better by replacing a MERGE with UPDATES followed by INSERTS. But at the top of...

case when @valiValue is null then 'N/A' else

         CASE WHEN CHARINDEX(''''+RTRIM(@VALUE)+'''', @valiValue) > 0 THEN

         'PASS' else 'FAIL' END END as Validation_Result,

Just create the table, populate it with as many realistic rows as you can then run the report.
To run the report you just right click on the database in...

Here's a way to do it using only mathematical operations, I think it will be less efficient than using a replace though.

DECLARE @table TABLE (ID INT...