Set Expression for Sum

[vikasgupta]

Hi Everyone !!!

Plz suggest me set expression for following  SQL condition :-

Select Year(InDt),Month(InDt),           /******InDt = Invoice Date******/

Sum(Case When IrRmCtg in ('C','D') Then IrRmiwt/5 else IrRmAwt end)  /***** IrRmCtg= Raw Material Category  And  IrRmiwt ,IrRmAwt = Invoice Wt***/

Group by Year(InDt),Month(InDt)

Order by Year(InDt),Month(InDt)

Explanation :- I wants to calculate SUM  if Invoice wt(IrRmiwt) where raw material category is in 'C' & 'D' then divide by 5 else invoice wt(IrRmAwt)

We used following expression :- Sum({ $ < RmCtg ={'C','D'}>}(IrRmIWt)/5)

but result is coming wrong  so plz correct if the above is wrong or suggest new expression .

Thanks

Vikas

[Gysbert_Wassenaar · Accepted solution]

I don't think set analysis can help you here. Try instead: sum(if(match(RmCtg,'C','D'),IrRmlWt/5,IrRmAwt))

[#3]

Try this

sum( if(RmCtg = 'C' or RmCtg = 'D',(IrRmIWt/5),IrRmIWt))

Niranjan.