Category: PIPER-Rx Reports (Free) Name: FUA006-10 SS User Activity by Connect Time (period) ReportDefFileName: .\FUA006-10 SS User Activity by Connect Time (period).fr3 ===Begin Comments=== Copyright (C) 2009 G Piper This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details. a copy of the GNU Affero General Public Licenses provided is part of the down load Terms and Conditions available on our site www.piper-rx.com or you can refere to www.gnu.org/licenses/gpl.html ===End Comments=== ===Begin SQL=== -- **************************************************** -- ** PIPER-RX - Free Reports -- ** -- ** Copyright (C) 2009 G.Piper -- ** -- ** Self service activity by connect time -- ** -- ** ------------------------------------------------- -- ** Version 1.0 Jul-09 G.Piper Initial Creation -- **************************************************** SELECT upper(:START_DATE) start_date, upper(:END_DATE) end_date, database_name.db_sid, fu.user_name, fu.description, round(sum(icxsess.last_connect - icxsess.first_connect) * 24,1) connect_time, round((sum(icxsess.last_connect - icxsess.first_connect) * 24) * 100 / decode(totals.total_hours, 0, .0000001, totals.total_hours ),1) connect_time_pct_of_tot, count(*) number_of_sessions, round((count(*) * 100 / totals.total_connects),1) num_sessions_pct_of_tot, sum(icxsess.counter) num_pages, round((sum(icxsess.counter) * 100 / totals.total_counter),1) num_pages_pct_of_tot FROM icx.icx_sessions icxsess, applsys.fnd_user fu, ( SELECT sum(icxsessb.last_connect - icxsessb.first_connect) * 24 total_hours, count(*) total_connects, decode(sum(icxsessb.counter), 0, .0000001,sum(icxsessb.counter) ) total_counter FROM icx.icx_sessions icxsessb WHERE icxsessb.first_connect >= to_date(upper(:START_DATE), 'DD-MON-YY HH24:MI') and icxsessb.first_connect <= to_date(upper(:END_DATE), 'DD-MON-YY HH24:MI') ) totals, ( SELECT vd.name db_sid FROM gv$database vd WHERE vd.inst_id = 1) database_name WHERE icxsess.user_id = fu.user_id and icxsess.first_connect >= to_date(upper(:START_DATE), 'DD-MON-YY HH24:MI') and icxsess.first_connect <= to_date(upper(:END_DATE), 'DD-MON-YY HH24:MI') GROUP by database_name.db_sid, fu.user_name, fu.description, totals.total_hours, totals.total_connects, totals.total_counter ORDER by 6 desc ===End SQL=== ParamName: START_DATE ParamType: String ParamValue: 01-Jul-08 00:00 ParamExp: ParamName: END_DATE ParamType: String ParamValue: 31-Jul-09 23:59 ParamExp: ===Begin Report Definition===