SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table CRA_feasibility_test(
     atmid varchar(50),
     CRA varchar(50),
     monday  varchar(50),
     tuesday varchar(50),
     wednesday  varchar(50),
     thursday    varchar(50),
     friday  varchar(50),
     saturday varchar(50)
);

insert into CRA_feasibility_test values ('DA0068C1','ABC','Y','N','Y','N','N','Y');

declare @dateT datetime
set @dateT ='2018-06-15'

;with calendar as (
  select @dateT startDt,dateadd(day,7,@dateT) endDt, 1 rn
  union all
  select dateadd(day,1,startDt) , endDt,rn +1
  from calendar
  WHERE  dateadd(day,1,startDt)<endDt
), cte as (
select 
        atmid,v.*
from CRA_feasibility_test cross apply (
	values ('monday',monday), ('tuesday',tuesday), ('wednesday',wednesday), ('thursday',thursday), ('friday',friday), ('saturday',saturday)
) v(name,flag)

)

select atmid,
	@dateT today,
	min(case when flag = 'Y' then startDt end) nextYDay,
	datename(dw,min(case when flag = 'Y' then startDt end) ) nextYname
from calendar t1 
join cte t2 on datename(dw,startDt) = t2.name
group by atmid

atmid	today	nextYDay	nextYname
DA0068C1	15/06/2018 00:00:00	16/06/2018 00:00:00	Saturday

Warning: Null value is eliminated by an aggregate or other SET operation.