April 29, 2009 at 10:58 pm
I need a scalar valued function like
If i giving the values like 10.25 i need a output as 10.00
My logic is
case 1:
Input value 10.01 to 10.25 then out put should be 10.00
case 2 :
Input value 10.26 to 10.50 then out put should be 10.50
case 3 :
Input value 10.76 to 10.99 then out put should be 11.00
can any one give me a Function for the above expectation
thanks in advance.... here i am trying but not success..
April 29, 2009 at 11:53 pm
hi viswa,
Good morning...
Before u read on make sure that there will be always only 2 decimals will be there in ur values and i wrote it in bit of hurry and not tasted much so u pls do the testing and let me know , will it work or not?
alter function dbo.rnd
(@i numeric(38,2))
returns numeric(38,2)
as
begin
if right(@i,2) between 01 and 25
begin
set @i=round(@i,0)
end
if right(@i,2) between 26 and 50
begin
set @i=left(@i,len(@i)-3)+'.50'
end
if right(@i,2) between 51 and 99
begin
set @i=CEILING(@i)
end
return @i
end
--select dbo.rnd(125454545454.25)
I may be wrong so please find out the mistakes and let me know
Mithun
April 30, 2009 at 12:01 am
Hi u r correct but If the numeric like (17,3) mean how can u filter that
EX:
amount is (10.568) then the if condition will not work.. so try some best..
or amount = 10.123465
thanks in advance
April 30, 2009 at 12:04 am
viswa (4/29/2009)
I need a scalar valued function likeIf i giving the values like 10.25 i need a output as 10.00
My logic is
case 1:
Input value 10.01 to 10.25 then out put should be 10.00
case 2 :
Input value 10.26 to 10.50 then out put should be 10.50
case 3 :
Input value 10.76 to 10.99 then out put should be 11.00
can any one give me a Function for the above expectation
thanks in advance.... here i am trying but not success..
You missed the case of
Input value 10.51 to 10.75 then out put should be ....?
But it seems just like normal rounding but to the half and 0.01 down.
As soon as you figure it out the solution is simple:
ROUND((n-0.01)*2, 0)/2.00
_____________
Code for TallyGenerator
April 30, 2009 at 12:09 am
But my exact input length of parameter is numeric(18,7)
and output is numeric(18,7)
that time your condition will not work so make me exact..
April 30, 2009 at 12:32 am
viswa (4/30/2009)
But my exact input length of parameter is numeric(18,7)and output is numeric(18,7)
that time your condition will not work so make me exact..
Then you need to make some effort in specifying the request.
There are 100k different numbers between 10.25 and 10.26 which are not considered by your spec.
_____________
Code for TallyGenerator
April 30, 2009 at 1:27 am
mithun.gite (4/29/2009)
hi viswa,Good morning...
Before u read on make sure that there will be always only 2 decimals will be there in ur values and i wrote it in bit of hurry and not tasted much so u pls do the testing and let me know , will it work or not?
alter function dbo.rnd
(@i numeric(38,2))
returns numeric(38,2)
as
begin
if right(@i,2) between 01 and 25
begin
set @i=round(@i,0)
end
if right(@i,2) between 26 and 50
begin
set @i=left(@i,len(@i)-3)+'.50'
end
if right(@i,2) between 51 and 99
begin
set @i=CEILING(@i)
end
return @i
end
--select dbo.rnd(125454545454.25)
I may be wrong so please find out the mistakes and let me know
Mithun
Hi viswa,
Just alter the Mithun function to
declare @RESULT numeric (18,7), @con int
select @RESULT = 10.4534567
select @con = RIGHT(@RESULT,7)
SELECT case when @con between 0000001 and 2500000 then ROUND(@RESULT, 0, 1)
when @con between 2500001 and 5000000 then (ROUND(@RESULT, 0, 1)+0.500)
when @con between 5000001 and 9999999 then ROUND(@RESULT, 0, 0) end
ARUN SAS
April 30, 2009 at 1:54 am
Declare @aa numeric(10,2)
Set @aa = 1.60
Select case when @aa between cast(@aa as int) and cast(cast(cast(@aa as int) as varchar(10)) + '.25' as numeric(10,2)) then cast(cast(@aa as int) as numeric(10,2))
when @aa between cast(cast(cast(@aa as int) as varchar(10)) + '.25' as numeric(10,2)) and cast(cast(cast(@aa as int) as varchar(10)) + '.50' as numeric(10,2)) then cast(cast(cast(@aa as int) as varchar(10)) + '.5' as numeric(10,2))
when @aa between cast(cast(cast(@aa as int) as varchar(10)) + '.51' as numeric(10,2)) and cast(cast(cast(@aa as int) as varchar(10)) + '.99' as numeric(10,2)) then cast(cast(@aa as int) + 1 as numeric(10,2)) else 0.00 end
April 30, 2009 at 3:59 am
viswa (4/30/2009)
Hi u r correct but If the numeric like (17,3) mean how can u filter thatEX:
amount is (10.568) then the if condition will not work.. so try some best..
or amount = 10.123465
thanks in advance
hi viswa
have it upto 8 decimal
alter function dbo.rnd
(@i numeric(38,8))
returns numeric(38,8)
as
begin
declare @y numeric(38,8)
set @y=substring(cast(@i as varchar(50)),(charindex('.',@i)),len(@i))
if @y between 0.10000000 and 0.25000000
begin
set @i=round(@i,0)
end
if @y between 0.25000001 and 0.50000000
begin
set @i=left(@i,charindex('.',@i))
set @i=@i+0.50
end
if @y between 0.50000001 and 0.99999999
begin
set @i=CEILING(@i)
end
return @i
end
and if u want more then just increase the scale values of all variables in function...
hope this will work
Mithun
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply