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
