Update TD_REPORTS.dbo.Flynns_CalcPricing
SET FCMP =
CASE
WHEN CalcIt.Cman = 'Michelin' then ((UsableCost + 44) * CalcIt.Multiplier) -- Add All Mans and some Lines (Lines before just Mans)
WHEN CalcIt.UsableCost > 0 and CalcIt.UsableCost <= 40.99 then ((UsableCost + 32) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 41 and CalcIt.UsableCost <= 45.99 then ((UsableCost + 34) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 46 and CalcIt.UsableCost <= 50.99 then ((UsableCost + 36) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 51 and CalcIt.UsableCost <= 55.99 then ((UsableCost + 38) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 56 and CalcIt.UsableCost <= 60.99 then ((UsableCost + 40) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 61 and CalcIt.UsableCost <= 65.99 then ((UsableCost + 42) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 66 and CalcIt.UsableCost <= 70.99 then ((UsableCost + 44) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 71 and CalcIt.UsableCost <= 75.99 then ((UsableCost + 45) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 76 and CalcIt.UsableCost <= 80.99 then ((UsableCost + 46) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 81 and CalcIt.UsableCost <= 85.99 then ((UsableCost + 47) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 86 and CalcIt.UsableCost <= 90.99 then ((UsableCost + 48) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 91 and CalcIt.UsableCost <= 95.99 then ((UsableCost + 49) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 96 and CalcIt.UsableCost <= 100.99 then ((UsableCost + 50) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 101 and CalcIt.UsableCost <= 109.99 then ((UsableCost + 51) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 110 and CalcIt.UsableCost <= 119.99 then ((UsableCost + 52) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 120 and CalcIt.UsableCost <= 129.99 then ((UsableCost + 53) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 130 and CalcIt.UsableCost <= 139.99 then ((UsableCost + 54) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 140 and CalcIt.UsableCost <= 149.99 then ((UsableCost + 55) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 150 and CalcIt.UsableCost <= 159.99 then ((UsableCost + 56) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 160 and CalcIt.UsableCost <= 169.99 then ((UsableCost + 57) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 170 and CalcIt.UsableCost <= 179.99 then ((UsableCost + 58) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 180 then ((UsableCost + 59) * CalcIt.Multiplier) -- Truncate and add .98
END
From
(
SELECT
pr.USku,
s.Manufacturer as Cman,
100.00 as UsableCost,
2 as multiplier
FROM
TD_REPORTS.dbo.Flynns_CalcPricing pr (nolock)
inner join
TD_SOURCE.dbo.SkuMaster s (nolock)
ON pr.USku = s.SkuCode
)CalcIt
I am doing some testing with some constants for usable cost and multiplier. The problem is the first 'when' in the case is not executing to use this formula when manufacturer is 'Michelin'. I pulled out just the select statement from this code and the manufacturer column is in there, so I am not sure what is not correct.
Also if I need to update the next field after FCMD as well, what would the syntax be after all the code for updating FCMP? I am sure it is another SET statement just not sure of the syntax. I added:
SET LowerAverage = 1 on the next line after the CalcIt and I get an error.
with a comma between each column/value pair (even if the value is a large case statement like yours). btw surely with a little bit of math you can get from the cost ranges to what you add to usablecost without all those cases.
There are couple of ways to do the update. One is to use a T-SQL construct usually called "UPDATE..FROM" and is described here. In particular, look at the section labeled Best Practices where they describe how the update can be non-deterministic if more than one row is returned from the from clause or a given row in the target table to be updated. This approach is NON-ANSI, but is one that I use very often. If you were to do that, your query would be something like this:
UPDATE pr SET
FCMP = CASE WHEN Cman = 'Michelin'
THEN ( ( UsableCost + 44 ) * Multiplier ) -- Add All Mans and some Lines (Lines before just Mans)
WHEN UsableCost > 0
AND UsableCost <= 40.99
THEN ( ( UsableCost + 32 ) * Multiplier )
WHEN UsableCost >= 41
AND UsableCost <= 45.99
THEN ( ( UsableCost + 34 ) * Multiplier )
WHEN UsableCost >= 46
AND UsableCost <= 50.99
THEN ( ( UsableCost + 36 ) * Multiplier )
WHEN UsableCost >= 51
AND UsableCost <= 55.99
THEN ( ( UsableCost + 38 ) * Multiplier )
WHEN UsableCost >= 56
AND UsableCost <= 60.99
THEN ( ( UsableCost + 40 ) * Multiplier )
WHEN UsableCost >= 61
AND UsableCost <= 65.99
THEN ( ( UsableCost + 42 ) * Multiplier )
WHEN UsableCost >= 66
AND UsableCost <= 70.99
THEN ( ( UsableCost + 44 ) * Multiplier )
WHEN UsableCost >= 71
AND UsableCost <= 75.99
THEN ( ( UsableCost + 45 ) * Multiplier )
WHEN UsableCost >= 76
AND UsableCost <= 80.99
THEN ( ( UsableCost + 46 ) * Multiplier )
WHEN UsableCost >= 81
AND UsableCost <= 85.99
THEN ( ( UsableCost + 47 ) * Multiplier )
WHEN UsableCost >= 86
AND UsableCost <= 90.99
THEN ( ( UsableCost + 48 ) * Multiplier )
WHEN UsableCost >= 91
AND UsableCost <= 95.99
THEN ( ( UsableCost + 49 ) * Multiplier )
WHEN UsableCost >= 96
AND UsableCost <= 100.99
THEN ( ( UsableCost + 50 ) * Multiplier )
WHEN UsableCost >= 101
AND UsableCost <= 109.99
THEN ( ( UsableCost + 51 ) * Multiplier )
WHEN UsableCost >= 110
AND UsableCost <= 119.99
THEN ( ( UsableCost + 52 ) * Multiplier )
WHEN UsableCost >= 120
AND UsableCost <= 129.99
THEN ( ( UsableCost + 53 ) * Multiplier )
WHEN UsableCost >= 130
AND UsableCost <= 139.99
THEN ( ( UsableCost + 54 ) * Multiplier )
WHEN UsableCost >= 140
AND UsableCost <= 149.99
THEN ( ( UsableCost + 55 ) * Multiplier )
WHEN UsableCost >= 150
AND UsableCost <= 159.99
THEN ( ( UsableCost + 56 ) * Multiplier )
WHEN UsableCost >= 160
AND UsableCost <= 169.99
THEN ( ( UsableCost + 57 ) * Multiplier )
WHEN UsableCost >= 170
AND UsableCost <= 179.99
THEN ( ( UsableCost + 58 ) * Multiplier )
WHEN UsableCost >= 180
THEN ( ( UsableCost + 59 ) * Multiplier ) -- Truncate and add .98
END
FROM
TD_REPORTS.dbo.Flynns_CalcPricing pr
INNER JOIN TD_SOURCE.dbo.SkuMaster s
ON pr.USku = s.SkuCode
You can run a SELECT instead of the update to see the current values and the values that will be used to update the table, something like this:
SELECT *,
-- your long case expression here
FROM
TD_REPORTS.dbo.Flynns_CalcPricing pr
INNER JOIN TD_SOURCE.dbo.SkuMaster s
ON pr.USku = s.SkuCode
A second approach which also would work in your case is to use a CTE.
;WITH cte AS
(
SELECT FCMP AS TargetColumn,
CASE WHEN Cman = 'Michelin'
-- rest of your long case expression here
THEN ( ( UsableCost + 59 ) * Multiplier ) -- Truncate and add .98
END AS NewValue
FROM
TD_REPORTS.dbo.Flynns_CalcPricing pr
INNER JOIN TD_SOURCE.dbo.SkuMaster s
ON pr.USku = s.SkuCode
)
UPDATE cte SET TargetColumn = NewValue;
What if these other fields that will be updated will also need a different complicated Case statement using different tables than FCMP is using to get the values needed? Won't this from clause now apply to those updated fields?