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?