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.
Author |
Topic |
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2012-12-21 : 11:04:22
|
Dear All,Need help on the below query.I have pulled this script from postgres database and modifed in sql sever.output in postgres=0.00273785078713210130output in sql server=0.00273972602739726the logic is the same as in postgres,not understanding what is the issue in this queryplease help me the data type for @u and @t in postgres is just numeric,i have taken as float in sql serverdeclare @u float=1declare @t float=2declare @d VARCHAR(255)='N' DECLARE @Output floatIF @u > @t * (365.25/12) BEGIN SELECT @Output=1 ENDELSE IF @t <= 0 OR @u <= 0 BEGIN SELECT @Output=0 ENDELSE IF (@d = 'N' and @t < 85) or (@d = 'U' and @t <61) BEGIN SELECT @Output = (earnlo + (earnhi - earnlo) * ( @u - expdayslo) / (expdayshi - expdayslo) ) FROM rm_vscearn WHERE @u BETWEEN expdayslo AND expdayshi AND @t BETWEEN termlo AND termhi AND @d = newused ENDELSE BEGIN SELECT @Output = @u/(@t * (365.25/12)) ENDselect @Output |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-21 : 11:24:22
|
float is approximate numeric datatype so if you want accuracy till particular decimal place use numeric or decimal datatype instead------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2012-12-21 : 11:29:12
|
quote: Originally posted by visakh16 float is approximate numeric datatype so if you want accuracy till particular decimal place use numeric or decimal datatype instead------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Hi Visakh,I tried using numeric(22,20) but i get output as 0.00273900000000000000 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-21 : 11:48:59
|
the result will be corrected to highest significant figure among operands. so check the values of fields involved in the calculation and see if they've required precision and scale.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2012-12-21 : 11:57:56
|
quote: Originally posted by visakh16 the result will be corrected to highest significant figure among operands. so check the values of fields involved in the calculation and see if they've required precision and scale.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Thanks for the reply.all the fields involved in the calculation have integer values,they don't have any decimal value in the table.so,how do i achieve the required output. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-21 : 14:06:19
|
cast them to required decimal then------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
|
|
|