• Status: Solved
• Priority: Medium
• Security: Public
• Views: 227
• Last Modified:

# TSQL Help

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
cheryl9063
Asked:
• 11
• 6
• 4
• +1
2 Solutions

Commented:
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

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

uph_Grade2      uph_Grade2
1925      5779
435      1309
0

Commented:
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

Author Commented:
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

Commented:
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

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

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

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

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

Author Commented:
The number ALWAYS has to end in 9
0

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

Chief Technology OfficerCommented:
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

Chief Technology OfficerCommented:
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;
SET @uph_grade2 = 433;
SELECT FLOOR(@uph_Grade2 * 3.0 / 10) * 10 + 9;
0

Author Commented:
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

Author Commented:
Arithmetic overflow error converting nvarchar to data type numeric
0

Author Commented:
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

Commented:
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

If (@uph_Grade2-35)%10 = 0
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

Author Commented:
Cant I do that in my case select? Here is the chunck of code
,uph_Grade1
,uph_Grade2

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

ELSE
null
END as uph_Grade2

,uph_Grade3,uph_Grade4 ,uph_Grade5 ,uph_Grade6 ,uph_Grade7,uph_Grade8 ,uph_Grade9 ,uph_Grade10
0

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

,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
END as uph_Grade2
0

Chief Technology OfficerCommented:
AH, I got you. If 1309 ends in 09. Sorry I was missing that.
DECLARE @uph_grade2 INT;
SET @uph_grade2 = 430;
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

Commented:
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

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

Author Commented:
Thanks!
0

## Featured Post

• 11
• 6
• 4
• +1
Tackle projects and never again get stuck behind a technical roadblock.