Need help with SQL query

[bstout2]

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).

Example:

Thanks.

[Hugo Kornelis]

On 25 Mar 2005 08:15:47 -0800, bs*****@gmail.com wrote:

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).

Hi bstout,

SELECT Customer, OrderNumber,
COALESCE(SUM(ROUND(QTY * Amount, 2)), 0) AS Balance
FROM ARDetail
GROUP BY Customer, OrderNumber
HAVING (COALESCE(SUM(ROUND(QTY * Amount, 2)), 0) <> 0)
OR MAX(CASE WHEN Item = 5 THEN 1 ELSE 0 END) = 1
ORDER BY Customer, OrderNumber, Balance

Untested, since you didn't post CREATE TABLE and INSERT statements with
sample data plus expected output (see www.aspfaq.com/5006).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)