Case When with Over Partition

Bone_12 361 Reputation points


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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,812 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 94,921 Reputation points

    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?).

    0 comments No comments

  2. MelissaMa-MSFT 24,136 Reputation points

    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

    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.

    0 comments No comments