Share via

Case When Over Partition

Bone_12 361 Reputation points
2021-09-08T14:12:02.427+00:00

Hi,

I have a case statement that I would like to add an Over Partition by but can't quite get the syntax. Anyone able to advise please?

case when Co_table = '2' and Co_field = '1' then Co_Numeric end as 'Rec_Change'

I would like to over partition this by the variable 'Cust_id'

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. EchoLiu-MSFT 14,581 Reputation points
    2021-09-09T01:32:20.613+00:00

    Hi @Bone_12 ,

    Welcome to the microsoft tsql Q&A forum!

    Your question is related to tsql, so my colleague helped you add the tsql tag.

    Sorry, your description is not clear enough for me. I don't know what the specific problem you encountered is.Generally speaking, the over function is used together with aggregate functions, ranking functions, etc. Cannot be used alone in the CASE WHEN statement.

    We recommend that you provide the relevant tables and data (CREATE and INSERT statements), as well as the results you expect.

    Perhaps the following is what you want: 

    case when Co_table = '2' and Co_field = '1'   
then max(Co_Numeric) over(partition by Cust_id) end as 'Rec_Change'

    For the OVER function, please refer to:
    SELECT - OVER Clause (Transact-SQL)

    If you have any question, please feel free to let me know.

    Regards
    Echo

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments