Help with a calculation please

18 Aug 2006 6:59 PM

ITDUDE27

Hello,

I have this stored procedure that is calcualting invoice payment. For some
awkward reason it some of the record the right payment amount and for others
it gives some rediculest number. Can anyone see what i'm doing wrong ?

sum(ar2.payment_amount - (ih.freight * (ar2.payment_amount /
cs.total_amount))) as [TotalAmount],

when input the real numbers instead of field name I get the correct value.
i.e. [ select sum( 14387-(7787 * (14387/7787))) ]

Thanks in advance.

18 Aug 2006 7:24 PM

David Browne

Show quote

"ITDUDE27" <ITDUD***@discussions.microsoft.com> wrote in message
news:D53D8B8A-8504-45FA-8B92-3FDB759BD5FB@microsoft.com...
> Hello,
>
> I have this stored procedure that is calcualting invoice payment. For some
> awkward reason it some of the record the right payment amount and for
> others
> it gives some rediculest number. Can anyone see what i'm doing wrong ?
>
> sum(ar2.payment_amount - (ih.freight * (ar2.payment_amount /
> cs.total_amount))) as [TotalAmount],
>
> when input the real numbers instead of field name I get the correct value.
> i.e. [ select sum( 14387-(7787 * (14387/7787))) ]
>

Please provide DDL and sample data
http://www.aspfaq.com/etiquette.asp?id=5006

David

19 Aug 2006 2:54 PM

--CELKO--

Your sample data shows integet values; I would assumed that you are
dealing with decimals and do not want to do interger math.

18 Aug 2006 7:27 PM

SQL Ken

is any of the value null??

ITDUDE27 wrote:
Show quote

> Hello,
>
> I have this stored procedure that is calcualting invoice payment. For some
> awkward reason it some of the record the right payment amount and for others
> it gives some rediculest number. Can anyone see what i'm doing wrong ?
>
> sum(ar2.payment_amount - (ih.freight * (ar2.payment_amount /
> cs.total_amount))) as [TotalAmount],
>
> when input the real numbers instead of field name I get the correct value.
> i.e. [ select sum( 14387-(7787 * (14387/7787))) ]
>
> Thanks in advance.

18 Aug 2006 7:36 PM

ITDUDE27

no.

Show quote

"SQL Ken" wrote:

> is any of the value null??
>
> ITDUDE27 wrote:
> > Hello,
> >
> > I have this stored procedure that is calcualting invoice payment. For some
> > awkward reason it some of the record the right payment amount and for others
> > it gives some rediculest number. Can anyone see what i'm doing wrong ?
> >
> > sum(ar2.payment_amount - (ih.freight * (ar2.payment_amount /
> > cs.total_amount))) as [TotalAmount],
> >
> > when input the real numbers instead of field name I get the correct value.
> > i.e. [ select sum( 14387-(7787 * (14387/7787))) ]
> >
> > Thanks in advance.
>
>

18 Aug 2006 7:59 PM

Roy Harvey

>when input the real numbers instead of field name I get the correct value.
>i.e. [ select sum( 14387-(7787 * (14387/7787))) ]

That expression is self contradictory. The SUM expression is summing
the total expression - but there is only one "instance" of the
expression in your substitution. Using SUM implies there are multiple
rows involved. My guess is that some of the figures should be summed
individually, BEFORE being used in some part of the calculation, while
other parts should summed AFTER.

Roy Harvey
Beacon Falls, CT

On Fri, 18 Aug 2006 11:59:02 -0700, ITDUDE27
<ITDUD***@discussions.microsoft.com> wrote:

Show quote

>Hello,
>
>I have this stored procedure that is calcualting invoice payment. For some
>awkward reason it some of the record the right payment amount and for others
>it gives some rediculest number. Can anyone see what i'm doing wrong ?
>
>sum(ar2.payment_amount - (ih.freight * (ar2.payment_amount /
>cs.total_amount))) as [TotalAmount],
>
>when input the real numbers instead of field name I get the correct value.
>i.e. [ select sum( 14387-(7787 * (14387/7787))) ]
>
>Thanks in advance.
>