Ceiling upto 5 Decimals. - 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)

Ceiling upto 5 Decimals.

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-05 : 02:45:50

Hi All,

Can any one please let me know how can i apply Ceiling and Floor function to round upto 5 Decimals..

Something like what we have in Microsoft Excel

if i have value 1232 and I do FLOOR(1232,5), the value will come 1230 and if do CEILING(1232,5) and then it will come 1235.

Is it possible to do using T-SQL.

I know i can do this very well in front end, but in our package we have something like query analyser in which user can apply some queries and can pull out the data on grid and then can export.. so it will be better i can do this using Queries.

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-05 : 03:27:55

This is what you want ?

declare	@sig	int

select	@sig	= 5

select	num, num / @sig * @sig as [excel floor], ((num / @sig) * @sig) +ceiling((num % @sig) * 1.0 / @sig) * @sig as [excel ceiling]
from
(
	select	(1230 + n) as num
	from
	(
		select	0 as n	union all	select	1 as n	union all
		select	2 as n	union all	select	3 as n	union all
		select	4 as n	union all	select	5 as n	union all
		select	6 as n	union all	select	7 as n	union all
		select	8 as n	union all	select	9 as n	union all
		select 10 as n	union all	select 11 as n
	) a
) b

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-05 : 03:36:02

i think this is what you want:
EDIT: better and shorter


create function dbo.FloorEx(@num decimal(38, 33), @decimalPosition int)
returns decimal(38, 33)
as 
begin
if @decimalPosition < 0
	set @decimalPosition = 0
return floor(@num*power(10, @decimalPosition))/power(10, @decimalPosition)
end
go
create function dbo.CeilingEx(@num decimal(38, 33), @decimalPosition int)
returns decimal(38, 33)
as 
begin
if @decimalPosition < 0
	set @decimalPosition = 0
return ceiling(@num*power(10, @decimalPosition))/power(10, @decimalPosition)
end
go


declare @num decimal(38, 33), @decimalPosition int
select @num  = 10.123456, @decimalPosition = 3
select	@num , 
		dbo.FloorEx(@num , @decimalPosition), 
		dbo.CeilingEx(@num , @decimalPosition) 
go
drop function dbo.FloorEx
drop function dbo.CeilingEx

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-05 : 04:02:08

khtan : I guess you must be one of the Math gurus. That was which i was looking for. I understood how did you got the Floor but i am just wondering how did you got the Ceiling. It will be great if you can explain me.

Spirt : Aha Thanks, i can get this kind of requirement at any time, so that time i can use your code, but for the current requirement i dont want to round up the precision values only the integer values.

Thanks both of you for the quick help

i was just getting mad . playing around with Round function.. with no luck..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-05 : 04:27:49

see the intermidiate calc value with the script below

declare	@sig	int

select	@sig	= 5

select	num, num / @sig * @sig as [excel floor], ((num / @sig) * @sig) + ceiling((num % @sig) * 1.0 / @sig) * @sig as [excel ceiling],
	num % @sig as [remainder of num divide by @sig],
	(num % @sig) * 1.0 / @sig as [convert to remainder in decimal so that i can use ceiling()],
	ceiling((num % @sig) * 1.0 / @sig) as [convert remainder to 0 or 1]
from
(
	select	(1230 + n) as num
	from
	(
		select	0 as n	union all	select	1 as n	union all
		select	2 as n	union all	select	3 as n	union all
		select	4 as n	union all	select	5 as n	union all
		select	6 as n	union all	select	7 as n	union all
		select	8 as n	union all	select	9 as n	union all
		select 10 as n	union all	select 11 as n
	) a
) b

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-05 : 05:02:15

Aha pretty simple once you break down..

Thanks a lot for the help..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-05 : 05:03:51

quote:
Originally posted by chiragkhabaria

Aha pretty simple once you break down..

Thanks a lot for the help..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources