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 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
| SELECT
_P.ProjectName COLLATE Latin1_General_CI_AS as ProjectName,
_P.[CF Nom] AS [Titre du projet],
_P.[CF Numéro],
_P.[CF Directeur],
_P.[CF Chef],
_P.[CF Phase],
ISNULL(_R.[CF Type], 'N/D') AS [CF Type],
CASE
WHEN (_R.[CF Type] = 'Interne' AND AUV.[CF CR ressource_R] IS NOT NULL) THEN ISNULL(LEFT(RIGHT(AUV.[CF CR ressource_R], 8), 3), 'N/D')
WHEN (_R.[CF Type] = 'Externe' AND AUV.[CF CR ressource_R] IS NOT NULL) THEN ISNULL(LEFT(RIGHT(AUV.[CF CR ressource_R], 8), 3), 'N/D')
ELSE ISNULL(LEFT(RIGHT(AUV.[CF CR affectation_T], 8), 3), 'N/D')
END AS [Catégorie ressource],
CASE
WHEN YEAR(ABD.TimeByDay) = YEAR(GETDATE()) THEN ('Année ' + CAST((YEAR(GETDATE())) AS nvarchar(4)))
WHEN YEAR(ABD.TimeByDay) = (YEAR(GETDATE()) + 1) THEN ('Année ' + CAST((YEAR(GETDATE()) + 1) AS nvarchar(4)))
WHEN YEAR(ABD.TimeByDay) = (YEAR(GETDATE()) + 2) THEN ('Année ' + CAST((YEAR(GETDATE()) + 2) AS nvarchar(4)))
WHEN YEAR(ABD.TimeByDay) > (YEAR(GETDATE()) + 2) THEN 'Années futures'
ELSE 'Années passées'
END AS Période,
[Année]= (CAST(YEAR(ABD.TimeByDay)AS nvarchar(4))),
ISNULL(SUM(ABD.AssignmentActualCost), 0) AS [A_Coût réalisation],
ISNULL(SUM(ABD.AssignmentRemainingCost), 0) AS [A_Coût restant],
ISNULL(SUM(ABD.AssignmentBaseline10Cost), 0) AS [A_Coût autorisé],
ISNULL(SUM(ABD.AssignmentActualCost), 0) + ISNULL(SUM(ABD.AssignmentRemainingCost), 0) AS [A_Coût total],
ISNULL(SUM(ABD.AssignmentBaseline10Cost), 0) - (ISNULL(SUM(ABD.AssignmentActualCost), 0) + ISNULL(SUM(ABD.AssignmentRemainingCost), 0)) AS [A_Écart Coût],
ISNULL(SUM(ABD.AssignmentActualWork), 0) / 7 AS [A_Travail réalisation],
ISNULL(SUM(ABD.AssignmentRemainingWork), 0) / 7 AS [A_Travail restant],
ISNULL(SUM(ABD.AssignmentBaseline10Work), 0) / 7 AS [A_Travail autorisé],
(ISNULL(SUM(ABD.AssignmentActualWork), 0) + ISNULL(SUM(ABD.AssignmentRemainingWork), 0)) / 7 AS [A_Travail total],
(ISNULL(SUM(ABD.AssignmentBaseline10Work), 0) - (ISNULL(SUM(ABD.AssignmentActualWork), 0) + ISNULL(SUM(ABD.AssignmentRemainingWork), 0)))/ 7 AS [A_Écart travail],
_P.ProjectUID
FROM dbo.MSP_EpmAssignmentByDay_UserView AS ABD WITH (nolock)
LEFT OUTER JOIN dbo.MSP_EpmAssignment_UserView AS AUV WITH (nolock) ON ABD.AssignmentUID = AUV.AssignmentUID
LEFT OUTER JOIN dbo.MSP_EpmAssignment AS _A WITH (nolock) ON ABD.AssignmentUID = _A.AssignmentUID
LEFT OUTER JOIN dbo.MSP_EpmTask_UserView AS _T WITH (nolock) ON ABD.ProjectUID = _T.ProjectUID AND ABD.TaskUID = _T.TaskUID
LEFT OUTER JOIN dbo.MSP_EpmProject_UserView AS _P WITH (nolock) ON _A.ProjectUID = _P.ProjectUID
LEFT OUTER JOIN (SELECT DISTINCT ResourceName COLLATE Latin1_General_CI_AS AS Expr1,
[CF Type],
ResourceUID,
ResourceIsGeneric
FROM dbo.MSP_EpmResource_UserView AS R WITH (nolock)
) AS _R ON AUV.ResourceUID = _R.ResourceUID
GROUP BY
ABD.TimeByDay,
_P.ProjectName COLLATE Latin1_General_CI_AS,
_P.ProjectUID,
_P.[CF Nom],
_P.[CF Numéro],
_P.[CF Directeur],
_P.[CF Chef],
_P.[CF Phase],
_R.[CF Type],
AUV.[CF CR affectation_T],
AUV.[CF CR ressource_R] |
Partager