ROUND() negatives down? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ROUND() negatives down?

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2007-03-14 : 07:51:28

Hi,

I'm returning data to a reporting utility from a stored procedure to draw a graph.

The data I'm returning ranges from - to + values on the bottom axis (DaysInTarget).

I want the minimum and maximum values I return to be divisible by 10, so the values on my graph always pass through zero (I'm using a marker scale of 10 on the bottom axis of my graph).

So to pad out the values I've already got, my query currently looks something like this (I've included sample data):


create table #results (DaysIntarget int, NumberOfCases int)

insert #results
select -23, 2 union 
select -15, 3 union 
select -5, 6 union 
select -2, 1 union 
select 7, 11 union 
select 8, 5 union 
select 9, 1 union 
select 15, 1 union 
select 27, 3 union 
select 31, 6 

--first, I need to define the min and max values, and make sure they are divisible by 10
declare @minVal int
declare @maxval int

set @minval = (select round(min(daysintarget), -1) from #results)
set @maxval = (select round(max(daysintarget), -1) from #results)

--Then pad out the results I've already got so the graph is OK
create table #padout
(DaysIntarget int, NumberOfCases int)

	while @minval <= @maxval
	
	begin
	
	insert into #padout
	values (@minval, 0)
		
	set @minval = @minval + 1
	
	end

--so the final resultset is a union of these 2 tables
select
daysintarget,
sum(numberofcases) as cases
from
(select * from #results
union
select * from #padout) as final
group by daysintarget

drop table #results
drop table #padout

OK, If you look at the results, I want the lowest value to be -30, and the highest to be 40, so the scale on my graph always passes through zero point - They actually come back as -23 and 31, because my rounding returns the rounded min and max values as -20 and 30.

How do I make the values round up? and does rounding behave the same for negative values as positive (if 15 rounds up to 20, will -15 round 'positively' up to -10, or 'negatively' up to -20?)

Help?

Cheers,

Yonabout

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 08:01:15

[code]set @minval = 10 * (select (min(daysintarget) - 5) / 10 - 1 from #results)
set @maxval = 10 * (select (max(daysintarget) + 5) / 10 + 1 from #results)[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 08:03:36

select	@minval = min(daysintarget) - min(daysintarget) % 10 - 10 from #results
select	@maxval = max(daysintarget) - max(daysintarget) % 10 + 10 from #results

Peter Larsson
Helsingborg, Sweden

yonabout
Posting Yak Master

112 Posts

Posted - 2007-03-14 : 08:08:53

OK, so the ROUND() function doesn't do this kind of rounding very well?

Cheers,

Yonabout

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 08:09:28

Don't use UNION! Use UNION ALL!

-- prepare sample data
create table #results (daysintarget int, numberofcases int)

insert	#results
select	-23, 2 union 
select	-15, 3 union 
select	-5, 6 union 
select	-2, 1 union 
select	7, 11 union 
select	8, 5 union 
select	9, 1 union 
select	15, 1 union 
select	27, 3 union 
select	31, 6 

--first, i need to define the min and max values, and make sure they are divisible by 10
declare @minval int,
	@maxval int

select	@minval = min(daysintarget) - min(daysintarget) % 10 - 10,
	@maxval = max(daysintarget) - max(daysintarget) % 10 + 10
from	#results

--so the final resultset is a union of these 2 tables
select		daysintarget,
		sum(numberofcases) as cases
from		(
			select	daysintarget,
				numberofcases
			from	#result

			union all

			select	number,
				0
			from	f_table_number_range(@minval, @maxval)
		) as final
group by	daysintarget

drop table #results

Peter Larsson
Helsingborg, Sweden

yonabout
Posting Yak Master

112 Posts

Posted - 2007-03-14 : 08:14:47

Sorry!

Cheers,

Yonabout

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 08:15:06

To answer your question, I turned to Books Online for "ROUND FUNCTION".
This is what I read

quote:
Returns the number not having a fractional part that is closest to the argument. If there is more than one number like that, the one that is closest to positive infinity is returned. For example:

If the argument is 2.5, round() returns 3.
If the argument is 2.4999, round() returns 2.
If the argument is -2.5, round() returns -2.
If the argument is an empty sequence, round() returns the empty sequence.

Peter Larsson
Helsingborg, Sweden

yonabout
Posting Yak Master

112 Posts

Posted - 2007-03-14 : 08:21:04

OK,

That makes some kind of sense.

Thanks for your help!

Cheers,

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources