metropia
asked on
case statement not following desire logic correctly
I am trying to input the pseudo-code below into a sql case statement
but i think is not working because the 3rd scenario is not applying when appropriate.
pseudo code:
A. If “Requested Price” have value then calculate “Requested Price” minus “Total Cost” divided “Requested Price” equal “Expected Margin”
B. If “Suggested Price” have value then calculate “Suggested Price” minus “Total Cost” divided “Suggested Price” equal “Expected Margin”
C. If “Requested Price” and “Suggested Price” both have values then calculate “Suggested Price” minus “Total Cost” divided “Suggested Price” equal “Expected Margin”
but i think is not working because the 3rd scenario is not applying when appropriate.
pseudo code:
A. If “Requested Price” have value then calculate “Requested Price” minus “Total Cost” divided “Requested Price” equal “Expected Margin”
B. If “Suggested Price” have value then calculate “Suggested Price” minus “Total Cost” divided “Suggested Price” equal “Expected Margin”
C. If “Requested Price” and “Suggested Price” both have values then calculate “Suggested Price” minus “Total Cost” divided “Suggested Price” equal “Expected Margin”
, ExpectedMargin = CASE
WHEN [sql].[SalesQuotePriceRequested] <> 0
THEN (
[sql].[SalesQuotePriceRequested]
- (
ROUND([sql].UnitCostMaterial,2)
+ROUND([sql].[TotalLabor_OHCost], 2)
+ROUND([sql].[UnitCostFreight], 2)
+[sql].[UnitCostSGA]
+ROUND([sql].[UnitCostDiscount], 2)
+[sql].[AdditionalCost]
)
) / [sql].[SalesQuotePriceRequested]
WHEN [sql].[SuggestedPrice] <> 0
THEN (
[sql].[SuggestedPrice]
- (
ROUND([sql].UnitCostMaterial,2)
+ROUND([sql].[TotalLabor_OHCost], 2)
+ROUND([sql].[UnitCostFreight], 2)
+[sql].[UnitCostSGA]
+ROUND([sql].[UnitCostDiscount], 2)
+[sql].[AdditionalCost]
)
) / [sql].[SuggestedPrice]
WHEN ([sql].[SalesQuotePriceRequested] <> 0 AND [sql].[SuggestedPrice] <> 0)
THEN (
[sql].[SuggestedPrice]
- (
ROUND([sql].UnitCostMaterial,2)
+ROUND([sql].[TotalLabor_OHCost], 2)
+ROUND([sql].[UnitCostFreight], 2)
+[sql].[UnitCostSGA]
+ROUND([sql].[UnitCostDiscount], 2)
+[sql].[AdditionalCost]
)
) / [sql].[SuggestedPrice]
END
ASKER CERTIFIED 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.
ASKER