By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,268 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Using Round in a sql query

P: 12
I have the following two cases in my sql query I would like to round the results (round 0) however I cannot figure out the correct syntax to use round in this instance.. any help on this would be greatly appreciated

Expand|Select|Wrap|Line Numbers
  1. CASE WHEN tbl_CCMonitorSheetAnswer.Answer = 'False' THEN (tbl_CCMonitorFormQuestion.Points - tbl_CCMonitorFormQuestion.Points) 
  2.                       WHEN tbl_CCMonitorSheetAnswer.Answer IS NULL THEN NULL ELSE tbl_CCMonitorFormQuestion.Points END AS [Achieved Points], 
  3.  
  4.                       CASE WHEN tbl_CCMonitorSheetAnswer.Answer IS NULL THEN NULL ELSE tbl_CCMonitorFormQuestion.Points END AS [Max Points],
May 19 '08 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
Round(0) ?

Do you want to remove the decimal place?

Here's the full ROUND syntax.

-- CK
May 19 '08 #2

P: 12
Yes that is correct (I want to remove the decimal place).. and i have read over that but I cannot figure out how to use round in conjunction with the case syntax (my example above) how would i incorporate round into that select?
May 19 '08 #3

P: 12
I can do it on the reporting end and use Round(Fields!Achieved_Points.Value) but it then rounds all Null Values to 0 and null values need to remain Null
May 19 '08 #4

P: 12
Also if i modify my query like the following.. it only rounds to the nearest whole number but leaves the decimal place

Expand|Select|Wrap|Line Numbers
  1. CASE WHEN tbl_CCMonitorSheetAnswer.Answer = 'False' THEN Round(tbl_CCMonitorFormQuestion.Points - tbl_CCMonitorFormQuestion.Points, 0) 
  2. WHEN tbl_CCMonitorSheetAnswer.Answer IS NULL THEN NULL ELSE Round(tbl_CCMonitorFormQuestion.Points, 0) END AS [Achieved Points], 
  3. CASE WHEN tbl_CCMonitorSheetAnswer.Answer IS NULL THEN NULL ELSE round(tbl_CCMonitorFormQuestion.Points, 0) END AS [Max Points], 
May 19 '08 #5

ck9663
Expert 2.5K+
P: 2,878
I can do it on the reporting end and use Round(Fields!Achieved_Points.Value) but it then rounds all Null Values to 0 and null values need to remain Null
Then a CASE function would do

Expand|Select|Wrap|Line Numbers
  1. Value = case when Fields!Achieved_Points.Value is null then null else Round(Fields!Achieved_Points.Value) end
-- CK
May 20 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.