1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| select to_char(startdate,'dd/mm/yyyy hh24:mi:ss') as Time, ((1- (s1 + s2 + s3)/ (s4 + s5 + s6))*100) as succ_rate
from
(
select MGW, startdate, sum(v1) as s1, sum(v2) as s2, sum(v3) as s3, sum(v4) as s4, sum(v5) as s5, sum(v6) as s6
from (
select MGW, startdate, avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfAal2TermsRej' then valeur else null end) as v1,
avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfIpTermsRej' then valeur else null end) as v2,
avg(case when indicateur like '%TdmTermGrp%' and compteur = 'pmNrOfTdmTermsRej' then valeur else null end) as v3,
avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfAal2TermsReq' then valeur else null end) as v4,
avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfIpTermsReq' then valeur else null end) as v5,
avg(case when indicateur like '%TdmTermGrp%' and compteur = 'pmNrOfTdmTermsReq' then valeur else null end) as v6
from data where to_char(startdate,'dd/mm/yyyy hh24:mi:ss') like$P{date_debut}
and MGW like $P{par_mgw}
group by startdate, MGW)
group by startdate, MGW)
order by startdate asc |
Partager