Case When with Over Partition

Question

Hi,

I am trying to so a CASE statement with an over partition (if it's possible), but struggling to get the correct syntax for it.

Is anyone able to please help me with this?

case when a.top_key in ('85','99','129','142') over (partition by a.cust_ref, a.topkey) then 'Y' else 'N' end as 'New_and_Existing'

Thanks in advance

Answer 1

Maybe like this:

case when exists (select * from MyTableA where top_key in ('85','99','129','142') and cust_ref = a.cust_ref and topkey = a.topkey) then...

Perhaps other constructs are more appropriate if you describe your intentions. (Do you really have columns named top_key and topkey?).

Answer 2

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.

Best regards
Melissa

---

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.