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

sql 2014 query

Hi experts- can anyone with access to sql 2014 run this query and see if the syntax is available.. 2012 gives error:
http://sqlfiddle.com/#!6/b1698/49
0
25112
Asked:
25112
1 Solution
 
Wasim Akram ShaikCommented:
it says roundup.. i think there is a round function.. try this

select ROUND (111,40)
0
 
25112Author Commented:
yes, that just rounds to specified # of digits.. but roundup and rounddown does more (away or toward)?

select ROUNDUP (111,40)
select ROUNDDOWN (111,40)

http://technet.microsoft.com/en-us/library/ee634948.aspx
http://technet.microsoft.com/en-us/library/ee634963.aspx
http://technet.microsoft.com/en-us/library/ee634402.aspx
0
 
SurranoSystem EngineerCommented:
Try CEILING and FLOOR though they don't handle digits.
To round up to three digits, you may try e.g. CEILING (numeric_expression * 1000) / 1000
As "poor man's ceiling" you may use ROUND(numeric_expression + 0.4999999) with an appropriate number of nines. ROUND (numeric_expression + 0.5) will round one higher if you have an integer value to start with.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
25112Author Commented:
Surrano, with your example how would you convert
ROUNDUP(1.3,0.2)
with
ROUND or CEILING?
0
 
SurranoSystem EngineerCommented:
It depends what you mean by ROUNDUP(1.3, 0.2) since ROUNDUP (in SQL Server 2012) has an integer for second parameter: 2 = 2 digits to the right of the decimal separator, -2 = 2 digits to the left. i.e.
ROUNDUP (123.456, 2) = 123.46
ROUNDUP (123.456, -2) = 200

If you mean round up to two fractional digits (i.e. to 1.30 but it doesn't make sense in this example):

ceiling (1.3*100)/100

If you mean round up to the nearest integer multiple of 0.2 (i.e. to 1.4):

ceiling (1.3/0.2)*0.2

Otherwise, please provide more examples with the desired result so that your interpretation of ROUNDUP is unambiguous. E.g. what should be the result of the following calls?

ROUNDUP (1234.5678, 0.2)
ROUNDUP (1234.5678, 0.3)
ROUNDUP (-1234.5678, 0.2)
ROUNDUP (-1234.5678, 0.3)
ROUNDUP (1234.5678, -0.2)
ROUNDUP (1234.5678, -0.3)
ROUNDUP (-1234.5678, -0.2)
ROUNDUP (-1234.5678, -0.3)
0
 
25112Author Commented:
Surrano,
according to http://technet.microsoft.com/en-us/library/ee634948.aspx
ROUNDUP(PI(),4) = 3.1416
ROUNDUP(1.3,0.2) = 1.4
ROUNDUP(13.25,-1) =20


is ROUNDUP working in your instance?
0
 
PadawanDBAOperational DBACommented:
To briefly answer your question: roundup is not valid syntax in 2014.  The KB you posted a link to a DAX function, which if I am not mistaken, would be applicable to SSAS.  You would indeed need to get creative with round and/or ceiling.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now