Set Expression for Sum

vikasgupta — Mon, 03 Dec 2012 08:59:39 GMT

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

Re: Set Expression for Sum

Gysbert_Wassenaar — Mon, 03 Dec 2012 09:24:32 GMT

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

Mon, 03 Dec 2012 09:45:34 GMT

Try this

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

Niranjan.