Solved

# case statement not following desire logic correctly

Posted on 2014-03-11
244 Views
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”

``````,	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].[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].[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].[SuggestedPrice]
END
``````
0
Question by:metropia

LVL 65

Accepted Solution

Jim Horn earned 275 total points
ID: 39921818
For starters, wayy too many {TAB} characters.

After that, I recommend a nesting that goes something like this, with your third block moved to the first, as in the current order both 1 and 2 will be correct before 3, which is causing unnecessary processing.

<air code>
``````CASE
WHEN Requested Price IS NOT NULL AND  “Suggested Price” IS NOT NULL
THEN Expected Margin = --  that calculation goes here
WHEN Requested Price IS NOT NULL
THEN Expected Margin = --  that calculation goes here
WHEN Suggested Price IS NOT NULL
THEN Expected Margin = --  that calculation goes here
END
``````
0

LVL 69

Assisted Solution

ScottPletcher earned 225 total points
ID: 39921819
You have to do the last WHEN first, otherwise it's never reached:

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)
)
0

Author Closing Comment

ID: 39921976
thank you experts!
0

## Featured Post

### Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…