150 lines
6.7 KiB
SQL
150 lines
6.7 KiB
SQL
column counted format 9G999G999
|
|
|
|
select to_number(to_char(senddate, 'YYYY')) year, to_number(to_char(senddate, 'WW')) week, count(*) counted
|
|
from stats_200804
|
|
where notification_group in
|
|
( 'bvp-sms', 'bvp-sms-bus', 'bvp-sms-nonbus', 'dba-standby-bus', 'dba-standby-nonbus',
|
|
'dba-standby-nonbus-nowarn', 'hosting-standby-bus', 'hosting-standby-nonbus',
|
|
'janssst-sms-nonbus', 'liekejo-sms-bus', 'liekejo-sms-nonbus', 'network-standby-bus',
|
|
'network-standby-nonbus', 'spruyma-bus-sms', 'spruyma-nonbus-sms', 'spruyma-standby-bus',
|
|
'spruyma-standby-nonbus', 'vanropi-bus-sms', 'vanropi-nonbus-sms', 'vermema-sms-nonbus',
|
|
'win-standby-bus', 'win-standby-nonbus'
|
|
)
|
|
and senddate >= add_months(trunc(sysdate, 'MM'), -6)
|
|
group by to_number(to_char(senddate, 'YYYY')), to_number(to_char(senddate, 'WW'))
|
|
order by year, week;
|
|
|
|
|
|
|
|
set linesize 120
|
|
set pages 9999
|
|
|
|
column counted format 9G999G999
|
|
column notification_group format a50
|
|
break on begin_date skip 1 on end_date
|
|
compute sum of counted on begin_date
|
|
|
|
select trunc(senddate, 'MM') begin_date, add_months(trunc(senddate, 'MM'), 1) - 1 end_date, notification_group, count(*) counted
|
|
from stats_200804
|
|
where notification_group in
|
|
( 'bvp-sms', 'bvp-sms-bus', 'bvp-sms-nonbus', 'dba-standby-bus', 'dba-standby-nonbus',
|
|
'dba-standby-nonbus-nowarn', 'hosting-standby-bus', 'hosting-standby-nonbus',
|
|
'janssst-sms-nonbus', 'liekejo-sms-bus', 'liekejo-sms-nonbus', 'network-standby-bus',
|
|
'network-standby-nonbus', 'spruyma-bus-sms', 'spruyma-nonbus-sms', 'spruyma-standby-bus',
|
|
'spruyma-standby-nonbus', 'vanropi-bus-sms', 'vanropi-nonbus-sms', 'vermema-sms-nonbus',
|
|
'win-standby-bus', 'win-standby-nonbus'
|
|
)
|
|
and senddate >= add_months(trunc(sysdate, 'MM'), -6)
|
|
group by notification_group, trunc(senddate, 'MM')
|
|
order by begin_date, notification_group;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
set linesize 120
|
|
set pages 9999
|
|
|
|
column counted format 9G999G999
|
|
column month_sum format 9G999G999
|
|
column pct format 990D99
|
|
column notification_group format a50
|
|
break on begin_date skip 1 on end_date skip 1
|
|
compute sum of counted on begin_date
|
|
|
|
select sendmonth begin_date, add_months(sendmonth, 1) - 1 end_date, ranking, notification_group, counted, month_sum,
|
|
(100/month_sum) * counted pct
|
|
from ( select sendmonth, notification_group, counted,
|
|
dense_rank () over ( partition by sendmonth order by counted desc) ranking,
|
|
sum(counted) over (partition by sendmonth) month_sum
|
|
from ( select trunc(senddate, 'MM') sendmonth, notification_group, count(*) counted
|
|
from stats_200804
|
|
where notification_group in
|
|
( 'bvp-sms', 'bvp-sms-bus', 'bvp-sms-nonbus', 'dba-standby-bus', 'dba-standby-nonbus',
|
|
'dba-standby-nonbus-nowarn', 'hosting-standby-bus', 'hosting-standby-nonbus',
|
|
'janssst-sms-nonbus', 'liekejo-sms-bus', 'liekejo-sms-nonbus', 'network-standby-bus',
|
|
'network-standby-nonbus', 'spruyma-bus-sms', 'spruyma-nonbus-sms', 'spruyma-standby-bus',
|
|
'spruyma-standby-nonbus', 'vanropi-bus-sms', 'vanropi-nonbus-sms', 'vermema-sms-nonbus',
|
|
'win-standby-bus', 'win-standby-nonbus'
|
|
)
|
|
and senddate >= add_months(trunc(sysdate, 'MM'), -6)
|
|
group by notification_group, trunc(senddate, 'MM')
|
|
)
|
|
)
|
|
where ranking <= 10
|
|
order by sendmonth, ranking, notification_group;
|
|
|
|
|
|
|
|
|
|
set linesize 120
|
|
set pages 9999
|
|
|
|
column counted format 9G999G999
|
|
column month_sum format 9G999G999
|
|
column pct format 990D99
|
|
column clientname format a30
|
|
break on begin_date skip 1 on end_date skip 1
|
|
compute sum of counted on begin_date
|
|
|
|
select sendmonth begin_date, add_months(sendmonth, 1) - 1 end_date, ranking, clientname, counted, month_sum,
|
|
(100/month_sum) * counted pct
|
|
from ( select sendmonth, clientname, counted,
|
|
dense_rank () over ( partition by sendmonth order by counted desc) ranking,
|
|
sum(counted) over (partition by sendmonth) month_sum
|
|
from ( select trunc(senddate, 'MM') sendmonth, clientname, count(*) counted
|
|
from stats_200804
|
|
where notification_group in
|
|
( 'bvp-sms', 'bvp-sms-bus', 'bvp-sms-nonbus', 'dba-standby-bus', 'dba-standby-nonbus',
|
|
'dba-standby-nonbus-nowarn', 'hosting-standby-bus', 'hosting-standby-nonbus',
|
|
'janssst-sms-nonbus', 'liekejo-sms-bus', 'liekejo-sms-nonbus', 'network-standby-bus',
|
|
'network-standby-nonbus', 'spruyma-bus-sms', 'spruyma-nonbus-sms', 'spruyma-standby-bus',
|
|
'spruyma-standby-nonbus', 'vanropi-bus-sms', 'vanropi-nonbus-sms', 'vermema-sms-nonbus',
|
|
'win-standby-bus', 'win-standby-nonbus'
|
|
)
|
|
and senddate >= add_months(trunc(sysdate, 'MM'), -6)
|
|
group by clientname, trunc(senddate, 'MM')
|
|
)
|
|
)
|
|
where ranking <= 10
|
|
order by sendmonth, ranking, clientname;
|
|
|
|
|
|
|
|
|
|
|
|
set linesize 140
|
|
set pages 9999
|
|
|
|
column counted format 9G999G999
|
|
column month_sum format 9G999G999
|
|
column pct format 990D99
|
|
column clientname format a30
|
|
column systemname format a30
|
|
break on begin_date skip 1 on end_date skip 1
|
|
compute sum of counted on begin_date
|
|
|
|
select sendmonth begin_date, add_months(sendmonth, 1) - 1 end_date, ranking, clientname, systemname, counted, month_sum,
|
|
(100/month_sum) * counted pct
|
|
from ( select sendmonth, clientname, systemname, counted,
|
|
dense_rank () over ( partition by sendmonth order by counted desc) ranking,
|
|
sum(counted) over (partition by sendmonth) month_sum
|
|
from ( select trunc(senddate, 'MM') sendmonth, clientname, systemname, count(*) counted
|
|
from stats_200804
|
|
where notification_group in
|
|
( 'bvp-sms', 'bvp-sms-bus', 'bvp-sms-nonbus', 'dba-standby-bus', 'dba-standby-nonbus',
|
|
'dba-standby-nonbus-nowarn', 'hosting-standby-bus', 'hosting-standby-nonbus',
|
|
'janssst-sms-nonbus', 'liekejo-sms-bus', 'liekejo-sms-nonbus', 'network-standby-bus',
|
|
'network-standby-nonbus', 'spruyma-bus-sms', 'spruyma-nonbus-sms', 'spruyma-standby-bus',
|
|
'spruyma-standby-nonbus', 'vanropi-bus-sms', 'vanropi-nonbus-sms', 'vermema-sms-nonbus',
|
|
'win-standby-bus', 'win-standby-nonbus'
|
|
)
|
|
and senddate >= add_months(trunc(sysdate, 'MM'), -3)
|
|
group by clientname, trunc(senddate, 'MM'), systemname
|
|
)
|
|
)
|
|
where ranking <= 20
|
|
order by sendmonth, ranking, clientname;
|