convert inline IF to CASE statement
can someone help me convert the following formula:
to a SQL CASE statement?
The name of the fields are the same.
Thank you!
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)to a SQL CASE statement?
The name of the fields are the same.
Thank you!
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.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
question,
what would be the operator order on this part?
OrderBalanceQty * OrderPrice / OrderBalanceQty
should it he like:
( OrderBalanceQty * (OrderPrice / OrderBalanceQty ))
or
((OrderBalanceQty * OrderPrice) / OrderBalanceQty )
what would be the operator order on this part?
OrderBalanceQty * OrderPrice / OrderBalanceQty
should it he like:
( OrderBalanceQty * (OrderPrice / OrderBalanceQty ))
or
((OrderBalanceQty * OrderPrice) / OrderBalanceQty )
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.