patla4u (2/8/2016)
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;
Why did you commented the order by? That's important to ensure that the correct row is chosen. Why aren't you comparing the date? You need something to be either on a previous shift the same day or a previous day with any shift. (Just translate this to T-SQL)