SUM Over Partition

Bone_12 361 Reputation points


I have the below SUM that almost gives me what I need, but not fully and wondered if anyone can help please.

sum(c.comp_amount) over (partition by a.comp_number , c.start_date, [TD_numeric] ) * ([TD_numeric]/100) as comp_value

The problem I have is the variable [TD_numeric] having '0' values so when looking to * [TD_numeric] where it has a 0, it's obviously not going to return any values. However, I need it to essentially include an else, so where [TD_numeric] is '0' then bring back this calculation:

sum(c.comp_amount) over (partition by a.comp_number , c.start_date)

Any idea if this can be done please?

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,820 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 95,076 Reputation points

    Your description can be implemented in this manner:

    case TD_numeric when 0 then ... else ... end as comp_value

    Replace '...' with corresponding sums.

    0 comments No comments

0 additional answers

Sort by: Most helpful