Solved

# TSQL Help

Posted on 2011-10-13
215 Views
I need the field uph_Grade2  to round from 1925 to 5779.. Its rounding to 5775 when I use the formula below.. Basically the number always has to end in 9 so another example is 435 changes to 1305 but I need it to be 1299.. How can I make this happen?

ceiling((cast(uph_Grade2 as int) * 3.0) / 5) * 5
0
Question by:cheryl9063

LVL 10

Expert Comment

first of all the /5 *5 is wrong. It's null cause it's divided and re-multiplied for the same. THen how do you think that a round number will grow to 5779 ceiling that if it's already an integer?

The right thing to do is ((cast uph_grade2 as int)*3)+4
0

LVL 1

Author Comment

That works for the 1925 to 5779 but it does not work for 435 to 1299.. It need to round to the closest 9

1925      5779
435      1309
0

LVL 25

Expert Comment

How you are deciding when to lower the value or when to increase the value. Why 5775 goes to 5779 and not 5769, and why 1305 goes to 1299 and not 1309?
0

LVL 1

Author Comment

3 times 1925 is 5775 but it needs be rounded to the nearest 9 so 5779  BUT 435 3 times and to the NEAREST 9 is 1299
0

LVL 25

Expert Comment

How nearest can be 1299 but not 1309 where you have to just add 4 in 1305 but you have to subtract 6 to get 1299.
0

LVL 59

Expert Comment

Using division by 10, 1925 works with the below but 435 ends up at 1309.
``````DECLARE @uph_grade2 INT;
SELECT CEILING(CAST(@uph_Grade2 as int) * 3.0 / 10) * 10 - 1;
``````

Maybe divide by 10 sooner would work... will test.
0

LVL 1

Author Comment

Its basically another Case statement... IF the number ends in 09 subtract 10.. How do I write that?
0

LVL 59

Expert Comment

I agree with http:#36964711, the round up and down is not consistent between the two results you reported.
0

LVL 1

Author Comment

The number ALWAYS has to end in 9
0

LVL 1

Author Comment

number times 3 then make it end in 9.. If it ends in 09 then subtract 10..
0

LVL 59

Expert Comment

Okay, so:

1925 x 3 = 5775
435 x 3 = 1305

Neither of those numbers ends in 09, so it is confusing why 10 is subtracted there?
0

LVL 59

Expert Comment

For example, this formula may be a little more clear on what it is doing with division as dividing by 10 and removing decimal places with FLOOR() then multiplying by 10 gets you the answer with 0 as ending digit always, so that +9 always results in 9 at the end; however, 435 == 1309 and 433 == 1299.

``````DECLARE @uph_grade2 INT;
SELECT FLOOR(@uph_Grade2 * 3.0 / 10) * 10 + 9;
``````
0

LVL 1

Author Comment

The Business rule is it needs to be rounded to the nearest 9.. Has to end in 9.. 5775 becomes 5779 which is fine.. 1305 would become 1309.. NOt good.. IF it ends in 09 then 10 should be subtracted from 1309 and the number becomes 1299.. How?
0

LVL 1

Author Comment

Arithmetic overflow error converting nvarchar to data type numeric
0

LVL 1

Author Comment

so once we get the error out then I just have to find the ones that end in 09 and subtract 10 after correct?
0

LVL 25

Assisted Solution

This is only possible if the number has 35 as its last 2 digits. So you can create a function with a integer parameter where you will pass the value, and return an integer value which will be your outcome.

Inside the body you can write: Assumption: @uph_Grade2 is input parameter

then
begin
Return SELECT CEILING(@uph_Grade2 * 3.0 / 10) * 10 - 1;
end
else
begin
Return SELECT Floor(@uph_Grade2 * 3.0 / 10) * 10 - 1;
end
0

LVL 1

Author Comment

Cant I do that in my case select? Here is the chunck of code
``````,uph_Grade1

,CASE WHEN isnumeric(uph_Grade1) = 1 THEN
FLOOR(cast(uph_Grade2 AS int) * 3.0 / 10) * 10 + 9

ELSE
null

``````
0

LVL 1

Author Comment

OH soo close(see code below).. Its now giving me 5769 instead of 5779 but I'm getting 1299
``````,uph_Grade1

,CASE WHEN (cast(uph_Grade2 as int)-35)*10 =0 THEN
CEILING(cast(uph_Grade2 as int) * 3.0 / 10) * 10 - 1

ELSE
Floor(cast(uph_Grade2 as int) * 3.0 / 10) * 10 - 1
``````
0

LVL 59

Accepted Solution

AH, I got you. If 1309 ends in 09. Sorry I was missing that.
SELECT (FLOOR(@uph_Grade2 * 3.0 / 10) - CASE FLOOR(@uph_Grade2 * 3.0 / 10)%10 WHEN 0 THEN 1 ELSE 0 END) * 10 + 9;
0

LVL 25

Expert Comment

Check my answer again. What you are doing is after subtracting 35, you are multiplying with 10 , whereas I am saying to do modulo 10.
0

LVL 59

Expert Comment

I agree. I thought maybe I missed something on that as it didn't match what you posted originally.
0

LVL 1

Author Closing Comment

Thanks!
0

## Featured Post

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server.Â RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at: Â  htâ€¦
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoftâ€™s cloud platform, you know that you will need to create a corporate email signature for your Office 365â€¦
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax â€” just include tâ€¦