I have a SELECT query that basically adds up my sales, removes credit adjustments (eg. Returns) and gives the net figure.
This is the 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 '2004-07-11' AND '2004-07-17' )) + SUM(cli_tran_amount) AS amount FROM vetpmardet WHERE cli_tran_trans_date BETWEEN '2004-07-11' AND '2004-07-17' AND sys_transaction_type LIKE 'C'
The subquery in there is necessary to link the credit back to the original transaction. This query works well as far as I’m concerned.
Where I run into problems is when I try to break the report down by staff codes. Unfortunately, my accounting s/w does not put a staff code with the credit adjustment, but I do want to be able to see the net sales by staff code. So I modify the query like this: (add one select column and a group by clause)
SELECT dat_staff_code, (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 '2004-07-11' AND '2004-07-17' )) + SUM(cli_tran_amount) AS amount FROM vetpmardet WHERE cli_tran_trans_date BETWEEN '2004-07-11' AND '2004-07-17' AND sys_transaction_type LIKE 'C' GROUP BY dat_staff_code
But this query will not produce a row for dat_staff_code when it’s value is null. It only produces grouped rows where dat_staff_code is not null, and the sum of those rows does not equal the value of the single row returned in the first query above. How do I get it to produce a row even when dat_staff_code is null?
TIA
Caleb
