Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql 2014 query

Posted on 2014-04-30
7
Medium Priority
?
385 Views
Last Modified: 2014-05-01
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
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40031838
it says roundup.. i think there is a round function.. try this

select ROUND (111,40)
0
 
LVL 5

Author Comment

by:25112
ID: 40031848
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
 
LVL 8

Expert Comment

by:Surrano
ID: 40031875
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 5

Author Comment

by:25112
ID: 40031895
Surrano, with your example how would you convert
ROUNDUP(1.3,0.2)
with
ROUND or CEILING?
0
 
LVL 8

Expert Comment

by:Surrano
ID: 40032330
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
 
LVL 5

Author Comment

by:25112
ID: 40032569
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
 
LVL 10

Accepted Solution

by:
PadawanDBA earned 2000 total points
ID: 40034706
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

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question