SELECT consumer_key,product_key,days_in_product,20100201 period_key FROM
(SELECT consumer_key,
  product_key,
  days_in_product,
  row_number() over ( Partition BY consumer_key order by Days_in_product DESC) row_num
FROM
  (SELECT consumer_key,
    product_key,
    SUM(no_ofdays) days_in_product
  FROM
    (SELECT pcv.consumer_key,
      pcv.product_key,
      pcv.product_consumer_valid_from,
      pcv.product_consumer_valid_to,
      DECODE (SIGN(20100201000000-product_consumer_valid_from),1,20100201000000,product_consumer_valid_from) period_start,
      DECODE (SIGN(20100228235959-product_consumer_valid_to),1,product_consumer_valid_to,20100228235959) period_end,
      CASE
        WHEN to_number(TO_CHAR(cd.activation_date,'YYYYMMDDHH24MISS')) BETWEEN 20100201000000 AND 20100228235959
        AND activation_date > to_Date(product_consumer_valid_to,'YYYYMMDDHH24MISS')
        THEN 0
        WHEN to_number(TO_CHAR(cd.activation_date,'YYYYMMDDHH24MISS')) BETWEEN 20100201000000 AND 20100228235959
        AND activation_date BETWEEN to_Date(product_consumer_valid_from,'YYYYMMDDHH24MISS') AND to_Date(product_consumer_valid_to,'YYYYMMDDHH24MISS')
        THEN
          --to_char(activation_date,'MON-YYYY')='PERIOD_ACTIVE'  and activation_date >= to_Date(product_consumer_valid_from,'YYYYMMDDHH24MISS') then
          (to_date(DECODE (SIGN(20100228235959-product_consumer_valid_to),1,product_consumer_valid_to,20100228235959),'YYYYMMDDHH24MISS') - to_date(TO_CHAR(activation_date,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS') )
        WHEN to_number(TO_CHAR(cd.activation_date,'YYYYMMDDHH24MISS')) < 20100201000000
        THEN (to_date(DECODE (SIGN(20100228235959-product_consumer_valid_to),1,product_consumer_valid_to,20100228235959),'YYYYMMDDHH24MISS') - to_Date(DECODE (SIGN(20100201000000-product_consumer_valid_from),1,20100201000000,product_consumer_valid_from),'YYYYMMDDHH24MISS') )
        WHEN to_number(TO_CHAR(cd.activation_date,'YYYYMMDDHH24MISS')) > 20100228235959
        THEN 0
        ELSE
          --unusual situation
          (to_date(DECODE (SIGN(20100228235959-product_consumer_valid_to),1,product_consumer_valid_to,20100228235959),'YYYYMMDDHH24MISS') - to_Date(DECODE (SIGN(20100201000000-product_consumer_valid_from),1,20100201000000,product_consumer_valid_from),'YYYYMMDDHH24MISS') )
      END No_ofDays
    FROM cimtran.product_consumer_validity pcv,
      consumer_dimension cd
    WHERE pcv.consumer_key           =cd.consumer_key
    AND product_consumer_valid_to   >= 20100201000000
    AND product_consumer_valid_from <= 20100228235959
      --and product_consumer_valid_from > '20090801000000'
    ORDER BY consumer_key,
      product_key,
      product_consumer_valid_from
    ) a
  GROUP BY consumer_key,
    product_key
  ORDER BY consumer_key,
    product_key
  )
) WHERE row_num=1 ;

"PLAN_TABLE_OUTPUT"
"Plan hash value: 3823907703"
" "
"--------------------------------------------------------------------------------------------------------------"
"| Id  | Operation                | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |"
"--------------------------------------------------------------------------------------------------------------"
"|   0 | SELECT STATEMENT         |                           |  4665K|   231M|       |   133K  (1)| 00:31:08 |"
"|*  1 |  VIEW                    |                           |  4665K|   231M|       |   133K  (1)| 00:31:08 |"
"|*  2 |   WINDOW SORT PUSHED RANK|                           |  4665K|   173M|   232M|   133K  (1)| 00:31:08 |"
"|   3 |    VIEW                  |                           |  4665K|   173M|       |   104K  (1)| 00:24:18 |"
"|   4 |     SORT GROUP BY        |                           |  4665K|   182M|   729M|   104K  (1)| 00:24:18 |"
"|*  5 |      HASH JOIN           |                           |    13M|   533M|    65M| 44241   (1)| 00:10:20 |"
"|   6 |       TABLE ACCESS FULL  | CONSUMER_DIMENSION        |  2657K|    35M|       |  4337   (1)| 00:01:01 |"
"|*  7 |       TABLE ACCESS FULL  | PRODUCT_CONSUMER_VALIDITY |    13M|   351M|       | 15340   (2)| 00:03:35 |"
"--------------------------------------------------------------------------------------------------------------"
" "
"Predicate Information (identified by operation id):"
"---------------------------------------------------"
" "
"   1 - filter(""ROW_NUM""=1)"
"   2 - filter(ROW_NUMBER() OVER ( PARTITION BY ""CONSUMER_KEY"" ORDER BY "
"              INTERNAL_FUNCTION(""DAYS_IN_PRODUCT"") DESC )<=1)"
"   5 - access(""PCV"".""CONSUMER_KEY""=""CD"".""CONSUMER_KEY"")"
"   7 - filter(""PRODUCT_CONSUMER_VALID_FROM""<=20100228235959 AND "
"              ""PRODUCT_CONSUMER_VALID_TO"">=20100201000000)"