Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

convert inline IF to CASE statement

can someone help me convert the following formula:

IIf(CDec(Sum(IIf(Fields!OrderLineStatus.Value = "OPEN", Fields!OrderBalanceQty.Value, CDec(0)))) <> 0, (CDec(Sum(IIf(Fields!OrderLineStatus.Value = "OPEN", (Fields!OrderBalanceQty.Value * Fields!OrderPrice.Value), CDec(0)))) / CDec(Sum(IIf(Fields!OrderLineStatus.Value = "OPEN", Fields!OrderBalanceQty.Value, CDec(0))))), 0)

Open in new window


to a SQL CASE statement?

The name of the fields are the same.

Thank you!
0
metropia
Asked:
metropia
  • 3
  • 2
  • 2
  • +2
4 Solutions
 
LIONKINGCommented:
I could help you, but I kind of don't understand the formula.
I see you have a sum so I guess you're grouping somehow, but I see that you have orderBalanceQuantity * OrderPrice / OrderBalanceQuantity

Wouldn't that give you OrderPrice?
Sorry if this is a dumb question, I just want to understand what you need.
0
 
awking00Commented:
Can you describe what you're trying to accomplish in plain language? Rather than trying to convert, it is often easier to just write the syntax if we know the objective.
0
 
metropiaAuthor Commented:
the formula was part of a report, i want to calculate the weighted price. is not dumb question, i am also confused by some of the formulas i am finding in some of the reports, and no one to ask.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ryan McCauleyCommented:
If I'm reading your formula correctly, you're looking for this:

SUM(CASE WHEN Fields!OrderLineStatus.Value <> "OPEN" THEN 0
		 WHEN Fields!OrderBalanceQty.Value = 0 THEN 0
		 ELSE Fields!OrderBalanceQty.Value *
			  Fields!OrderPrice.Value /
			  Fields!OrderBalanceQty.Value
	END)

Open in new window


Effectively, you're returning zero if either the LineStatus <> "OPEN" or if the BalanceQuantity=0. Assuming both of those are false (status = OPEN and Quantity <> 0), it performs the calculation.
0
 
Chris LuttrellSenior Database ArchitectCommented:
He is taking an IF statement from SSRS and trying to accomplish the same outcome in a SQL statement.  Given the Fields names are the same as the Columns on the table this code should work in SQL:
SUM(CASE WHEN OrderLineStatus <> "OPEN" THEN 0
		 WHEN OrderBalanceQty = 0 THEN 0
		 ELSE OrderBalanceQty *
			  OrderPrice /
			  OrderBalanceQty 
	END)

Open in new window

0
 
Ryan McCauleyCommented:
I figured, but I wasn't sure if the field names actually matched, so I thought I'd let the OP swap those out themself - if they match exactly, then yours is what they'd actually use.
0
 
metropiaAuthor Commented:
question,


what would be the operator order on this part?


OrderBalanceQty *  OrderPrice / OrderBalanceQty


should it he like:

( OrderBalanceQty *  (OrderPrice / OrderBalanceQty ))

or

((OrderBalanceQty *  OrderPrice) / OrderBalanceQty )
0
 
LIONKINGCommented:
The order of multiplication and division operators is the same... It doesn't matter in what order you put them, the result will not vary.

For example:


OrderBalanceQty *  OrderPrice / OrderBalanceQty = ( OrderBalanceQty *  (OrderPrice / OrderBalanceQty )) = ((OrderBalanceQty *  OrderPrice) / OrderBalanceQty ) = OrderPrice

The only way the value would change is if the operation (multiplication) is done in the denominator, which doesn't seem likely in this case.
0
 
Ryan McCauleyCommented:
It would execute left to right (since order of operations on multiplication/division don't matter), but that won't affect the results at all. If you had addition in there, it would execute the multiplication first, using the standard order of operations.

The only thing you need to be concerned about for your calculation is the divisor - it needs to be non-zero or you'll get the error. As long as it's non-zero, the other values can be anything.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now