Solved

Update the Price field and format digits after decimal point

Posted on 2011-09-23
4
386 Views
Last Modified: 2012-06-27
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
Comment
Question by:MikeMCSD
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
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

Open in new window

0
 
LVL 28

Expert Comment

by:sammySeltzer
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

by:MikeMCSD
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

by:MikeMCSD
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now