SELECT o.*,

COALESCE( NULLIF(o.t_wght, 0), x.t_wght, 0)

FROM [TBRTEK002] o

OUTER APPLY (

SELECT TOP 1 max(i.t_wght) as t_wght

FROM [TBRTEK002] i

WHERE i.t_mcno = o.t_mcno

AND i.t_citg = o.t_citg

AND i.t_shft < o.t_shft

-- ORDER BY i.t_mcno,i.t_citg,i.t_trdt desc,i.t_shft DESC

) x

WHERE o.t_mcno = 'F01'

ORDER BY [t_mcno],[t_citg],t_trdt desc, t_shft desc;