Java EE (Java Enterprise Edition) General Discussion

Give this a shot:

SELECT action
     , COUNT(*) AS cnt
FROM   ( SELECT action
              , date_time - LAG(date_time,1,date_time - 2) OVER (PARTITION BY action ORDER BY date_time) AS time_diff
         FROM   log_table
         /* Month filtering here */
         WHERE  date_time BETWEEN TO_DATE('04/01/2011','MM/DD/YYYY') AND TO_DATE('04/30/2011','MM/DD/YYYY')
       )
WHERE  time_diff >= 2
GROUP BY action
ORDER BY action
;

with t(ACTION,DATE_TIME) as(
select 1,date '2011-03-31' from dual union all
select 1,date '2011-04-01' from dual union all
select 2,date '2011-04-01' from dual union all
select 1,date '2011-04-04' from dual union all
select 1,date '2011-04-05' from dual union all
select 2,date '2011-04-07' from dual union all
select 2,date '2011-04-08' from dual union all
select 2,date '2011-04-10' from dual)
select ACTION as NEW_ACTION,sum(willSum) as cnt
from (select ACTION,
      case when Lag(DATE_TIME) over(partition by ACTION
                order by DATE_TIME)+2 > DATE_TIME
           then 0 else 1 end as willSum
      from t
      where trunc(sysdate,'mm') <= DATE_TIME)
group by ACTION;

NEW_ACTION  CNT
----------  ---
         1    2
         2    3

My SQL articles of OTN-Japan :-)
http://www.oracle.com/technetwork/jp/articles/otnj-sql-image3-1-323602-ja.html

Many thank you for the replies!

Probably I did not explain my problem correctly in my initial post... I tried your solutions but it only addresses only one part of my problem.

The purpose of my report query -> A user will be charged/billed for each distinct new ACTION at the begin of each month. A charged/billed ACTION is like a token that is valid for 2 days. The user then has 2 days to repeat the same action as many times as he wants (for free). Afterwards (once the token expired) he will be charged/billed again for the same next ACTION.

-> From my log table data I need to calculate how many "tokens" I need to charge/bill the user for the past month...

Maybe this can not be implemented in one single SQL query !?

Thx & cheers,
Peter