Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Special Report SQL Query

781721Apr 25 2011 — edited Apr 26 2011
Hi there

I have a log table looking like: 
ACTION     DATE_TIME     
1          31.03.2011   (Don't count because last month)
1          01.04.2011   (Count as new)
2          01.04.2011   (Count as new)
1          04.04.2011   (Count as new because there are at least 2 days since last new ACTION=1) 
1          05.04.2011   (Don't count because since last new ACTION=1 there are not 2 days in between)
2          07.04.2011   (Count as new because there are at least 2 days since last new ACTION=2)
2          08.04.2011   (Don't count because since last new ACTION=2 there are not 2 days in between)
2          10.04.2011   (Count as new because there are at least 2 days since last new ACTION=2)
I need to count the number of ACTIONs within one calendar month (April 2011 in the example above).
The difficulty is that I only have to count them as new action when there are at least 2 days between the first counted action and the next one.

So in the case of the example above I'd need to get as result: 
NEW_ACTION  COUNT
1           2
2           3
Thanks,
Peter

Comments

Centinul
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
;
Aketi Jyuuzou 
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
781721
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
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 24 2011
Added on Apr 25 2011
#oracle, #pl-sql, #sql
3 comments
361 views