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 35 36 37 38 39 40 41
| SELECT DISTINCT TB1.THEME_LIBELLE,
TB1.FER_ID,
TB1.FER_LIBELLE,
TB1.AFFAIRE_ID,
TB1.ACTIVITE_ID,
TB1.THEME_ID,
CASE WHEN TB1.EVAL_ID <> -1 THEN TB2.EVAL_ID ELSE -1 END AS EVAL_ID,
ISNULL(TB2.NUMERATEUR, 0) AS NUMERATEUR,
ISNULL(TB2.DENOMINATEUR, 1) AS DENOMINATEUR
FROM (SELECT TBL_THEME.THEME_LIBELLE, TBL_FER.FER_ID, TBL_FER.FER_LIBELLE, LNK_AFFAIRE_EVAL_ACTIVITE.AFFAIRE_ID,
LNK_AFFAIRE_EVAL_ACTIVITE.ACTIVITE_ID, TBL_THEME.THEME_ID, ISNULL(TBL_AUTORIZE_PA.EVAL_ID, -1) AS EVAL_ID
FROM PER4_Monde_Dev.dbo.LNK_AFFAIRE_EVAL_ACTIVITE LNK_AFFAIRE_EVAL_ACTIVITE INNER JOIN
PER4_Monde_Dev.dbo.LNK_ACTIVITE_FER LNK_ACTIVITE_FER ON LNK_AFFAIRE_EVAL_ACTIVITE.ACTIVITE_ID = LNK_ACTIVITE_FER.ACTIVITE_ID INNER JOIN
PER4_Monde_Dev.dbo.LNK_THEME_FER LNK_THEME_FER ON LNK_ACTIVITE_FER.FER_ID = LNK_THEME_FER.FER_ID INNER JOIN
PER4_Monde_Dev.dbo.TBL_THEME TBL_THEME ON LNK_THEME_FER.THEME_ID = TBL_THEME.THEME_ID INNER JOIN
PER4_Monde_Dev.dbo.TBL_FER TBL_FER ON LNK_ACTIVITE_FER.FER_ID = TBL_FER.FER_ID LEFT OUTER JOIN
PER4_Monde_Dev.dbo.TBL_AUTORIZE_PA TBL_AUTORIZE_PA ON TBL_AUTORIZE_PA.FER_ID = TBL_FER.FER_ID AND TBL_AUTORIZE_PA.AFFAIRE_ID = '75614224'
WHERE LNK_AFFAIRE_EVAL_ACTIVITE.EVAL_ID IN (-1, 8394, 8395)
AND TBL_FER.LANGUE_ID = 1
AND TBL_THEME.LANGUE_ID = 1) AS TB1
LEFT OUTER JOIN
(SELECT DISTINCT LAF.ACTIVITE_ID, LAEA.AFFAIRE_ID, LTF.THEME_ID, TF.FER_ID, TF.FER_LIBELLE, TT.THEME_LIBELLE,
SUM(CAST(REPLACE(TRE.PROPOSITION_ID, '2', '0') AS REAL)) AS NUMERATEUR,
SUM(CAST(REPLACE(TRE.PROPOSITION_ID, '2', '1') AS REAL)) AS DENOMINATEUR,
ISNULL(MAX(TAP.EVAL_ID), -1) AS EVAL_ID
FROM PER4_Monde_Dev.dbo.LNK_AFFAIRE_EVAL_ACTIVITE LAEA INNER JOIN
PER4_Monde_Dev.dbo.TBL_RESULTAT_EVALUATION TRE ON LAEA.EVAL_ID = TRE.EVAL_ID INNER JOIN
PER4_Monde_Dev.dbo.LNK_FER_QUESTION LFQ ON TRE.QUESTION_ID = LFQ.QUESTION_ID INNER JOIN
PER4_Monde_Dev.dbo.TBL_FER TF ON LFQ.FER_ID = TF.FER_ID INNER JOIN
PER4_Monde_Dev.dbo.LNK_THEME_FER LTF ON TF.FER_ID = LTF.FER_ID INNER JOIN
PER4_Monde_Dev.dbo.LNK_ACTIVITE_FER LAF ON LAF.FER_ID = TF.FER_ID INNER JOIN
PER4_Monde_Dev.dbo.TBL_THEME TT ON TT.THEME_ID = LTF.THEME_ID LEFT OUTER JOIN
PER4_Monde_Dev.dbo.TBL_AUTORIZE_PA TAP ON (TAP.AFFAIRE_ID = '75614224' AND (TAP.EVAL_ID IN (-1, 8394, 8395) OR TAP.EVAL_ID IS NULL)AND TAP.FER_ID = TF.FER_ID)
WHERE (LAEA.AFFAIRE_ID = '75614224')
AND (TF.LANGUE_ID = 1)
AND LAEA.EVAL_ID IN (-1, 8394, 8395)
AND TT.LANGUE_ID = 1
GROUP BY LAEA.EVAL_ID, TF.FER_ID, TF.FER_LIBELLE, LAF.ACTIVITE_ID, LAEA.AFFAIRE_ID, LTF.THEME_ID, TT.THEME_LIBELLE) AS TB2
ON TB1.FER_ID = TB2.FER_ID
WHERE 1 = 1
ORDER BY TB1.FER_ID |
Partager