Please try this.

select distinct TransactionNumber from

(

select TransactionNumber, CancelDueToCorrection, count(1) over( partition by TransactionNumber) as TransCount

, sum(case when CancelDueToCorrection = 'Y' then 1

else 0

end) over( partition by TransactionNumber) as CancelCount

from test

) A

where ( CancelCount >=1 and TransCount > 1 )

or ( CancelCount < 1 )