469,167 Members | 1,453 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

Need help with SQL query

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.

Jul 23 '05 #1
1 1038
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)
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by lawrence | last post: by
9 posts views Thread by netpurpose | last post: by
6 posts views Thread by paii | last post: by
reply views Thread by ward | last post: by
10 posts views Thread by L. R. Du Broff | last post: by
7 posts views Thread by Rnykster | last post: by
3 posts views Thread by pbd22 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.