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 |
