Prev: SQL Server 2005: functions become slow after a while
Next: Get rows whose sum matches a value
From: markgordon_007 on 17 Apr 2008 09:09
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 ......
From: --CELKO-- on 17 Apr 2008 12:29
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules, so that people can read your posting
and understand it (i.e names like Table_A is pretty useless). Sample
data is also a good idea, along with clear specifications. It is very
hard to debug code when you do not let us see it. If you want to
learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html <<

From: Ed Murphy on 18 Apr 2008 10:08
--CELKO-- wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. If you know how, follow ISO-11179 data element naming
> conventions and formatting rules, so that people can read your posting
> and understand it (i.e names like Table_A is pretty useless). Sample
> data is also a good idea, along with clear specifications. It is very
> hard to debug code when you do not let us see it. If you want to
> learn how to ask a question on a Newsgroup, look at:
> http://www.catb.org/~esr/faqs/smart-questions.html <<

While the customer table is indeed a couple types of unclear, it doesn't
appear to be directly relevant. From context, the specifications are as
follows:

1) Negative charges are applied to positive charges for the same
customer and item (earliest first on both sides), canceling
them out in whole or part.

2) Negative payments are applied to positive payments for the same
customer (earliest first on both sides), canceling them out in
whole or part.

3) Uncanceled payments are applied to uncanceled charges for the same
customer (earliest first on both sides) and displayed in
interleaved order, including unapplied payment amounts (if any).

Personally, I would do most of the work at the reporting layer, but I
suppose you could get some mileage out of something like the following:

create view customer_activity as
select customer_id,
transaction_date,
item_id,
total_price as amount,
'Charge' as transaction_type
from customer_charges
union
select customer_id,
transaction_date,
null as item_id,
payment_amount as amount,
'Payment' as payment_type
from customer_payments
 | 
Pages: 1
Prev: SQL Server 2005: functions become slow after a while
Next: Get rows whose sum matches a value