1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
| WITH
MONSELECT AS(
select
count(resultat.resultatid) as population,
mesure.mesureCode,
mesure.mesureDesc,
etape.etapeCode,
etape.etapeDesc,
moyen.moyenid,
moyen.moyenname,
ROW_NUMBER() OVER (PARTITION BY mesure.mesureCode,etape.etapeCode ORDER BY moyen.moyenName) rn,
COUNT(*) OVER (PARTITION BY mesure.mesureCode,etape.etapeCode) cnt
from GWTMESURE mesure
LEFT OUTER JOIN GWTETAPE etape on mesure.EtapeID=etape.EtapeId
LEFT OUTER JOIN GWTRESULTATTEST resultat on etape.ResultatId=resultat.ResultatId
LEFT OUTER JOIN GWTMOYEN moyen on resultat.moyenId=moyen.moyenId
LEFT OUTER JOIN GWTNUMSERIE numSerie on resultat.NumSerieId=numSerie.NumSerieId
WHERE moyen.FactoryId='1'
AND numSerie.ArticleId NOT IN(SELECT bl.articleId FROM GWRUSINENOTARTICLE bl WHERE FactoryId='1')
AND mesure.mesurecode!='STATUT' AND mesure.mesurecode!='DUREE'
GROUP BY moyen.moyenid,moyen.moyenname,mesure.mesureCode,mesure.mesureDesc,etape.etapeCode,etape.etapeDesc
ORDER BY mesure.mesureCode,etape.etapeCode
),
FLT_JOIN AS (
SELECT DISTINCT mesureCode,mesureDesc,etapeCode,etapeDesc,LTRIM(SYS_CONNECT_BY_PATH(moyenname,','),',') moyenname
FROM MONSELECT
WHERE rn=cnt
START WITH rn=1
CONNECT BY PRIOR mesureCode = mesureCode AND etapeCode=etapeCode AND PRIOR rn = rn-1
)
SELECT FLT_JOIN.mesureCode,FLT_JOIN.mesureDesc,FLT_JOIN.etapeCode,FLT_JOIN.etapeDesc,FLT_JOIN.moyenname,SUM(MONSELECT.population) population
FROM FLT_JOIN
JOIN MONSELECT ON (FLT_JOIN.mesureCode=MONSELECT.mesureCode AND FLT_JOIN.etapeCode=MONSELECT.etapeCode)
GROUP BY FLT_JOIN.mesureCode,FLT_JOIN.mesureDesc,FLT_JOIN.etapeCode,FLT_JOIN.etapeDesc,FLT_JOIN.moyenname |
Partager