Calculated Field Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculated Field Problem

Author

Topic

mwpat
Starting Member

2 Posts

Posted - 2004-06-16 : 12:53:40

I am using the following script to get a list of all outstanding invoices. That is, all invoices with an Total where the Outstanding Amount = (Invoices.TotalAmount - all payments and discounts applied to that invoice). This works fine when I have payments or discounts applied against the invoice. However, when there are no payments or discounts applied against the invoice, nothing is returned. I suspect the reason for this is because the following..

(SELECT SUM(apayments.PaymentAmount) from apayments WHERE apayments.InvoiceId = invoices.InvoiceId) +
(SELECT SUM(apayments.DiscountAmount) from apayments WHERE apayments.InvoiceId = invoices.InvoiceId)

returns a null value

Is there a way to calculate invoices.TotalAmount - (paymentTotal+DiscountTotal) so that even if (paymentTotal+DiscountTotal) return null, TotalAmount will return 0

The full code is listed below..

SELECT

DISTINCT invoices.InvoiceId,
invoices.InvoiceNumber,
invoices.InvoiceDate,
invoices.BalanceDueDate,
(invoices.TotalAmount -

(SELECT SUM(apayments.PaymentAmount) from apayments WHERE apayments.InvoiceId = invoices.InvoiceId) +
(SELECT SUM(apayments.DiscountAmount) from apayments WHERE apayments.InvoiceId = invoices.InvoiceId)) as BalanceDue

FROM invoices

WHERE
invoices.TotalAmount -

(SELECT SUM(apayments.PaymentAmount) from apayments WHERE apayments.InvoiceId = invoices.InvoiceId) +
(SELECT SUM(apayments.DiscountAmount) from apayments WHERE apayments.InvoiceId = invoices.InvoiceId)

> 0

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-06-16 : 12:55:39

Have a look at the ISNULL() function in BOL

mwpat
Starting Member

2 Posts

Posted - 2004-06-16 : 15:14:52

quote:
Originally posted by ehorn

Have a look at the ISNULL() function in BOL

Thankyou, works like a charm.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources