TSQL Update with inline, grouping and between

Answer 1

Your condition TableA.Start_Date>=TableB.START_DTTM AND TableB.START_DTTM '< GETDATE() is wrong. This restricts TableB values to those before the startdate and less than current date not those between the two.

The below appears to work.

UPDATE    TableA
SET  Stop_Date = 
   (
   SELECT MIN(TableB.START_DTTM)  
   FROM TableB 
   WHERE 
     TableA.Ref_No = TableB.REFRL_REFNO
   AND (TableB.CancelReason = 'Treatment') 
   AND (TableB.START_DTTM BETWEEN TableA.[Start_Date] AND GETDATE())
   )
WHERE (TableA.Stop_Date IS NULL)