SUM Over Partition

Question

Hi,

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?

Answer 1

Your description can be implemented in this manner:

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

Replace '...' with corresponding sums.