--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 re****ting 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
|
