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 ( TransCount > 1 and CancelCount >=1 )

or ( TransCount = 1 and CancelCount = 0 )