select t.dt "Hour" ,t.cnt "Today Views" ,round(t.av,2) "Avg time",
l.cnt "2 Week Ago Views",round(l.av,2) "2 Week Ago Avg" ,
case when (t.cnt-l.cnt) > 0 then '<span style="color:green;">'||round((l.cnt/t.cnt)*100) ||'% ('||(t.cnt-l.cnt)||')</span>' else '<span style="color:red;">-'||round((l.cnt/t.cnt)*100) ||'% ('||(t.cnt-l.cnt)||')</span>' end "Count Diff" ,
case when (t.av - l.av) > 0 then '<span style="color:red;">-'||round((l.av/t.av)*100) ||'% ('||(round((t.av - l.av),2))||')</span>' else '<span style="color:green;">'||round((l.av/t.av)*100)|| '% ('||(round((t.av - l.av),2))||')</span>' end "Avg Diff"
from (
select to_char(time_stamp,'HH24') dt,count(1) cnt,avg(elap) av
from apex_030200.wwv_flow_activity_log
where time_stamp > sysdate - 1
group by to_char(time_stamp,'HH24')
) t,
( select to_char(time_stamp,'HH24') dt,count(1) cnt ,avg(elap) av
from apex_030200.wwv_flow_activity_log
where time_stamp between sysdate -14 -1 and sysdate -14
group by to_char(time_stamp,'HH24')
) l
where t.dt = l.dt
order by 1
Here's the results of the query when run for apex.oracle.com this morning. From these measure points, it appears what we're doing is working.
No comments:
Post a Comment