使用案例和类似的陈述没有重复的结果

I am trying to use case and like statement. When I run this below query it is duplicating results on some rows where it is y on particular columns. I am using SQL Server 2000.I am trying to use case and like statement. Whe