Hi ,
Check this it will give you Idea try it
with TName as (
SELECT Line_No, isnull(Tool_id, ' ') Tool_id, QUAD_ID, Tool_grade, EARLY_WARNING_FLAG, Department, STATION,
CASE WHEN DATEDIFF(MI,ISNULL(NOK_TIME, TIMESTAMP2),GETDATE()) > 1440 THEN TIMESTAMP2
ELSE ISNULL(NOK_TIME, TIMESTAMP2) END AS NOK_TIME, Ack_Time,
DATEDIFF(MI,TIMESTAMP2,GETDATE()) AS DOWNDURATION FROM TX_STATION_STATUS NOLOCK
WHERE LINE_NO LIKE '7%'
AND STATION LIKE'S%'
AND
(TOOL_ID IS NOT NULL AND TOOL_ID IN (SELECT TESTER_ID FROM TX_CR_QTY_BUILD WITH (NOLOCK) WHERE ((LINE_NO LIKE '7%') AND (LINE_NO LIKE '%')) AND TESTER_ID = TOOL_ID AND BUILD_HOUR + 1 >= getdate()))
OR (TOOL_ID IS NULL AND STATION IN (SELECT STATION FROM TX_CR_QTY_BUILD WITH (NOLOCK) WHERE ((LINE_NO LIKE '7%') AND (LINE_NO LIKE '')) AND STATION = STATION AND BUILD_HOUR + 1 >= getdate()) )
AND STATION+ISNULL(TOOL_ID,'')+CONVERT(VARCHAR(30), Timestamp2, 126) IN (
SELECT STATION+ISNULL(TOOL_ID,'')+CONVERT(VARCHAR(30), MAX(Timestamp2), 126)
FROM TX_STATION_STATUS WITH (NOLOCK)
WHERE LINE_NO LIKE '7%'
GROUP BY STATION, TOOL_ID)
)
select col1,col2 ,TableName.Line_No from TName inner join TableName
on
TableName.col1 = TName.Tool_id
Best Regards
M.Mitwalli