Rounding price increase to the nearest

[JeffMachine]

We are increasing our prices by 4% (price*1.04) and I need to write a query to show the sales dept. what the new prices would look like and I need a little help with the syntax to calculate the new rounded prices.

We would like to round these new prices based on the table below:

Price Round To
0-19.99 No Round
20-99.99 Round up to nearest .25
100-499.99 Round up to nearest .50
500-999.99 Round up to nearest 1.00
1000-9999.99 Round up to nearest 10.00
10000+ Round up to nearest 100.00

examples:
if price=10 then newprice=10.40 (10*1.04)
if price=25.50 then newprice=26.75 (25.5*1.04=26.52)
if price=752.25 then newprice=783.00 (752.25*1.04=782.34)
if price=34,093 then newprice=35,500 (34,093*1.04=35,456.72)

thanks

 

[gmmastros]

Take a look at this sample code. It appears to do what you want.

Declare @Temp Table(Price Decimal(20,2))

Insert Into @Temp Values(0.01)
Insert Into @Temp Values(19.99)
Insert Into @Temp Values(20.00)
Insert Into @Temp Values(50.00)
Insert Into @Temp Values(99.99)
Insert Into @Temp Values(100.00)
Insert Into @Temp Values(200.00)
Insert Into @Temp Values(499.99)
Insert Into @Temp Values(599.99)
Insert Into @Temp Values(1599.99)
Insert Into @Temp Values(100000.00)
Insert Into @Temp Values(10.00)
Insert Into @Temp Values(25.50)
Insert Into @Temp Values(752.25)
Insert Into @Temp Values(34093.00)

Select Price, 
       Price * 1.04 As UnroundedPrice,
       Case When Price Between       0 And   19.99 Then Round(Price * 1.04,2)
            When Price Between   20.00 And   99.99 Then Ceiling(4 * Price * 1.04) / 4
            When Price Between  100.00 And  499.99 Then Ceiling(2 * Price * 1.04) / 2
            When Price Between  500.00 And  999.99 Then Ceiling(Price * 1.04)
            When Price Between 1000.00 And 9999.99 Then Ceiling(0.1 * Price * 1.04) * 10
            Else Ceiling(0.01 * Price * 1.04) * 100
            End As NewPrice
From   @Temp

Copy/Paste this to a query window to see how it works. Of course, for your query, you'll need to modify the column and table name(s).

Notice that the Ceiling function does the bulk of the work here, not the round functions.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should mention that this code calculates the range for rounding before actually rounding. Specifically, if the old price falls within the range of values for a rounding method, but the new price puts it in to another range, it will still round based on the old price, not the new one.

Ex:
100-499.99 Round up to nearest .50
500-999.99 Round up to nearest 1.00

If the old price is 495.49, the new price would be 515.3096. The OLD range says to round up to .5, so the code I posted would return 515.50. But... the new range puts it in the Round up to the nearest 1.00.

This would be easy enough to accommodate though.

Declare @Temp Table(Price Decimal(20,2))

Insert Into @Temp Values(0.01)
Insert Into @Temp Values(19.99)
Insert Into @Temp Values(20.00)
Insert Into @Temp Values(50.00)
Insert Into @Temp Values(99.99)
Insert Into @Temp Values(100.00)
Insert Into @Temp Values(200.00)
Insert Into @Temp Values(499.99)
Insert Into @Temp Values(599.99)
Insert Into @Temp Values(1599.99)
Insert Into @Temp Values(100000.00)
Insert Into @Temp Values(10.00)
Insert Into @Temp Values(25.50)
Insert Into @Temp Values(752.25)
Insert Into @Temp Values(34093.00)

Select Price, 
       Price * 1.04 As UnroundedPrice,
       Case When Price [!]* 1.04 [/!]Between       0 And   19.99 Then Round(Price ,2)
            When Price [!]* 1.04 [/!] Between   20.00 And   99.99 Then Ceiling(4 * Price * 1.04) / 4
            When Price [!]* 1.04 [/!] Between  100.00 And  499.99 Then Ceiling(2 * Price * 1.04) / 2
            When Price [!]* 1.04 [/!] Between  500.00 And  999.99 Then Ceiling(Price * 1.04)
            When Price [!]* 1.04 [/!] Between 1000.00 And 9999.99 Then Ceiling(0.1 * Price * 1.04) * 10
            Else Ceiling(0.01 * Price * 1.04) * 100
            End As NewPrice
From   @Temp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom