Solved

Update and data manipulation of a number field

Posted on 2011-09-05
5
307 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:MikeMCSD
5 Comments
 
LVL 10

Expert Comment

by:John Claes
Comment Utility
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

Open in new window

0
 
LVL 25

Accepted Solution

by:
Luis Pérez earned 167 total points
Comment Utility
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
 
LVL 11

Assisted Solution

by:dougaug
dougaug earned 167 total points
Comment Utility
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 166 total points
Comment Utility
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
 
LVL 16

Author Comment

by:MikeMCSD
Comment Utility
thanks all
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

772 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

13 Experts available now in Live!

Get 1:1 Help Now