Hello Group
I having problems creating a result set. The challenge is I have 3
tables: A customer table, A charge table and a payment table..... The
result set needs to show all payments and what detail lines were paid
down by each payment. Both the detail line table and payment table can
contain positive and negative transactions...... Each payment must be
listed and split across the detail lines if needed... unpaid detail
lines should not be displayed in the final result set..,.. If a
customer paid more money then he owes that needs to display in the
result set as well....
Any assistance anyone could provide will be greatly appreciated
Customer: customer name, customer id, customer pk
charge: transaction date, quantity, item id, item description, unit
price, total price customer id
Payment Table: transaction date, payment type, payment amount,
description (optional) customer id
customer data
customer 1 QWER COMPANY
customer 2 ZXCV COMPANY
customer 2 IOP COMPANY
charge data
customer 1, 04/01/2008, 3 WIDGET A, 5.00, 15,00
customer 1 04/02/2008, 1 WIDGET B 2,00, 2.00
customer 1 04/03/2008 3 WIDGET A -5.00, -15.00
customer 2 04/10/2008 1 WIDGET A 5.00 5.00
customer 2 04/12/2008 2 WIDGET D 10.00 20.00
customer 2 04/15/2008 5 WIDGET C 20.00 100.00
customer 2 04/20/2008 2 WIDGET X 100.00 200.00
payment data
customer 1, 04/05/2008 CASH 2.00
customer 2 05/01/2008 CHECK 100.00
customer 2 05/10/2008 CHECK - 100.00 (RETURN CHECK)
customer 2 05/20/2008 CASH 15.00
customer 2 05/25/2008 VISA 500.00
RESULT SET
CUSTOMER 1 04/05/2008 CASH 2.00
04/02/2008 WIDGET B 2.00
CUSTOMER 2 05/01/2008 CHECK 100.00
CUSTOMER 2 05/10/2008 CHECK -100.00 RETURN CHECK
CUSTOMER 2 05/20/2008 CASH 5.00
04/10/2008 WIDGET A 5.00
CUSTOMER 2 05/20/2008 CASH 10.00
04/12/2008 WIDGET D 10.00
CUSTOMER 2 05/20/2008 VISA 10.00
04/12/2008 WIDGET D 10.00
CUSTOMER 2 05/20/2008 VISA 100.00
04/15/2008 WIDGET C 100.00
CUSTOMER 2 05/20/2008 VISA 200.00
04/12/2008 WIDGET D 200.00
CUSTOMER 2 05/20/2008 VISA 290.00
ETC ......
|
