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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
|
sSQL = sSQL + " SELECT DD.PARC_CONTRACTUEL,CC.CODE_TYPECNT,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 1 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_01,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 2 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_02,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 3 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_03,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 4 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_04,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 5 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_05,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 6 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_06,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 7 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_07,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 8 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_08,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 9 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_09,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 10 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_10,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 11 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_11,"
sSQL = sSQL + " SUM(CASE"
sSQL = sSQL + " WHEN (MONTH(DD.DTE_FACT)-1) = 12 THEN (CAST(DD.KMS_PARCOURUS as NUMERIC))"
sSQL = sSQL + " ELSE 0"
sSQL = sSQL + " END) AS KMS_PARCOURUS_12,"
sSQL = sSQL + " SUM(CAST(DD.KMS_FACT as NUMERIC)) AS KMS_FACT,"
sSQL = sSQL + " MAX(CAST(DD.PX_UNITAIRE as money)) AS PX_UNITAIRE,"
sSQL = sSQL + " SUM(CAST(DD.FACT_MONTANTNET as money)) AS FACT_MONTANTNET,"
sSQL = sSQL + " SUM(CAST(PP.KMINCLUS as NUMERIC)) AS KMINCLUS"
sSQL = sSQL + " FROM (SELECT AA.ID_FACT,"
sSQL = sSQL + " AA.DTE_FACT,"
sSQL = sSQL + " AA.DTEDEB,"
sSQL = sSQL + " AA.DTEFIN,"
sSQL = sSQL + " AA.ID_CLIENTFICHE,"
sSQL = sSQL + " AA.FACT_NUMPARC,"
sSQL = sSQL + " (CASE"
sSQL = sSQL + " WHEN (BB.PARC_ID IS NULL"
sSQL = sSQL + " AND AA.FACT_NUMPARC IS NULL) THEN 'PAS DE CONTRAT'"
sSQL = sSQL + " WHEN (BB.PARC_ID IS NULL"
sSQL = sSQL + " AND AA.FACT_NUMPARC IS NOT NULL) THEN AA.FACT_NUMPARC"
sSQL = sSQL + " WHEN (BB.PARC_ID IS NOT NULL) THEN BB.PARC_ID"
sSQL = sSQL + " Else: BB.PARC_ID"
sSQL = sSQL + " END) AS 'PARC_CONTRACTUEL',"
sSQL = sSQL + " BB.VERSION_CONTRAT,"
sSQL = sSQL + " AA.AAAAMM,"
sSQL = sSQL + " AA.TYPE_LIBELLEPROP,"
sSQL = sSQL + " AA.KMS_PARCOURUS,"
sSQL = sSQL + " AA.KMS_FACT,"
sSQL = sSQL + " AA.PX_UNITAIRE,"
sSQL = sSQL + " AA.FACT_MONTANTNET,"
sSQL = sSQL + " AA.TYPE_PRST,"
sSQL = sSQL + " AA.CODE_AGENCE,"
sSQL = sSQL + " AA.NO_CONTRAT,"
sSQL = sSQL + " AA.NUM_FACT"
sSQL = sSQL + " FROM (SELECT B.ID_FACT,"
sSQL = sSQL + " B.NUM_LIGNE,"
sSQL = sSQL + " H.DTE_FACT,"
sSQL = sSQL + " B.DTEDEB,"
sSQL = sSQL + " B.DTEFIN,"
sSQL = sSQL + " H.ID_CLIENTFICHE,"
sSQL = sSQL + " (CASE"
sSQL = sSQL + " WHEN B.FACT_NUMPARC IS NULL THEN (SELECT TOP 1 LL.FACT_PARCID"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.FACT_LIGNE LL"
sSQL = sSQL + " WHERE LL.ID_FACT = B.ID_FACT"
sSQL = sSQL + " AND (LL.NUM_LIGNE < B.NUM_LIGNE)"
sSQL = sSQL + " AND TYPE_LIGNE = 'HE'"
sSQL = sSQL + " ORDER BY LL.NUM_LIGNE DESC)"
sSQL = sSQL + " Else: B.FACT_NUMPARC"
sSQL = sSQL + " END) AS 'FACT_NUMPARC',"
sSQL = sSQL + " H.AAAAMM,"
sSQL = sSQL + " B.TYPE_LIBELLEPROP,"
sSQL = sSQL + " (CASE"
sSQL = sSQL + " WHEN TYPE_LIBELLEPROP = 'lbl.ficheFactSiege.lblLigneTableau_RetourDepart' THEN FACT_QTE"
sSQL = sSQL + " Else '0'"
sSQL = sSQL + " END) AS 'KMS_Parcourus',"
sSQL = sSQL + " (CASE"
sSQL = sSQL + " WHEN TYPE_LIBELLEPROP = 'ref.typeprst.KM.aff' THEN FACT_QTE"
sSQL = sSQL + " Else '0'"
sSQL = sSQL + " END) AS 'KMS_FACT',"
sSQL = sSQL + " (COALESCE(B.PX_UNITAIRE,0)) AS 'PX_UNITAIRE',"
sSQL = sSQL + " (COALESCE(B.FACT_MONTANTNET,0)) AS 'FACT_MONTANTNET',"
sSQL = sSQL + " B.TYPE_PRST,"
sSQL = sSQL + " B.CODE_AGENCE,"
sSQL = sSQL + " (CASE"
sSQL = sSQL + " WHEN B.NO_CONTRAT IS NULL THEN (SELECT TOP 1 LL.NO_CONTRAT"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.FACT_LIGNE LL"
sSQL = sSQL + " WHERE LL.ID_FACT = B.ID_FACT"
sSQL = sSQL + " AND (LL.NUM_LIGNE < B.NUM_LIGNE)"
sSQL = sSQL + " AND LL.CODE_TYPEPRST = 'LO')"
sSQL = sSQL + " Else: B.NO_CONTRAT"
sSQL = sSQL + " END) AS 'NO_CONTRAT',"
sSQL = sSQL + " H.NUM_FACT"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.FACT_LIGNE B"
sSQL = sSQL + " LEFT JOIN S1SRVBDD02.IGLOO.DBO.FACT_HEADER H"
sSQL = sSQL + " ON H.ID_FACT = B.ID_FACT"
sSQL = sSQL + " WHERE B.ID_FACT IN (SELECT ID_FACT"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.FACT_HEADER"
sSQL = sSQL + " WHERE CODE_TYPECNT = 'LD'"
sSQL = sSQL + " AND DTE_FACT BETWEEN '20100101' AND '20101231'"
sSQL = sSQL + " AND CODE_SOCPF = 'PFLO'"
sSQL = sSQL + " AND ID_CLIENTFICHE <> '5007798'"
sSQL = sSQL + " AND ID_CLIENTFICHE IN (SELECT SUBSTRING(NUM_CLIENT,3,9)"
sSQL = sSQL + " FROM S1SRVBDD02.Z_STATS.DBO.LISTEGRDSCPTES"
sSQL = sSQL + " WHERE GROUPE_PRINCIPAL LIKE '%BRAKE%'))"
sSQL = sSQL + " AND (TYPE_LIBELLEPROP = 'lbl.ficheFactSiege.lblLigneTableau_RetourDepart'"
sSQL = sSQL + " OR CODE_TYPEPRST = 'KM')"
sSQL = sSQL + " AND H.NUM_FACT IS NOT NULL"
sSQL = sSQL + " AND H.IS_COMPTA_CEGID = '1') AA"
sSQL = sSQL + " LEFT JOIN S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR BB"
sSQL = sSQL + " ON AA.NO_CONTRAT = BB.NO_CONTRAT"
sSQL = sSQL + " AND (CASE"
sSQL = sSQL + " WHEN (SELECT TOP 1 VERSION_CONTRAT"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR CCC"
sSQL = sSQL + " WHERE AA.NO_CONTRAT = CCC.NO_CONTRAT"
sSQL = sSQL + " AND AA.DTE_FACT BETWEEN CCC.DTE_DEBUT AND (COALESCE(CCC.DTE_FIN,'31/12/2099'))) IS NOT NULL THEN (SELECT TOP 1 VERSION_CONTRAT"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR CCC"
sSQL = sSQL + " WHERE AA.NO_CONTRAT = CCC.NO_CONTRAT"
sSQL = sSQL + " AND AA.DTE_FACT BETWEEN CCC.DTE_DEBUT AND (COALESCE(CCC.DTE_FIN,'31/12/2099')))"
sSQL = sSQL + " ELSE (SELECT MAX(VERSION_CONTRAT)"
sSQL = sSQL + " FROM S1SRVBDD02.IGLOO.DBO.CONTRAT_VAR CCC"
sSQL = sSQL + " WHERE AA.NO_CONTRAT = CCC.NO_CONTRAT)"
sSQL = sSQL + " END) = BB.VERSION_CONTRAT) DD"
sSQL = sSQL + " "
sSQL = sSQL + " LEFT JOIN S1SRVBDD02.IGLOO.DBO.CNT_PRST PP"
sSQL = sSQL + " ON PP.NO_CONTRAT = DD.NO_CONTRAT"
sSQL = sSQL + " AND PP.VERSION_CONTRAT = DD.VERSION_CONTRAT"
sSQL = sSQL + " AND PP.CODE_TYPEPRST = 'KM'"
sSQL = sSQL + " LEFT JOIN S1SRVBDD02.IGLOO.DBO.CONTRAT CC"
sSQL = sSQL + " ON PP.NO_CONTRAT = CC.NO_CONTRAT"
sSQL = sSQL + " "
sSQL = sSQL + " GROUP BY DD.PARC_CONTRACTUEL, CC.CODE_TYPECNT"
sSQL = sSQL + " ORDER BY DD.PARC_CONTRACTUEL" |
Syntaxe VBA / SQL
Répondre avec citation
Partager