2 Table Question

From: Mtek <mtek_at_mtekusa.com>
Date: Mon, 15 Sep 2008 13:28:30 -0700 (PDT)
Message-ID: <4ed3fde0-a49c-4e65-b2bd-65e95058d328@b1g2000hsg.googlegroups.com>

This is really messed up.

We have a history table below and the report & query that we use:

HISTORY

---

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


DATE      ID    PRODUCT       ADD     DELETE   DIFFERENCE TOTAL
01-JAN-08 PP    Product #1      3          1        2      NN
01-FEB-08 HL    Product #2      1          0        1      NN
01-MAR-08 ZI    Product #3      1          0        1      NN
01-APR-08 ZI    Product #3      0          1       -1      NN


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; Now, we have an active table that looks like this:

ACTIVES

---

ID PRODUCT
12345 PP
56789 PP
43657 HL
54600 ZI

The relationship is that when a new ID & Product is added to this table, it is also
added to the history table with an 'A' action. When a member removes himself from the active table, they get a 'D'elete in the history table.

What they want is the find out how many 'A'ctives there were each month and add that
as the last column. Since there is no date column in the active table, I think we'd
have to write something where it looks at the history table, and for each month, add the
members who do NOT have a 'D'elete in the same month.......and are still on the active
list.

Can all this be done in a query? Or will code need to be written? Received on Mon Sep 15 2008 - 15:28:30 CDT