Rounding currency

[brialex]

Does anyone know of a way to round currency to the nearest .50 or .95? I need to calculate the retail cost of an item by taking the price + $3.00 * 1.43 and round it to the nearest .50 or .95. We always want to round up for example 47.44 would be $7.50 and $7.51 would be $7.95.

 

[AlexCuse]

Something like this?

[COLOR=green]--set up sample data
[/color][COLOR=blue]declare[/color] @t [COLOR=blue]table[/color](i [COLOR=blue]decimal[/color](10,2))


[COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] 0
union all [COLOR=blue]select[/color] 1.41
union all [COLOR=blue]select[/color] 1.52
union all [COLOR=blue]select[/color] 1.97


[COLOR=green]--display results
[/color][COLOR=blue]select[/color] i, [COLOR=blue]case[/color] [COLOR=blue]when[/color] (i - [COLOR=#FF00FF]floor[/color](i)) < .5 [COLOR=blue]then[/color]
		[COLOR=#FF00FF]floor[/color](i) + .5
	    [COLOR=blue]when[/color] (i - [COLOR=#FF00FF]floor[/color](i)) < .95 [COLOR=blue]then[/color]
		[COLOR=#FF00FF]floor[/color](i) + .95
[COLOR=green]--want to round up when > .95???
[/color]	    [COLOR=blue]else[/color]
		[COLOR=#FF00FF]floor[/color](i) + 1
	    [COLOR=blue]end[/color] [COLOR=blue]as[/color] RoundValue
[COLOR=blue]from[/color] @t

I suggest you read up on the FLOOR and CASE functions in BOL.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

You'll probably want to use the ceiling function to do this. Something like this...

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Price [COLOR=blue]Decimal[/color](10,4))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](7.00)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](7.10)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](7.20)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](7.30)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](7.40)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](7.50)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](7.60)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](7.70)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](7.80)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](7.90)

[COLOR=blue]Select[/color] *, 
       [COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]Ceiling[/color]((Price + $3.00 * 1.43) * 2) / 2 = [COLOR=#FF00FF]Floor[/color]([COLOR=#FF00FF]Ceiling[/color]((Price + $3.00 * 1.43)* 2) / 2)
            [COLOR=blue]Then[/color] [COLOR=#FF00FF]Ceiling[/color]((Price + $3.00 * 1.43) * 2) / 2 - 0.05
            [COLOR=blue]Else[/color] [COLOR=#FF00FF]Ceiling[/color]((Price + $3.00 * 1.43) * 2) / 2
            [COLOR=blue]End[/color]
[COLOR=blue]From[/color]   @Temp

-George

"the screen with the little boxes in the window." - Moron

 

[brialex]

This is what I ended up doing and it works also:

SELECT CASE WHEN right(sug_retail,2) >50 and right(sug_retail,2)<95 then replace(sug_retail,right(sug_retail,2),95) else replace(sug_retail,right(sug_retail,2),50) end as NewPrice


or for the full version:

case when right(cast(round(((itw.lst_cost) +3) * 1.43,2) as decimal(8,2)),2)> 50 and
right(cast(round(((itw.lst_cost) +3) * 1.43,2) as decimal(8,2)),2)< 95 then
replace(cast(round(((itw.lst_cost) +3) * 1.43,2) as decimal(8,2)),right(cast(round(((itw.lst_cost) +3) * 1.43,2) as decimal(8,2)),2),95) else
replace(cast(round(((itw.lst_cost) +3) * 1.43,2) as decimal(8,2)),right(cast(round(((itw.lst_cost) +3) * 1.43,2) as decimal(8,2)),2),50)
end as sug_retail