Backward Reference
Date: Mon, 15 Sep 2008 13:39:38 -0700 (PDT)
Message-ID: <6f622fa2-64ba-468e-a573-53891e2e1d07@34g2000hsh.googlegroups.com>
Hi,
Say I have a history table ike this:
ID PRODUCT DATE ACTION 12345 PP 01/01/2008 A 12345 PP 01/06/2008 D 33333 PP 01/01/2008 A 56789 PP 01/09/2008 A 43657 HL 02/01/2008 A 54600 ZI 03/01/2008 A 54600 ZI 04/01/2008 D
I want to find out how many 'A'ctive members there are for a given product. That means I have to, for each member, look at their history and make sure that the MAX record is an A. They could have come into the product in 2005 or 2008, but as long as their latest entry is an 'A'.
What messes this up is that they want it as part of the report below as the last column:
DATE ID PRODUCT ADD DELETE DIFFERENCE 01-JAN-08 PP Product #1 3 1 2 01-FEB-08 HL Product #2 1 0 1 01-MAR-08 ZI Product #3 1 0 1 01-APR-08 ZI Product #3 0 1 -1
The query which populates that is this:
select TRUNC(action_date,'MM'), product_id,
DECODE(product_id,'PP','Product #1','HL','Product
#2','ZI','Product #3''Unknown') "Product",
sum(decode(action,'A',1, 0 )) "Add",
sum(decode(action,'D',1, 0 )) "Delete",
(sum(decode(action,'A',1, 0 ))) - (sum(decode(action,'D',1,
0 ))) "Difference",
(select sum(DECODE(product_id,'PP',1,'HL',1,'ZI',1)) "Total"
from products)
from products
group by TRUNC(action_date,'MM'), product_id,
DECODE(product_id,'PP','Product #1','HL','Product
#2','ZI','Product #3''Unknown')
ORDER BY 1, 2;
So, it seems that I need to go through each member, check their
history, and add to a counter, while the other part of the report is a
simple query.......
Can all this be done within the same query, or are we talking code at this point?
Much much thanks! Received on Mon Sep 15 2008 - 15:39:38 CDT