select acct_unit,
Case When active_status='A' then
(select max(CAST(r_date AS DATE)) from glnamesau where glNames.acct_unit=glnamesau.acct_unit AND new_value='A')
ELSE CAST('19000101' as date)
END AS Active_DATE
from glnames