sql - Refer to a value inside of an exists sub-query -

i'm building query uses sub-query. when sub-query returns value want return value, if sub-query returns nothing want return today's date. problem when sub-query has value don't know how refer column want return. in case effdate:

select  case when exists(select top 1 pp.period_end_date [effdate] employee ee (nolock) inner join payplanner pp (nolock) on ee.clientid = pp.clientid     , ee.paygroupuid = pp.paygroupuid ee.clientid = 85845     , pp.clientid = 85845     , ee.empuid = 133066325953730     , ee.m_eff_end_date null     , pp.m_eff_end_date null     , pp.payroll_status in ('e','i')     , pp.date_type in ('r','a') order period_end_date) ???how access effdate here??? else getdate() end 

you can't access columns in subqueries. seem want maximum date -- if exists -- or current date. instead, use aggregation query coalesce():

select coalesce(min(pp.period_end_date), getdate()) [effdate] employee ee (nolock) inner join payplanner pp (nolock) on ee.clientid = pp.clientid     , ee.paygroupuid = pp.paygroupuid ee.clientid = 85845     , pp.clientid = 85845     , ee.empuid = 133066325953730     , ee.m_eff_end_date null     , pp.m_eff_end_date null     , pp.payroll_status in ('e', 'i')     , pp.date_type in ('r', 'a') 

note: query seems want minimum period end date. expect maximum. in case, aggregation function min().