Perhaps this is what you mean.

WITH SampleData (date, HourType, hours, HourValue, status) AS(

SELECT 'day1','m',1,1,'y'

UNION ALL SELECT 'day1','m',2,1,'y'

UNION ALL SELECT 'day1','m',3,1,'y'

UNION ALL SELECT 'day1','a',4,0,'n'

UNION ALL SELECT 'day1','a',5,0,'n'

UNION ALL SELECT 'day1','a',6,0,'n'

UNION ALL SELECT 'day2','m',1,1,'y'

UNION ALL SELECT 'day2','m',2,1,'y'

UNION ALL SELECT 'day2','m',3,1,'y'

UNION ALL SELECT 'day2','a',4,0,'y'

UNION ALL SELECT 'day2','a',5,1,'y'

UNION ALL SELECT 'day2','a',6,1,'y'

)

SELECT *

,CASE (

SELECT MAX(HourValue)

FROM SampleData b

WHERE a.date = b.date AND a.HourType = b.HourType

) WHEN 1 THEN 'y' ELSE 'n' END

FROM SampleData a;