SQLTeam.com | Weblogs | Forums

Using IF/WHEN to multiply SOLVED


#1

Hello

Van# MFG PART# PartDesc Cost Quantity CallsL24Months NoMoveValue
100 100 ABC KIT 1.25 1 0 ---

I was wondering if there is a solution for above....

What i am trying to do is; if "Calls24Months" is 0 (zero) then multiply Cost and Quantity and have it show within the NoMoreValue column, and if the CallsL24Months is not 0 NoMoveValue would be $0

The Calls24Months is a SUM field calculated by adding 24 separate columns (setup as SMALLINT in my table)

In the sample above i only shown one row; but i actually have over 4500 when i run the complete report

This is what i was trying to use:

[Case WHEN 'CallsL24Months' = '0' THEN round(SUM(iccost*icqty),2) ELSE '0' END as NoMoveValue]

Thanks


#2
Case WHEN CallsL24Months = '0' 
     THEN round(SUM(iccost*icqty),2) 
     ELSE 0 END AS NoMoveValue`

#3

Thanks - it seems to do the same thing - when there is a 0 in the CallsL24Months it puts a 0 in the NoMoveValue

Van# MFG PART# PartDesc Cost Quantity CallsL24Months NoMoveValue
V06 110 676ss ELEMENT KT 1.07 1 2 0
V06 110 93ss10 KIT 2.29 1 0 0


#4

Hmm, that's odd. What is the data type of CallsL24Months?


#5

blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } Hello
It is a sum of 24 different cells ... I was summing the cells and the doing the formula , once I put the sum of the 24 cells in the formula it works fine.

Thanks