Divide Integers

I'm trying to divide 2 integers to return a column of C's per thousand with
(Failures / Dropped) * 1000.
I've tried different version of float, cast as decimal etc. I'd like 4-5
didgits after the decimal. What is the correct way to divide integers to
return a usable number.
Yr Mnth Dropped Failures
-- -- -- --
2005 July 126493 610
2005 August 207325 955
2005 September 89714 742
2005 October 225112 1142
2005 November 186264 791
2005 December 146901 774
2006 January 103096 510Try
SELECT CAST((CAST(Failures AS numeric) / Dropped) * 1000 AS decimal(8,4))
FROM [YourTable]
"Paul Ilacqua" wrote:

> I'm trying to divide 2 integers to return a column of C's per thousand wi
th
> (Failures / Dropped) * 1000.
> I've tried different version of float, cast as decimal etc. I'd like 4-5
> didgits after the decimal. What is the correct way to divide integers to
> return a usable number.
> Yr Mnth Dropped Failures
> -- -- -- --
> 2005 July 126493 610
> 2005 August 207325 955
> 2005 September 89714 742
> 2005 October 225112 1142
> 2005 November 186264 791
> 2005 December 146901 774
> 2006 January 103096 510
>
>|||Paul Ilacqua (pilacqu2@.twcny.rr.com) writes:
> I'm trying to divide 2 integers to return a column of C's per thousand
> with (Failures / Dropped) * 1000. I've tried different version of
> float, cast as decimal etc. I'd like 4-5 didgits after the decimal. What
> is the correct way to divide integers to return a usable number.
Does one of:
convert(decimal(18,4), 1E0 * Failures / Dropped * 1000)
round(1E0 * Failures / Dropped * 1000, 4)
meet your requirements? Note that the last expression is float, so it
may have 000000001 or similar at the end when you look at it in Query
Analyzer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That does work fine.... Thank You very much.
It is sure odd numeric behavior... calculate it in Access or Excel and
it's automatic. Would you call this method "standard" in handling integer
division?
Thanks again
Paul
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:FF7A96CF-B908-4FCD-BE98-6F6AB8FFDC8A@.microsoft.com...
> Try
> SELECT CAST((CAST(Failures AS numeric) / Dropped) * 1000 AS decimal(8,4))
> FROM [YourTable]
>
> --
> "Paul Ilacqua" wrote:
>|||Did you bother to look up the basic rules of integer math in SQL?|||Paul Ilacqua (pilacqu2@.twcny.rr.com) writes:
> That does work fine.... Thank You very much.
> It is sure odd numeric behavior... calculate it in Access or Excel and
> it's automatic. Would you call this method "standard" in handling integer
> division?
Exactly what is odd? integer/integer meaning integer division appears
in several other programming languages as well. And the fact that you
need to round or convert the result to get the number of desired decimals
is not strange at all - how should SQL Server be able to guess what you
want?
Yes, Excel has a number of rules for precisely that, guessing. Sometimes
it works, sometimes it does not. I have some CSV files around which
has data like 19991112093212, which Excel presents as float values
each time I open the file. In fact this is a string (date and time).
But Excel is an interactive tool, so mis-guesses can easily be corrected.
A server like SQL Server must work after more stringent rules.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
I agree... after I replied I realized that's why I like SQL Server over
Access or Excel (among other reasons) is that allows a finer control over
things like number formatting. Excel uses it's best guess a lot where it is
not always the desired result. "Odd" is a word that is replaced by
"understanding" after you learn something and that's why I frequent these
boards. (and sometimes take some hits for it)
Thanks again
Paul
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9755642E25FB4Yazorman@.127.0.0.1...
> Paul Ilacqua (pilacqu2@.twcny.rr.com) writes:
> Exactly what is odd? integer/integer meaning integer division appears
> in several other programming languages as well. And the fact that you
> need to round or convert the result to get the number of desired decimals
> is not strange at all - how should SQL Server be able to guess what you
> want?
> Yes, Excel has a number of rules for precisely that, guessing. Sometimes
> it works, sometimes it does not. I have some CSV files around which
> has data like 19991112093212, which Excel presents as float values
> each time I open the file. In fact this is a string (date and time).
> But Excel is an interactive tool, so mis-guesses can easily be corrected.
> A server like SQL Server must work after more stringent rules.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx