Query Question

From: Mtek <mtek_at_mtekusa.com>
Date: Tue, 16 Sep 2008 07:50:03 -0700 (PDT)
Message-ID: <19ebd72d-30b7-4d6a-9772-35cb935fb5c2@r66g2000hsg.googlegroups.com>

I have this query. It works fine and does what I want:

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, I need to add one column to the end. The criteria for that column is: How many customers were active on the list during a certain time period (each month) and are still active for each product.

So for this guy for HL, he was active on March 9, 2004 and stayed active until December 3, 2004. Then he because active again on September 29, 2005 and is still active.

So, during the months that this customer was active, he gets included in the count.
This needs to be intgrated into the above query......possible at all????

AC DATE CUSTOMER_ID PRODUCT

-- --------- ---------- -----
A  09-MAR-04 1072604574 HL
D  03-DEC-04 1072604574 HL
A  29-SEP-05 1072604574 HL
A  13-OCT-03 1072604574 PP
D  10-SEP-04 1072604574 PP
A  13-OCT-03 1072604574 PP1
D  10-SEP-04 1072604574 PP1
A  10-SEP-04 1072604574 PPM
D  14-MAY-05 1072604574 PPM
A  29-SEP-05 1072604574 PPM
D  11-NOV-05 1072604574 PPM
D  26-NOV-05 1072604574 PPM

Received on Tue Sep 16 2008 - 09:50:03 CDT