0

I have a task to transform (to round) decimals (with 2 decimal digits) using an sql cursor with the following rules

If the second decimal digit is:

a)0,1,2 then I must turn it to 0.

b)3,4,5,6,7 then I must turn it to 5.

c)8,9 then I must turn it to 0 (and add 1 in the first decimal digit).

The operation contains lots of records and values that need to be transformed using these rules. I am using a Microsoft SQL Server database.

Can you suggest me a smart sql that it can materialize these rules?

Thanx in advance!

John Maillet
  • 680
  • 8
  • 23
PanosPlat
  • 940
  • 1
  • 11
  • 29
  • possible duplicate of [TSQL round to half decimals](http://stackoverflow.com/questions/9831109/tsql-round-to-half-decimals) – dani herrera Jul 17 '12 at 21:39

2 Answers2

2
SELECT ROUND(<column>*2, 1)/2
EkoostikMartin
  • 6,831
  • 2
  • 33
  • 62
0

Your problem is a bit complicated, but you don't want to use a cursor.

update t
    set val = (case when floor(num*100)%10 in (0, 1, 2)
                    then (floor(num*100) - floor(num*100)%10)/100.0
                    when floor(num*100)%10 in (3, 4, 5, 6, 7)
                    then (floor(num*100) - floor(num*100)%10)/100.0 + 0.05
                    when floor(num*100)%10 in (8, 9)
                    then (floor(num*100) - floor(num*100)%10)/100.0 + 0.1 
               end)

You can see the values by putting the case in a select statement.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786