Help! Computed Column

I have a strange behavoiur
adding a computed column with the formulae (100 - 38 / 41 * 100)
gives a result in excel as 7.317073171 (correct) but in an sql column it
always returns 100
(the acutal formulae would be (100 - [FIELD] / 41 * 100)
any ideas?
Regards
Toby RileyToby
What's the datatype of [FIELD] you defined in SQL Server?
"TobyRiley" <TobyRiley@.discussions.microsoft.com> wrote in message
news:7B7BA6E5-89FE-4003-9E68-B8B11E12AA57@.microsoft.com...
>I have a strange behavoiur
> adding a computed column with the formulae (100 - 38 / 41 * 100)
> gives a result in excel as 7.317073171 (correct) but in an sql column it
> always returns 100
> (the acutal formulae would be (100 - [FIELD] / 41 * 100)
>
> any ideas?
>
> --
> Regards
> Toby Riley|||>> I have a strange behavoiur
adding a computed column with the formulae (100 - 38 / 41 * 100) <<
Integer math versus decimal math.|||The column might be an integer column
Use it this way.
100 - ([FIELD] *1.0)/ 41 * 100
Hope this helps.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"TobyRiley" wrote:

> I have a strange behavoiur
> adding a computed column with the formulae (100 - 38 / 41 * 100)
> gives a result in excel as 7.317073171 (correct) but in an sql column it
> always returns 100
> (the acutal formulae would be (100 - [FIELD] / 41 * 100)
>
> any ideas?
>
> --
> Regards
> Toby Riley|||Try using "41.0", as (100 - [FIELD] / 41.0 * 100)
Martin C K Poon
Senior Analyst Programmer
====================================
"TobyRiley" <TobyRiley@.discussions.microsoft.com> bl
news:7B7BA6E5-89FE-4003-9E68-B8B11E12AA57@.microsoft.com g...
> I have a strange behavoiur
> adding a computed column with the formulae (100 - 38 / 41 * 100)
> gives a result in excel as 7.317073171 (correct) but in an sql column it
> always returns 100
> (the acutal formulae would be (100 - [FIELD] / 41 * 100)
>
> any ideas?
>
> --
> Regards
> Toby Riley|||If [FIELD] in an integer datatype, then anything less than 41 divided by 41
will be truncated to 0, multiplied by 100 it is STILL 0. Subtract 0 from 100
and you have 100. One of the value in the division must be a datatype that
supports decimal digits.
HTH
Kalen Delaney, SQL Server MVP
"TobyRiley" <TobyRiley@.discussions.microsoft.com> wrote in message
news:7B7BA6E5-89FE-4003-9E68-B8B11E12AA57@.microsoft.com...
>I have a strange behavoiur
> adding a computed column with the formulae (100 - 38 / 41 * 100)
> gives a result in excel as 7.317073171 (correct) but in an sql column it
> always returns 100
> (the acutal formulae would be (100 - [FIELD] / 41 * 100)
>
> any ideas?
>
> --
> Regards
> Toby Riley|||Thank for all your quick replies, that's fixed it.
--
Regards
Toby Riley
"Kalen Delaney" wrote:

> If [FIELD] in an integer datatype, then anything less than 41 divided by 41
> will be truncated to 0, multiplied by 100 it is STILL 0. Subtract 0 from 1
00
> and you have 100. One of the value in the division must be a datatype tha
t
> supports decimal digits.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "TobyRiley" <TobyRiley@.discussions.microsoft.com> wrote in message
> news:7B7BA6E5-89FE-4003-9E68-B8B11E12AA57@.microsoft.com...
>
>