Need help with SQL query

[bs2]

I'm going to simplify the tables and queries to eliminate all but the
pertinent info.


We have a table containing detail information on our invoices called
ARDETAIL. Each line item on the invoice is included as a record, each
cash receipt is included as a record and each write off is included as
a record.


The layout is as follows:


OrderNumber
Item
Quantity
Amount


I can get a list of all the unpaid invoices with the following query:


SELECT Customer, OrderNumber, ISNULL(SUM(ROUND(QTY * Amount, 2)),0) AS BALANCE
FROM ARDetail
GROUP BY Customer, OrderNumber
HAVING (ISNULL(SUM(ROUND(QTY * Amount, 2)), 0) <> 0)
ORDER BY Customer, OrderNumber, BALANCE


When an invoice is written off a new record is created with item of '5' and amount of balance * -1. I want to modify my query to return all invoices with balance <> 0 or that have been written off (item will be 5, balance will be 0).


Thanks.

 

[PHV]

You consider an UNION query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244