Backward Reference

From: Mtek <>
Date: Mon, 15 Sep 2008 13:39:38 -0700 (PDT)
Message-ID: <>


Say I have a history table ike this:

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:

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

Original text of this message