[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Update the Price field and format digits after decimal point

Posted on 2011-09-23
4
Medium Priority
?
421 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 2000 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 29

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

649 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