Division query

5 Jan 2006 1:42 PM

NAVIN.D

i have an equation which i have to implement in query:

availa= (1-[return0]/sum(r1+r2+r3+r4+r5))

when tried the query i get avail as zero always, help me out

5 Jan 2006 1:56 PM

impslayer

NAVIN.D skrev:

> i have an equation which i have to implement in query:
>
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
>
> when tried the query i get avail as zero always, help me out

Without knowing what the above equation is or where it is used the easy
explanation would be that it's caused by integer division, ie.
everything below 1.0 will become 0.

/impslayer, aka Birger Johansson

5 Jan 2006 1:58 PM

Dan Guzman

You will get an integer result when all operands are integer. You can
specify a decimal operand if you need decimal result. The result will
decimal in that case because the decimal data type has a higher precedence
than integer. Try:

availa = (1.0-[return0]/sum(r1+r2+r3+r4+r5))

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quoteHide quote

"NAVIN.D" <NAV***@discussions.microsoft.com> wrote in message
news:48C5F07C-1023-4AB1-9B17-356321EB95CD@microsoft.com...
> i have an equation which i have to implement in query:
>
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
>
> when tried the query i get avail as zero always, help me out

5 Jan 2006 2:00 PM

Mark Williams

If SUM(r1+r2+r3+r4+r5) > (1-[return0]), you will get zero because SQL will
return a value within the datatype integer. (The answer will be truncated).

Try

(1.00 - [return0]/sum(r1+r2+r3+r4+r5)

--

Show quoteHide quote

"NAVIN.D" wrote:

> i have an equation which i have to implement in query:
>
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
>
> when tried the query i get avail as zero always, help me out

5 Jan 2006 2:07 PM

NAVIN.D

thank you mark and imsplayer it was decimal and integer funda only i got it
immd after posting anyways thanks for the reply

Show quoteHide quote

"Mark Williams" wrote:

> If SUM(r1+r2+r3+r4+r5) > (1-[return0]), you will get zero because SQL will
> return a value within the datatype integer. (The answer will be truncated).
>
> Try
>
> (1.00 - [return0]/sum(r1+r2+r3+r4+r5)
>
>
>
> --
>
>
>
> "NAVIN.D" wrote:
>
> > i have an equation which i have to implement in query:
> >
> > availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
> >
> > when tried the query i get avail as zero always, help me out

Thread options