select *
from ( ---zzz
SELECT X.BEROEP_CD
, b.omschrijving_l beroepnaam
, ROUND(SUM(AANT_COMP_RECIPROOK) * AVG(X.AANT_COMP), 3) GEWICHT
, X.COMPETENTIE_CD
, comp.omschrijving competentie
, decode(regcomp.beroep_cd, NULL, NULL, '*') reg
, COUNT(DISTINCT X.PERS_ID) AANT_PERS
, ROUND(AVG(X.JR_ERV),1) JR_ERV_GEM
, ROUND(AVG(X.AANT_COMP),1) AANT_COMP_GEM
FROM
( -- X
select peb.pers_id
, peb.beroep_cd
, round(peb.jr_erv, 1) jr_erv
, compcnt.aant_comp
, 1 / compcnt.aant_comp aant_comp_reciprook
, compcv.competentie_cd
from (select pers_id
, beroep_cd_reg beroep_cd
, sum(datum_tot - datum_vanaf) / 365.24 jr_erv
from wnx_cv.wnx_werkervaring
where 1=1 -- beroep_cd_reg in (4949,1000406546,1000000519,14169,1000401907,1000401909,1000406327,15133,14304,14979,1000406384)
group by pers_id, beroep_cd_reg
having sum(datum_tot - datum_vanaf) / 365.24 >= 3
) peb
, (select pers_id
, count(distinct competentie_referentie_id) aant_comp
from wnx_cv.wnx_competentie
group by pers_id
) compcnt
, (select pers_id
, competentie_referentie_id competentie_cd
from wnx_cv.wnx_competentie
where competentie_referentie_id is not null
group by pers_id, competentie_referentie_id
) compcv
where peb.pers_id = compcnt.pers_id
and peb.pers_id = compcv.pers_id
order by peb.pers_id, peb.beroep_cd, compcv.competentie_cd
) X
, reg_owner9.beroep b
, reg_owner9.competentie comp
, (select bi.beroep_cd
, bi.bcle_cd competentie_cd
from reg_owner9.beroepindeling bi
where bi.bclass_cd in (82, 89, 90, 93, 96, 102)
and bi.status_cd = 'A'
order by 1,2
) regcomp
where X.beroep_cd = b.beroep_cd
and X.competentie_cd = comp.competentie_cd
and X.beroep_cd = regcomp.beroep_cd(+)
and X.competentie_cd = regcomp.competentie_cd(+)
GROUP BY X.BEROEP_CD, X.COMPETENTIE_CD, b.omschrijving_l, comp.omschrijving, regcomp.beroep_cd
ORDER BY b.omschrijving_l, GEWICHT DESC, comp.omschrijving
) zzz
where zzz.aant_pers >= 50
|
Drempel = 50 personen
Gewicht = 1 per persoon