January 13, 2011 at 12:22 pm
Something very simple and then again I can't seem to solve it.
select SumOfPremium, SumOfNormal, SumOfNormalOnTime, SumOfPremiumOnTime, (SumOfPremiumOnTime/SumOfPremium) as pt from ......
Results:
104216865 15830 8180
Results are ok, except for the last column where I was expecting 0.78!
Whatever I try, it stays 0.
Furthermore: In Acces it is 0.78 and so it should be.....
This seems to be just too silly to be true.
Can anyone explain me what is happening here? (and how I can fix this?)
Henk
January 13, 2011 at 12:33 pm
Probably you are calculating using int columns or decimal(x,0).
With sqlserver you need to take care of the precision you need.
In your case I would suggest to test this:
cast((SumOfPremiumOnTime * 1.00 /SumOfPremium) as decimal( 5,2)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 13, 2011 at 12:47 pm
^ What he said, or alter your source table to store the data at the proper precision, which looks like you want decimal ( x, 2).
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 14, 2011 at 12:19 am
Another lesson learned!
However:
The crux is not in the decimal(,xy) but in the 1.0!
(Sum([PremiumOnTime])*1.0/Sum([premium]) )
Just putting in the 1.0 makes that I get all the decimals that I could think of.
Nice!
Henk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply