Eliminating Rows In Complex Query

Hi, been on this for more than 3 days. In the below query I am looking to look at each group of orders for each customer. Within that I only want the product type of 197 which is coded. What I want to do next is to eliminate any groups or partitions that contain an 'Active' status. With the remaining I only want to select a record if it has an expiration given by the date parameters.

Not sure if that makes sense, but I want to get a list of customers that have no active orders and of the orders that are not active, at least one must be within the date range provided.

I figured I have to use PARTITION BY to group the customers into groups in order to work on them separately. I guess the trick here is to be able to eliminate the groups as a whole that fail any criteria.

Been 4 days already and I continue to play:

CURSOR zp_expired_crs(p_start_date DATE, p_end_date DATE) IS SELECT DISTINCT(customer_id), stat FROM (   SELECT DISTINCT(customer_id), SUM(DECODE(ol.status,'Active',1,0)) stat, DENSE_RANK()
  OVER (
    PARTITION BY customer_id ORDER BY customer_id) stuff   FROM engine.order_line ol, engine.customer_order co, engine.product p
  WHERE ol.order_id = co.order_id
    AND ol.product_id = p.product_id
    AND p.subproduct_id = 197
    AND p.subproduct_id NOT IN (SELECT subproduct_id FROM customer.trading_services_lookup)

    AND co.customer_id NOT IN (SELECT customer_id FROM subscriptions WHERE status = 1)

    AND CALC_EXPIRATION_DATE(order_date, quantity, quantity_uom) BETWEEN p_start_date AND p_end_date
  GROUP BY customer_id
  HAVING SUM(DECODE(ol.status,'Active',1,0)) = 0); Received on Fri Oct 17 2008 - 11:26:25 CDT