Hi @Bone_12 ，
Welcome to Microsoft Q&A!
Could we know the purpose of adding "over (partition by a.cust_ref, a.topkey)"?
You could just remove the over part like below:
case when a.top_key in ('85','99','129','142') then 'Y' else 'N' end as 'New_and_Existing'
If above is not working, you could consider to add aggregate functions like SUM(), COUNT(), AVG() before the over part like below:
case when a.top_key in ('85','99','129','142') and count(*) over(partition by a.cust_ref, a.topkey)>0
then 'Y' else 'N' end as 'New_and_Existing'
If above is still not working, we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data and your complete query. We also need to see the expected result of the sample.
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.