Solved

sql 2014 query

Posted on 2014-04-30
7
350 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
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 500 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now