SELECT T1.ACCOUNT_ID, T1.ACCOUNT_NAME, ....
         , (CASE WHEN T2.ACTIVE = 1 THEN 'Active' ELSE 'Inactive' END) as ACCOUNT_STATUS
FROM Account_Master AS T1
LEFT OUTER JOIN 
( SELECT ACCOUNT_ID, ACTIVE FROM 
(SELECT account_id, active, status_date
, ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY status_date DESC) AS ROWNUM 
FROM account_DeactivationReactivationHistory ) AS T2 
WHERE ROWNUM = 1
) AS T2 
ON T1.ACCOUNT_ID = T2.ACCOUNT_ID