# Update and data manipulation of a number field

I have a "Price" field that contains values like : 50.22, 8.78, 68.19, etc.

I want the last 2 digits after the decimal point to be rounded like this :

If :  .01 to .29  =  .29

If :  .30 to .49  =  .49

If :  .50 to .99  =  .95

So if Price is "29.14", it would be "29.29", . . . "18.78" would be "18.99", etc.

UPDATE Nop_ProductVariant
SET Price =  ?

Also, I want to raise all Prices by 18%, like :

UPDATE Nop_ProductVariant
SET Price = round(Price * 1.18, 2)

Can I combine these 2 queries at once? Or should I run 2 separate queries?  Thanks
LVL 16
###### Who is Participating?

x

Software Architect in .NetCommented:
Why don't you use a function:

CREATE FUNCTION updatePrice
(
@price numeric(8,2)
)
RETURNS numeric(8,2)
AS
BEGIN
SET @price = @price * 1.18
DECLARE @rounded INT
SET @rounded = CAST(@price AS INT)
DECLARE @decPart numeric(8,2)
SET @decPart = @price - @rounded

IF @decPart < 0.29
set @price = @rounded + 0.29
ELSE IF @decPart < 0.49
SET @price = @rounded + 0.49
ELSE
SET @price = @rounded + 0.95

RETURN @price
END

So you can do:
UPDATE Nop_ProductVariant SET Price = dbo.updatePrice(Price) [WHERE...]

Hope that helps.
0

Senior .Net Consultant & Technical AnalistCommented:
You can do this in 1 big Update script. and 3 different scripts
``````3 scripts :
UPDATE Nop_ProductVariant SET Price  = round(Price, 0, 1) + 0.29 where price % 1 < 0.29
UPDATE Nop_ProductVariant SET Price  = round(Price, 0, 1) + 0.49 where price % 1 < 0.49 and price % 1> 0.29
UPDATE Nop_ProductVariant SET Price  = round(Price, 0, 1) + 0.95 where price % 1 >0.49

1 script
UPDATE Nop_ProductVariant
SET Price = round(Price, 0, 1) +
Case when  price % 1 < 0.29 then 0.29
when price % 1 < 0.49 and price % 1> 0.29 then 0.49
else 0.95
End
``````
0

Commented:
If you want first increment by 18% and after adjust decimals numbers:
update Nop_ProductVariant
set Price = floor(round(Price * 1.18, 2)) +  case when round(Price * 1.18, 2) % 1 < 0.29 then 0.29
when round(Price * 1.18, 2) % 1 between .30 and 0.49 then 0.49
else 0.95
end

If you want first adjust decimals numbers and after increment by 18%:
update Nop_ProductVariant
set Price = round(floor(Price) + case when Price % 1 < 0.29 then 0.29
when Price % 1 between .30 and 0.49 then 0.49
else 0.95
end * 1.18, 2)

0

Commented:
are these supposed to be internal/external prices?

do any tax calculations get performed... which will affect the price point chosen?

ie move to the price point (.29 etc) and then apply tax
or apply tax then move to a price point?

you may find that keeping your current internal price column and having another computed column
is the better method /approach...

is each price uplift supposed to start from the endprice or the internal price point.

does the price point rounding depend on the basic unit cost of the price ... ie less than 10
round to nearest .09   10-50  nearest .29   50 -100 nearest .99  10-500 nearest 5.00 etc

a table specify the rounding units maybe a better approach
0

Author Commented:
thanks all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.