| Maximum concurrent session per day [message #593978] |
Thu, 22 August 2013 06:42  |
 |
sureshram24
Messages: 13
Registered: August 2013
Location: Bangalore
|
Junior Member |
|
|
Hi All,
Could you please help me how can i write pl/sq to find maximum concurrent session on day basis?
I tried writing below code but throws an unique constraint violated error when executing.
Below is the snippet of the code from pl/sql.
cursor summ_1 is
select trunc(start_ts,'DDD') start_ts,count(distinct(user_cupid)) user_cnt,count(SESS_ID) Sess_cnt
from PCM_SESSION_SUMMARY
where START_TS < sysdate or START_TS > (select max(sess_dt) from TEST_SESS_SUMM_DAILY where sess_dt < sysdate)
group by trunc(start_ts,'DDD')
order by start_ts;
summ_rec1 summ_1%rowtype;
cursor summ_2 is
select distinct trunc(start_ts,'hh24') as start_dt,
trunc(end_ts,'hh24') as end_dt,trunc(start_ts,'DDD') start_t,count(SESS_ID) sess_cn
from PCM_SESSION_SUMMARY
where trunc(start_ts,'ddd')= summ_rec1.start_ts
group by trunc(start_ts,'hh24'),trunc(end_ts,'hh24'),trunc(start_ts,'DDD')
order by 1;
summ_rec2 summ_2%rowtype;
begin
open summ_1;
loop
fetch summ_1 into summ_rec1;
exit when summ_1%NOTFOUND;
open summ_2;
loop
fetch summ_2 into summ_rec2;
exit when summ_2%NOTFOUND;
insert into TEST_SESS_SUMM_DAILY values (summ_rec1.start_ts,summ_rec1.user_cnt,summ_rec1.Sess_cnt,summ_rec2.sess_cn);
|
|
|
|
|
|
| Re: Maximum concurrent session per day [message #593987 is a reply to message #593978] |
Thu, 22 August 2013 06:58  |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
From your previous topic:
BlackSwan wrote on Tue, 13 August 2013 16:03Please read and follow the forum guidelines, to enable us to help you:
http://www.orafaq.com/forum/t/88153/0/
Michel Cadot wrote on Tue, 13 August 2013 16:27Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Regards
Michel
What don't you understand in "ORA-00001: unique constraint (G3COST.SYS_C00687927) violated"?
Note that we have not your table(s) and constraint(s) and so can't help you more.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
