2 Table Query

From: Mtek <mtek_at_mtekusa.com>
Date: Mon, 15 Sep 2008 13:08:15 -0700 (PDT)
Message-ID: <b55ce1e4-f066-429d-809f-44f13b087f1c@l43g2000hsh.googlegroups.com>

Hi,

This is really messed up I think......

Here is my data of active data. Only an ID and Product Code.

ACTIVES

---

ID PRODUCT
12345 PP
56789 PP
43657 HL
54600 ZI

Here is the product history. The ID, Product, Date and Action Taken

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

Here is the report and the SQL I used. The active table takes no part in this.

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

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 they want a count, by product code, from the active table for each product as the last column of the report.

Not sure how to join these, as there is a 1 to many(+) regarding the Active & History tables. And in reality, one does not really have to do with the other in terms of content.

The history table is showing us the history behind each product, there could be many entries. The active table only has one ID,Product combination......So a count by each product gives us the total.....but how do I join that and get it into the report? The total column comes from a different table.....

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

Received on Mon Sep 15 2008 - 15:08:15 CDT