List of concurrent request – its count by day, week and Month

select USER_CONCURRENT_PROGRAM_NAME,REQUEST_DATE,CNTPERDAY,week_no, case when week_no=1 then sum(CNTPERDAY) OVER (PARTITION BY USER_CONCURRENT_PROGRAM_NAME,week_no) else 0 end week_no_1,
case when week_no=2 then sum(CNTPERDAY) OVER (PARTITION BY USER_CONCURRENT_PROGRAM_NAME,week_no) else 0 end week_no_2
,case when week_no=3 then sum(CNTPERDAY) OVER (PARTITION BY USER_CONCURRENT_PROGRAM_NAME,week_no) else 0 end week_no_3
,case when week_no=4 then sum(CNTPERDAY) OVER (PARTITION BY USER_CONCURRENT_PROGRAM_NAME,week_no) else 0 end week_no_4
,case when week_no=5 then sum(CNTPERDAY) OVER (PARTITION BY USER_CONCURRENT_PROGRAM_NAME,week_no) else 0 end week_no_5
–,case when week_no in(1,2,3,4,5) then sum(week_no_1+week_no_2+week_no_3+week_no_4+week_no_5) OVER (PARTITION BY USER_CONCURRENT_PROGRAM_NAME,week_no) else 0 end Montdata
,sum(CNTPERDAY)OVER (PARTITION BY USER_CONCURRENT_PROGRAM_NAME) Permonth
from(
select  USER_CONCURRENT_PROGRAM_NAME,REQUEST_DATE,CNTPERDAY , case when to_Char(REQUEST_DATE,’W’)=1 then
1
 when to_Char(REQUEST_DATE,’W’)=2 then
2
 when to_Char(REQUEST_DATE,’W’)=3 then
3
 when to_Char(REQUEST_DATE,’W’)=4 then
4
 when to_Char(REQUEST_DATE,’W’)=5 then
5
else
0
end week_no
from(
select PT.USER_CONCURRENT_PROGRAM_NAME USER_CONCURRENT_PROGRAM_NAME,trunc(R.REQUEST_DATE) REQUEST_DATE , count(*) CNTPERDAY
from FND_CONCURRENT_PROGRAMS_TL pt,FND_CONCURRENT_REQUESTS R
where PT.LANGUAGE = USERENV(‘LANG’)
and PT.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
and trunc(R.REQUEST_DATE) between :fromdate and :todate
–group by
GROUP by PT.USER_CONCURRENT_PROGRAM_NAME,trunc(R.REQUEST_DATE)))
–group by  USER_CONCURRENT_PROGRAM_NAME,REQUEST_DATE,week_no,CNTPERDAY
order by 1,2; 
  • September 21, 2018 | 26 views
  • Comments