Q:

How to filter TOP 1 condition in WHERE clause

What would be the most efficient way to eliminate records in WHERE clause using TOP 1 logic?
Table tblQuoteStatusChangeLog is not in a JOIN. 
But based on value in this table I need to eliminate records that have NewQuoteStatusID = 12
It works the way it is, but I am looking for more efficient way, since I have Sort (Top N Sort) operator that is too expansive. 
SELECT    
    Q.ControlNo   
    ,sum(fid.amtbilled) as Premium

FROM           
    [dbo].tblQuotes Q 
        inner join [dbo].[tblFin_Invoices] FI  on Q.QuoteID = FI.QuoteID and FI.failed = 0  
        inner join [dbo].[tblFin_InvoiceDetails] FID  on FI.[InvoiceNum] = FID.InvoiceNum  
WHERE       (
            SELECT  TOP 1 NewQuoteStatusID
             FROM    tblQuoteStatusChangeLog
             WHERE   (ControlNo = Q.ControlNo)
             ORDER BY Timestamp DESC
             ) <> 12

Group by
Q.ControlNo

A:

Your code is RBAR; performing the same subquery 1 at a time, which is very inefficient.
You worry about "sort", but that by itself would not be a problem. Look further up and left of the plan; to the nested loop. See the fat input line at the top and thin just below. Basically you're hitting your sort very many times.
Suggestion: try to use a set-based solution. "Prepare" the data you require for the WHERE clause "in advance", so you can eliminate the RBAR. Imagine you had LatestStatus as a table with ControlNo and StatusID columns. It would be much simpler to apply your filter; and the Query Optimiser should be able to find a more efficient overall plan.
You can set this up using a CTE.
;with StatusByControlNo as (
  SELECT  ROW_NUMBER() OVER(PARTITION BY ControlNo ORDER BY Timestamp DESC) AS RowNo,
          ControlNo, Timestamp, NewQuoteStatusID
  FROM    tblQuoteStatusChangeLog
) ...
/*Easy to get Latest status per ControlNo from here*/
SELECT  ControlNo, NewQuoteStatusID
FROM    StatusByControlNo
WHERE   RowNo = 1

Now with a few tweaks your query becomes:
;with StatusByControlNo as (
  SELECT  ROW_NUMBER() OVER(PARTITION BY ControlNo ORDER BY Timestamp DESC) AS RowNo,
          ControlNo, Timestamp, NewQuoteStatusID
  FROM    tblQuoteStatusChangeLog
)
SELECT    
    Q.ControlNo,
    sum(fid.amtbilled) as Premium
FROM           
    tblQuotes Q 
    inner join tblFin_Invoices FI
        on Q.QuoteID = FI.QuoteID and FI.failed = 0
    inner join tblFin_InvoiceDetails FID
        on FI.InvoiceNum = FID.InvoiceNum
    inner join StatusByControlNo S
        on S.ControlNo = Q.ControlNo and S.RowNo = 1
WHERE
    S.ControlNo <> 12
Group by Q.ControlNo

It should go without saying you could try a number of variations on this. But the core principle is to reduce RBAR and look for solutions that are more 'set-based'.

