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
| SELECT
max(T1.Région) AS Region,
max(T1.Agence) AS Agence,
T1.Affaire,
case
when max(T4.[Statut Affaire]) = '2' Then 'Affaire Facturée'
when max(T4.[Statut Affaire]) = '3' Then 'Dossier Archivé '
end AS statut_affaires,
max(convert(char(10),T4.[Ending Date],103)) AS Date_Fin,
max(T4.[Description]) AS Description,
max(T4.[Type mission]) AS mission,
max(T1.[Code Ingénieur])AS Ingenieur,
sum(cast((T1.[Phase A])AS float)) AS Montant_ A,
sum(cast((T1.[Phase B complet])AS float)) AS Montant_ B,
sum(cast((T1.[Phase C])AS float)) AS Montant_ C,
sum(cast((T1.[Phase D])AS float)) AS Montant_ D,
sum(cast((T1.[Phase A]+T1.[Phase C]+T1.[Phase D]+T1.[Phase B complet])AS float)) AS Montant,
max(T3.jours_Prod) AS jours_Prod,
cast((sum(T1.[Phase B complet]) / max(T3.jours_Prod)) AS decimal(18,2)) AS PTVE
FROM dbo.[Societe$Statistiques] T1
LEFT OUTER JOIN dbo.[SOCIETE$Job] T4
ON T1.Affaire = T4.No_
OUTER apply (
SELECT
sum(cast(T2.[Présence Réel Equipe] AS float)) AS jours_Prod
FROM dbo.[SOCIETE$Production Chantier] T2
WHERE T1.Affaire = T2.[Document No_]
AND T2.No_ = 'PR'
AND T2.[Présence Réel Equipe] <> '0'
) T3
WHERE T1.[Date Facture] BETWEEN @debut AND @Fin
AND T1.[Région] = 'NORD'
AND T1.Agence IN (@Agence)
AND T4.[Ending Date] <> '01/01/1753'
AND T1.[Type Document] < '2'
AND T4.[Statut Affaire] BETWEEN '2' AND '3'
GROUP BY T1.Affaire
ORDER BY T1.Affaire |
Partager