Date: Tue, 20 Jul 2004 09:27:14 -0600
From: Caleb Simonyi-Gindele <csg@bordervet.ca>
Subject: SELECT query/subquery problem
To: pgsql-sql@postgresql.org
Message-id: <000001c46e6e$08cbd310$c201a8c0@borderveygqj37>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
Importance: Normal

I'm still trying to isolate issues with my SELECT query. I have a table in
my veterinary software which stores my sales transactions. The pertinent
columns are dat_staff_code (stores the doctor who gets credit for the sale),
sys_tran_number (stores a unique transaction #), cli_credit_adj_trans_no
(stores the sys_tran_number that a credit is put against).

My problem is that while dat_staff_code is populated for sales, it is not
populated when a credit against a sale is issued. So I end up with data as
follows:

|dat_staff |sys_tran |cli_credit   |cli_tran|
|_code     |_number  |_adj_tran_no |_amount |
|----------|---------|-------------|--------|
|mm        |91112    |             |50.00   |
|          |95402    |91112        |-50.00  |

What I want to end up with is net sales (sales - credits) GROUP BY
dat_staff_code. Where I'm stuck is I can't link the credits to a doctor for
the life of me. I use a subquery to get amt where cli_credit_adj_tran_no
matches sys_tran_number but I can't associate it with the doctor of the
original transaction.

This is my query:
SELECT 
	(SELECT SUM(cli_tran_amount) 
	FROM vetpmardet 
	WHERE cli_credit_adj_trans_no 
	IN 
		(SELECT sys_tran_number from vetpmardet 
		WHERE cli_tran_trans_date 
		BETWEEN '$pro_week_start_date_yyyymmdd' 
		AND '$pro_week_end_date_yyyymmdd'))
+ SUM(cli_tran_amount) AS amount 
FROM vetpmardet 
WHERE cli_tran_trans_date 
BETWEEN '$pro_week_start_date_yyyymmdd' 
AND '$pro_week_end_date_yyyymmdd' 
AND TRIM(dat_staff_code) LIKE 'mm'

Should I be switching to a union query or something?

TIA,
Caleb