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 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
|
--------------------------------------------- Nb jours PRODUCTION
WITH Jour_Prod (JousProd,Affaire)AS
(
SELECT
sum([Présence Réel Equipe]) AS Nb_Jours,
[Document No_]
FROM dbo.[Production Chantier]
WHERE No_ = 'PR'
AND [Forecast Date] BETWEEN '01/01/2000' AND '31/07/2012'
AND [Présence Réel Equipe]>0
AND Région = 'CENTRE'
GROUP BY [Document No_]
)
--------------------------------------------- Nb jours PRODUCTION 2012
, Jour_Prod_2012 (JousProd2012,Affaire)AS
(
SELECT
sum([Présence Réel Equipe]) AS Nb_Jours,
[Document No_]
FROM dbo.[Production Chantier]
WHERE No_ = 'PR'
AND [Forecast Date] BETWEEN '01/01/2012' AND '31/07/2012'
AND [Présence Réel Equipe]>0
AND Région = 'CENTRE'
GROUP BY [Document No_]
)
--------------------------------------------------- Factures
, Facturation (Affaire,Ingenieur,FactPhaseB)AS
(
SELECT
Affaire AS Affaire,
max([Code Ingénieur]) AS Ingenieur,
sum(cast([Phase B complet] AS float)) AS Fact_Phase_B
FROM dbo.[Statistiques]
WHERE Région = 'CENTRE'
GROUP BY Affaire
--ORDER BY Affaire
)
------------------------------------------------------ Devis
, Devis(Agence,Affaire,DevisPhaseB) AS
(
SELECT
max([Responsibility Center]) AS Agence,
[Job No_],
sum(cast([Line Amount]AS float)) AS Devis_phaseB
FROM dbo.[Sales Line]
WHERE [Phase Code] = 'B'
GROUP BY [Job No_]
)
SELECT
T4.Agence,
T1.Affaire,
T1.JousProd,
T2.JousProd2012,
T3.Ingenieur,
T3.FactPhaseB,
T4.DevisPhaseB
FROM dbo.[ Jour_Prod] T1
LEFT OUTER JOIN [Jour_Prod_2012] T2
ON T1.Affaire = T2.Affaire
LEFT OUTER JOIN [Facturation] T3
ON T1.Affaire = T3.Affaire
LEFT OUTER JOIN [Devis] T4
ON T1.Affaire = T4.Affaire
ORDER BY T4.Agence,T1.Affaire |
Partager