SQLTeam.com | Weblogs | Forums

Not Getting Correct Results


#1

Here is my code:

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.


#2

possibly stupid question: How do you know the first 'when' is not executing?


#3

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.


#4

syntax is:

set col1 = newval1,
col2 = newval2,

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.


#5

So this would then work adding a comma after CalcIt and then the following 4 other fields to update:

)CalcIt,

AveRetPrice = 1,
LowestPrice = 2,
MaxGP = 3,
NewStore = 4

I am getting an error using this.


#6

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;

#7

other sets come after the case statement, not after the FROM clause


#8

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?