From: Caleb Simonyi-Gindele <csg@bordervet.ca>
To: pgsql-sql@postgresql.org
Subject: SELECT query/subquery problem
Date: Tue, 20 Jul 2004 09:27:14 -0600
Message-ID: <000001c46e6e$08cbd310$c201a8c0@borderveygqj37> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: csg@bordervet.ca
Subject: Re: SELECT query/subquery problem
In-Reply-To: <000001c46e6e$08cbd310$c201a8c0@borderveygqj37>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox