• 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:
cheryl9063
  • 11
  • 6
  • 4
  • +1
2 Solutions
 
ienaxxxCommented:
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
 
cheryl9063Author 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
 
TempDBACommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
cheryl9063Author 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
 
TempDBACommented:
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
 
Kevin CrossChief 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;

Open in new window


Maybe divide by 10 sooner would work... will test.
0
 
cheryl9063Author Commented:
Its basically another Case statement... IF the number ends in 09 subtract 10.. How do I write that?
0
 
Kevin CrossChief Technology OfficerCommented:
I agree with http:#36964711, the round up and down is not consistent between the two results you reported.
0
 
cheryl9063Author Commented:
The number ALWAYS has to end in 9
0
 
cheryl9063Author Commented:
number times 3 then make it end in 9.. If it ends in 09 then subtract 10..
0
 
Kevin CrossChief 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
 
Kevin CrossChief 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;

Open in new window

0
 
cheryl9063Author 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
 
cheryl9063Author Commented:
Arithmetic overflow error converting nvarchar to data type numeric
0
 
cheryl9063Author 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
 
TempDBACommented:
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
 
cheryl9063Author 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

Open in new window

0
 
cheryl9063Author 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

Open in new window

0
 
Kevin CrossChief 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
 
TempDBACommented:
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
 
Kevin CrossChief Technology OfficerCommented:
I agree. I thought maybe I missed something on that as it didn't match what you posted originally.
0
 
cheryl9063Author Commented:
Thanks!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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