Hi all,

I'm using PostgreSQL database with the following tables:
sales (
invoice_no
staff_no
date_purchase timestamp
total_purchase
)

sales_details (
invoice_no
inventory_no
quantity_sold
sold_price
)

returned (
return_no
date_returned
invoice_no
inventory_no
quantity_returned
)

staff (
staff_no
name
)

The problem is, how do I get a listing of dates of the whole month, with the staff names, total sales for that date and total returned for that date.

I'm able to do the above but without the total returned for the date. It's as:

SELECT date(s.date_purchase) AS "Date Purchased", st.name AS "Staff Name", sum(s.total_purchase) AS "Amount sold" FROM sales s, staff st WHERE (s.staff_no = st.staff_no) GROUP BY date(s.date_purchase), st.name;

But how do I do for the total returned (for that date and staff). I believe my trouble is that the returned table has no staff_no field and that makes it difficult...

I'm open to using PL/PGSQL (i.e. using a stored procedure that returns a set of rows)

You could join the sales.invoice_no with the returned invoice_no!

In fact, I did that yesterday, as below:

(SELECT date(s.date_purchase ) AS "Date", st.name AS "Staff name", sum(s.total_purchase ) AS "Total Sales", 0 AS "Total returned"
FROM sales s, staff st
WHERE (s.staff_no = st.staff_no )
GROUP BY date(s.date_purchase ), st.name )
UNION
(
SELECT r.date_return AS "Date", st.name AS "Staff name", 0 AS "Total Sales", sum(r.sold_price ) AS "Total returned"
FROM returned r, staff st, sales s
WHERE ((r.invoice_no = s.invoice_no ) AND (s.staff_no = st.staff_no ))
GROUP BY r.date_return, st.name ));

But the trouble is, I can't get a listing for each day. So if there's no sales or returned goods, then there's no entry for that. Furthermore, the above UNION SQL statement separate the "Total Sales" and "Total Returned" into 2 different rows.

Try combining it like this:
Note: you do not need aliases on the UNION second select!