I need to report new logons by service name, and the output makes me
suspect I am doing something wrong. Here is the query I designed, can
anyone tell me if I have a logic problem with the query, and if so what I
need to do:
select h.snap_id, h.instance_number,
to_char(s.begin_interval_time,'dd-MON-yyyy hh24mi') begin_time,
h.service_name, h.value, sum(h1.value)-sum(h.value) new_logons
from dba_hist_service_stat h, dba_hist_service_stat h1, dba_hist_snapshot s
where s.snap_id between 95000 and 96123
and s.snap_id=h.snap_id
and s.dbid=h.dbid
and s.instance_number=h.instance_number
and h.stat_name='logons cumulative'
and h1.snap_id=h.snap_id+1
and h.dbid=h1.dbid
and h.instance_Number=h1.instance_number
and h.service_name=h1.service_name
and h.service_name_hash=h1.service_name_hash
and h.stat_id=h1.stat_id)
group by h.snap_id, h.instance_number,
to_char(s.begin_interval_time,'dd-MON-yyyy hh24mi'),h.service_name, h.value
order by h.service_name, h.instance_Number, begin_time;
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
