Solved

# Update the Price field and format digits after decimal point

Posted on 2011-09-23
386 Views
I have a Price field that has values like :
12.18, 45.78, 9.66, etc...
I want to Update the Price field and format digits after decimal point.

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

If :  .00 to .29  =  .29

If :  .30 to .49  =  .49

If :  .50 to .69  =  .69

If :  .70 to .99  =  .95

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

UPDATE Nop_ProductVariant
SET Price =

How can I do this? thanks
0
Question by:MikeMCSD
• 2

LVL 19

Accepted Solution

Bhavesh Shah earned 500 total points
ID: 36587783
Hi,

check out this.
It might helps.

- Bhavesh
``````declare @var numeric(30,2)
set @var = 15.78
select case when @var-floor(@var) >= 0.70 then floor(@var) + 0.95
else case when @var-floor(@var) >= 0.50 then floor(@var) + 0.69
else case when @var-floor(@var) >= 0.30 then floor(@var) + 0.49
else case when @var-floor(@var) >= 0 then floor(@var) + 0.29
end end end end as Price
``````
0

LVL 28

Expert Comment

ID: 36588087
You can also try this and see if it helps:

update Nop_ProductVariant
set price = case when price between 19.00 and 29.14 then LEFT(CONVERT(VARCHAR(20),price),2)+'.29' end)

If that works, then add more conditions like:

when price between 0.00 and 18.78 then LEFT(CONVERT(VARCHAR(20),price),2)+'.99' end)

0

LVL 16

Author Comment

ID: 36588410
thanks Brichsoft  .. that's perfect.

How do I use it in the Update statement ?

UPDATE Nop_ProductVariant
SET Price =

select case when @var-floor(@var) >= 0.70 then floor(@var) + 0.95
else case when @var-floor(@var) >= 0.50 then floor(@var) + 0.69
else case when @var-floor(@var) >= 0.30 then floor(@var) + 0.49
else case when @var-floor(@var) >= 0 then floor(@var) + 0.29
end end end end as Price
0

LVL 16

Author Comment

ID: 36590196
I'm not good with the CASE statement, but I think this worked :

UPDATE Nop_ProductVariant
SET Price =
CASE WHEN Price-floor(Price) >= 0.70 THEN floor(Price) + 0.95 ELSE
CASE WHEN Price-floor(Price) >= 0.50 THEN floor(Price) + 0.69 ELSE
CASE WHEN Price-floor(Price) >= 0.30 THEN floor(Price) + 0.49 ELSE
CASE WHEN Price-floor(Price) >= 0 THEN floor(Price) + 0.29
END END END END

0

## Featured Post

### Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.