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
| SELECT BDR.Staff_PC AS PC
, S_JobTitles.Job_Titles
, SUM(E_STE_Activity.STE*E_Effectifs.FTE) AS Val
, S_JobTitles.Weight
FROM ( ( ( SELECT E_JobTitles.Matricule
, E_JobTitles.JobTitlesAsOf
, CASE WHEN E_JobTitles.Job_Titles IN ('Metier1', ..., 'MetierN')
THEN 'Management'
ELSE E_JobTitles.Job_Titles
END AS Job_Titles
, E_Weight.Weight
FROM E_Weight
INNER JOIN
E_JobTitles
ON E_Weight.Job_Titles = S_JobTitles.Job_Titles
WHERE EXISTS
( SELECT NULL
FROM E_Effectifs
HAVING E_JobTitles.JobTitlesAsOf = MAX(E_Effectifs.AsOf)
)
) AS S_JobTitles
INNER JOIN
E_Effectifs
ON E_Effectifs.Employee_CD = S_JobTitles.Matricule
AND E_Effectifs.AsOf = S_JobTitles.JobTitlesAsOf
)
INNER JOIN
E_STE_Activity
ON E_STE_Activity.Matricule = E_Effectifs.Employee_CD
)
INNER JOIN
BDR
ON E_STE_Activity.Activity = BDR.Memo
WHERE STEAsOf = S_JobTitles.JobTitlesAsOf
GROUP BY BDR.Staff_PC
, S_JobTitles.Job_Titles
, E_STE_Activity.Activity
, S_JobTitles.Weight
; |
Partager