Procédure stockée + table temporaire

**gardou** · 20/01/2014, 17h38

Bonjour,

J'ai un soucis avec une procédure stockée ( oui elle est trés moche, et pas du tout simplifié ).
Le but est de calculer certaines données et les mettres dans un tableau plus tard en javascript.
Dans ma requête je met tout dans un premier tableau, puis j'update selon ce tableau dans d'autres table ( pour avoir la mise en page de mon tableau )

Problème : plusieurs utilisateurs lance la requête en même temps et plante avant le drop de la premiere table donc plus rien ne fonctionne vu que je fais un SELECT INTO.

Je cherche donc a trouver une solution, la meilleure semble etre la table tempo, je n'arrive pas du tout à utiliser ceci avec des "champs virtuel" suite à mes calculs ou si il y a une autre methode je suis preneur.

Je joins tout le code c'est peut etre plus simple pour comprendre ( c'est trés repetitif désolé et je m'excuse au prés des experts pour le mal de crane)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER procedure [dbo].[testkevin] 
@idaget as nvarchar(10),
@campagne1 as nvarchar(30),
@campagne2 as nvarchar(30),
@campagne3 as nvarchar(30)
 
as
 
SELECT
 
COUNT(CASE WHEN CallStatusNum = '1' THEN Duration END) AS CPG_Plateau,
COUNT(CASE WHEN CallStatusNum = '2' THEN Duration END) AS CAG_Plateau, 
COUNT(CASE WHEN CallStatusNum = '3' THEN Duration END) AS RefusRepondreG_Plateau, 
COUNT(CASE WHEN CallStatusNum = '4' THEN Duration END) AS DoublonsG_Plateau, 
COUNT(CASE WHEN CallStatusNum = '5' THEN Duration END) AS HorsCibleG_Plateau, 
COUNT(CASE WHEN CallStatusNum = '6' THEN Duration END) AS InjoignableG_Plateau, 
COUNT(CASE WHEN CallStatusNum = '7' THEN Duration END) AS RefusVPEG_Plateau, 
COUNT(CASE WHEN CallStatusNum = '91' THEN Duration END) AS FauxNumG_Plateau,
 
INTO DEV_SUPERVISION.dbo.PremierTableau
 
FROM HN_ONDATA2.dbo.odcalls
WHERE HN_ONDATA2.dbo.odcalls
AND (CustomerID = '97') 
AND (CallStatusNum = 1 OR CallStatusNum = 2 OR CallStatusNum = 3 OR CallStatusNum = 4 OR CallStatusNum = 5 OR CallStatusNum = 6 OR CallStatusNum =7 OR CallStatusNum = 91) 
AND (LastCampaign = @campagne1 OR LastCampaign = @campagne2 OR LastCampaign = @campagne3)
AND (LEFT(CallLocalTimeString, 8) = CONVERT(nvarchar, GETDATE(), 112))
 
UPDATE DEV_SUPERVISION.dbo.Tableau_Final
SET CG_PERSO=PremierTableau.CPG_Agent,
    CG_PLATEAU=PremierTableau.CPG_Plateau,
    C1_PERSO=PremierTableau.CPC1_Agent,
    C1_PLATEAU=PremierTableau.CPC1_Plateau,
    C2_PERSO=PremierTableau.CPC2_Agent,
    C2_PLATEAU=PremierTableau.CPC2_Plateau,
    C3_PERSO=PremierTableau.CPC3_Agent,
    C3_PLATEAU=PremierTableau.CPC3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final
WHERE INDICATEUR='VENTES'
 
-- Update 1er tableau CA
UPDATE DEV_SUPERVISION.dbo.Tableau_Final
SET CG_PERSO=PremierTableau.CAG_Agent, 
	CG_PLATEAU=PremierTableau.CAG_Plateau, 
	C1_PERSO=PremierTableau.CAC1_Agent,
	C1_PLATEAU=PremierTableau.CAC1_Plateau,
	C2_PERSO=PremierTableau.CAC2_Agent,
	C2_PLATEAU=PremierTableau.CAC2_Plateau,
	C3_PERSO=PremierTableau.CAC3_Agent,
	C3_PLATEAU=PremierTableau.CAC3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final
WHERE INDICATEUR='CA'
 
-- Update 1er tableau REFUS REPONDRE
UPDATE DEV_SUPERVISION.dbo.Tableau_Final
SET CG_PERSO=PremierTableau.RefusRepondreG_Agent, 
	CG_PLATEAU=PremierTableau.RefusRepondreG_Plateau, 
	C1_PERSO=PremierTableau.RefusRepondreC1_Agent,
	C1_PLATEAU=PremierTableau.RefusRepondreC1_Plateau,
	C2_PERSO=PremierTableau.RefusRepondreC2_Agent,
	C2_PLATEAU=PremierTableau.RefusRepondre2_Plateau,
	C3_PERSO=PremierTableau.RefusRepondreC3_Agent,
	C3_PLATEAU=PremierTableau.RefusRepondreC3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final
WHERE INDICATEUR='REFUS DE REPONDRE'
 
-- Update 1er tableau DOUBLONS
UPDATE DEV_SUPERVISION.dbo.Tableau_Final
SET CG_PERSO=PremierTableau.DoublonsG_Agent, 
	CG_PLATEAU=PremierTableau.DoublonsG_Plateau, 
	C1_PERSO=PremierTableau.DoublonsC1_Agent,
	C1_PLATEAU=PremierTableau.DoublonsC1_Plateau,
	C2_PERSO=PremierTableau.DoublonsC2_Agent,
	C2_PLATEAU=PremierTableau.DoublonsC2_Plateau,
	C3_PERSO=PremierTableau.DoublonsC3_Agent,
	C3_PLATEAU=PremierTableau.DoublonsC3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final
WHERE INDICATEUR='DOUBLONS'
 
-- Update 1er tableau HORS CIBLES
UPDATE DEV_SUPERVISION.dbo.Tableau_Final
set CG_PERSO=PremierTableau.HorsCibleG_Agent, 
	CG_PLATEAU=PremierTableau.HorsCibleG_Plateau,  
	C1_PERSO=PremierTableau.HorsCibleC1_Agent,
	C1_PLATEAU=PremierTableau.HorsCibleC1_Plateau,
	C2_PERSO=PremierTableau.HorsCibleC2_Agent,
	C2_PLATEAU=PremierTableau.HorsCibleC2_Plateau,
	C3_PERSO=PremierTableau.HorsCibleC3_Agent,
	C3_PLATEAU=PremierTableau.HorsCibleC3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final
WHERE INDICATEUR='HORS CIBLES'
 
-- Update 1er tableau INJOIGNABLES
UPDATE DEV_SUPERVISION.dbo.Tableau_Final
set CG_PERSO=PremierTableau.InjoignableG_Agent, 
	CG_PLATEAU=PremierTableau.InjoignableG_Plateau,  
	C1_PERSO=PremierTableau.InjoignableC1_Agent,
	C1_PLATEAU=PremierTableau.InjoignableC1_Plateau,
	C2_PERSO=PremierTableau.InjoignableC2_Agent,
	C2_PLATEAU=PremierTableau.InjoignableC2_Plateau,
	C3_PERSO=PremierTableau.InjoignableC3_Agent,
	C3_PLATEAU=PremierTableau.InjoignableC3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final
WHERE INDICATEUR='INJOIGNABLES'
 
-- Update 1er tableau REFUS VPE
UPDATE DEV_SUPERVISION.dbo.Tableau_Final
set CG_PERSO=PremierTableau.RefusVPEG_Agent, 
	CG_PLATEAU=PremierTableau.RefusVPEG_Plateau,  
	C1_PERSO=PremierTableau.RefusVPEC1_Agent,
	C1_PLATEAU=PremierTableau.RefusVPEC1_Plateau,
	C2_PERSO=PremierTableau.RefusVPEC2_Agent,
	C2_PLATEAU=PremierTableau.RefusVPEC2_Plateau,
	C3_PERSO=PremierTableau.RefusVPEC3_Agent,
	C3_PLATEAU=PremierTableau.RefusVPEC3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final
WHERE INDICATEUR='REFUS VPE'
 
-- Update 1er tableau FAUX NUMEROS
UPDATE DEV_SUPERVISION.dbo.Tableau_Final
set CG_PERSO=PremierTableau.FauxNumG_Agent, 
	CG_PLATEAU=PremierTableau.FauxNumG_Plateau, 
	C1_PERSO=PremierTableau.FauxNumC1_Agent,
	C1_PLATEAU=PremierTableau.FauxNumC1_Plateau,
	C2_PERSO=PremierTableau.FauxNumC2_Agent,
	C2_PLATEAU=PremierTableau.FauxNumC2_Plateau,
	C3_PERSO=PremierTableau.FauxNumC3_Agent,
	C3_PLATEAU=PremierTableau.FauxNumC3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final
WHERE INDICATEUR='FAUX NUMEROS'
 
-- Update 2eme tableau TAUX ACCORD
UPDATE DEV_SUPERVISION.dbo.Tableau_Final2
SET CG_PERSO=PremierTableau.TAccord_G_Agent,
    CG_PLATEAU=PremierTableau.TAccord_G_Plateau,
    C1_PERSO=PremierTableau.TAccord_C1_Agent,
    C1_PLATEAU=PremierTableau.TAccord_C1_Plateau,
    C2_PERSO=PremierTableau.TAccord_C2_Agent,
    C2_PLATEAU=PremierTableau.TAccord_C2_Plateau,
    C3_PERSO=PremierTableau.TAccord_C3_Agent,
    C3_PLATEAU=PremierTableau.TAccord_C3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final2
WHERE INDICATEUR='TAUX D ACCORD'
 
-- Update 2eme tableau TAUX CA/FT
UPDATE DEV_SUPERVISION.dbo.Tableau_Final2
SET CG_PERSO=PremierTableau.TCA_G_Agent,
    CG_PLATEAU=PremierTableau.TCA_G_Plateau,
    C1_PERSO=PremierTableau.TCA_C1_Agent,
    C1_PLATEAU=PremierTableau.TCA_C1_Plateau,
    C2_PERSO=PremierTableau.TCA_C2_Agent,
    C2_PLATEAU=PremierTableau.TCA_C2_Plateau,
    C3_PERSO=PremierTableau.TCA_C3_Agent,
    C3_PLATEAU=PremierTableau.TCA_C3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final2
WHERE INDICATEUR='TAUX CA/FT'
 
-- Update 2eme tableau TAUX RR/FT
UPDATE DEV_SUPERVISION.dbo.Tableau_Final2
SET CG_PERSO=PremierTableau.TRR_G_Agent,
    CG_PLATEAU=PremierTableau.TRR_G_Plateau,
    C1_PERSO=PremierTableau.TRR_C1_Agent,
    C1_PLATEAU=PremierTableau.TRR_C1_Plateau,
    C2_PERSO=PremierTableau.TRR_C2_Agent,
    C2_PLATEAU=PremierTableau.TRR_C2_Plateau,
    C3_PERSO=PremierTableau.TRR_C3_Agent,
    C3_PLATEAU=PremierTableau.TRR_C3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final2
WHERE INDICATEUR='TAUX RR/FT'
 
-- Update 2eme tableau TAUX DOUBLONS/FT
UPDATE DEV_SUPERVISION.dbo.Tableau_Final2
SET CG_PERSO=PremierTableau.TDoublons_G_Agent,
    CG_PLATEAU=PremierTableau.TDoublons_G_Plateau,
    C1_PERSO=PremierTableau.TDoublons_C1_Agent,
    C1_PLATEAU=PremierTableau.TDoublons_C1_Plateau,
    C2_PERSO=PremierTableau.TDoublons_C2_Agent,
    C2_PLATEAU=PremierTableau.TDoublons_C2_Plateau,
    C3_PERSO=PremierTableau.TDoublons_C3_Agent,
    C3_PLATEAU=PremierTableau.TDoublons_C3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final2
WHERE INDICATEUR='TAUX DOUBLONS/FT'
 
-- Update 2eme tableau TAUX HC/FT
UPDATE DEV_SUPERVISION.dbo.Tableau_Final2
SET CG_PERSO=PremierTableau.THCible_G_Agent,
    CG_PLATEAU=PremierTableau.THCible_G_Plateau,
    C1_PERSO=PremierTableau.THCible_C1_Agent,
    C1_PLATEAU=PremierTableau.THCible_C1_Plateau,
    C2_PERSO=PremierTableau.THCible_C2_Agent,
    C2_PLATEAU=PremierTableau.THCible_C2_Plateau,
    C3_PERSO=PremierTableau.THCible_C3_Agent,
    C3_PLATEAU=PremierTableau.THCible_C3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final2
WHERE INDICATEUR='TAUX DE HC/FT'
 
-- Update 2eme tableau TAUX INJOIGNABLE/FT
UPDATE DEV_SUPERVISION.dbo.Tableau_Final2
SET CG_PERSO=PremierTableau.TInjoignable_G_Agent,
    CG_PLATEAU=PremierTableau.TInjoignable_G_Plateau,
    C1_PERSO=PremierTableau.TInjoignable_C1_Agent,
    C1_PLATEAU=PremierTableau.TInjoignable_C1_Plateau,
    C2_PERSO=PremierTableau.TInjoignable_C2_Agent,
    C2_PLATEAU=PremierTableau.TInjoignable_C2_Plateau,
    C3_PERSO=PremierTableau.TInjoignable_C3_Agent,
    C3_PLATEAU=PremierTableau.TInjoignable_C3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final2
WHERE INDICATEUR='TAUX INJOIGNABLE/FT'
 
-- Update 2eme tableau TAUX REFUS VPE/FT
UPDATE DEV_SUPERVISION.dbo.Tableau_Final2
SET CG_PERSO=PremierTableau.TRefusVPE_G_Agent,
    CG_PLATEAU=PremierTableau.TRefusVPE_G_Plateau,
    C1_PERSO=PremierTableau.TRefusVPE_C1_Agent,
    C1_PLATEAU=PremierTableau.TRefusVPE_C1_Plateau,
    C2_PERSO=PremierTableau.TRefusVPE_C2_Agent,
    C2_PLATEAU=PremierTableau.TRefusVPE_C2_Plateau,
    C3_PERSO=PremierTableau.TRefusVPE_C3_Agent,
    C3_PLATEAU=PremierTableau.TRefusVPE_C3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final2
WHERE INDICATEUR='TAUX REFUS VPE/FT'
 
-- Update 2eme tableau TAUX FN/FT
UPDATE DEV_SUPERVISION.dbo.Tableau_Final2
SET CG_PERSO=PremierTableau.TFN_G_Agent,
    CG_PLATEAU=PremierTableau.TFN_G_Plateau,
    C1_PERSO=PremierTableau.TFN_C1_Agent,
    C1_PLATEAU=PremierTableau.TFN_C1_Plateau,
    C2_PERSO=PremierTableau.TFN_C2_Agent,
    C2_PLATEAU=PremierTableau.TFN_C2_Plateau,
    C3_PERSO=PremierTableau.TFN_C3_Agent,
    C3_PLATEAU=PremierTableau.TFN_C3_Plateau
FROM DEV_SUPERVISION.dbo.PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final2
WHERE INDICATEUR='TAUX FN/FT'
 
DROP TABLE DEV_SUPERVISION.dbo.PremierTableau

Merci
Cordialement,

**aieeeuuuuu** · 20/01/2014, 18h16

Bonjour,

Vous pouvez vous passez de votre table "premierTableau" en passant directement par une CTE, et ne faire qu'un seul update, ce qui sera bien plus rapide.

Quelque chose dans ce gout là... (c'est le principe, que je vous laisse compléter)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
ALTER procedure [dbo].[testkevin] 
@idaget AS nvarchar(10),
@campagne1 AS nvarchar(30),
@campagne2 AS nvarchar(30),
@campagne3 AS nvarchar(30)
 
AS
 
;WITH PremierTableau AS (
	SELECT
	COUNT(CASE WHEN CallStatusNum = '1' THEN Duration END) AS CPG_Plateau,
	COUNT(CASE WHEN CallStatusNum = '2' THEN Duration END) AS CAG_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '3' THEN Duration END) AS RefusRepondreG_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '4' THEN Duration END) AS DoublonsG_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '5' THEN Duration END) AS HorsCibleG_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '6' THEN Duration END) AS InjoignableG_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '7' THEN Duration END) AS RefusVPEG_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '91' THEN Duration END) AS FauxNumG_Plateau,
 	FROM HN_ONDATA2.dbo.odcalls
	WHERE HN_ONDATA2.dbo.odcalls
	AND (CustomerID = '97') 
	AND (CallStatusNum = 1 OR CallStatusNum = 2 OR CallStatusNum = 3 OR CallStatusNum = 4 OR CallStatusNum = 5 OR CallStatusNum = 6 OR CallStatusNum =7 OR CallStatusNum = 91) 
	AND (LastCampaign = @campagne1 OR LastCampaign = @campagne2 OR LastCampaign = @campagne3)
	AND (LEFT(CallLocalTimeString, 8) = CONVERT(nvarchar, GETDATE(), 112))
)
UPDATE DEV_SUPERVISION.dbo.Tableau_Final
SET CG_PERSO= CASE INDICATEUR
			WHEN 'VENTES' THEN PremierTableau.CPG_Plateau
			WHEN 'CA' THEN PremierTableau.CAG_Agent
			...
			ELSE CG_PERSO
		END
 
 
    ,CG_PLATEAU= CASE INDICATEUR
		WHEN ...
	,	...
FROM PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final
WHERE INDICATEUR='VENTES'
 
END

MAis :
1/ j'ai l'impression qu'il vous manque une condition de jointure entre vos deux tables.
2/ cette partie du filtre mériterait d'être réécrite :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
AND (LEFT(CallLocalTimeString, 8) = CONVERT(nvarchar, GETDATE(), 112))

**SQLDev** · 21/01/2014, 11h13

Bonjour,

Peut-être que vous devriez travailler sur une table qui ne sera jamais droppée, avec un système de purge des données une fois le traitement terminé, et l'ajout d'une colonne identifiant un traitement unique (donc une exécution unique de la procédure pour un utilisateur).
Vous pouvez utiliser le type UNIQUEIDENTIFIER par exemple pour identifier les données en cours de traitement dans la procédure, si votre version SQL Server le permet.

**gardou** · 21/01/2014, 18h17

Merci aieeeuuuuu

Ca m'a l'air d'etre pas mal du tout par contre je bloque sur un point :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
 
 
ALTER procedure [dbo].[ProcStockDevSupervision] 
@idaget as nvarchar(10),
@campagne1 as nvarchar(30),
@campagne2 as nvarchar(30),
@campagne3 as nvarchar(30)
 
as
 
WITH PremierTableau 
AS 
(
	SELECT
	-- GLOBAL PLATEAU
	COUNT(CASE WHEN CallStatusNum = '1'  THEN Duration END) AS CPG_Plateau,
	COUNT(CASE WHEN CallStatusNum = '2'  THEN Duration END) AS CAG_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '3'  THEN Duration END) AS RefusRepondreG_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '4'  THEN Duration END) AS DoublonsG_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '5'  THEN Duration END) AS HorsCibleG_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '6'  THEN Duration END) AS InjoignableG_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '7'  THEN Duration END) AS RefusVPEG_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '91' THEN Duration END) AS FauxNumG_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '1' THEN Duration END)) / NULLIF((COUNT(CASE WHEN CallStatusNum = '1' THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '2' THEN Duration END)+ COUNT(CASE WHEN CallStatusNum = '7' THEN Duration END)),0)*100) AS TAccord_G_Plateau, 
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '2' THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') THEN Duration END),0)*100) AS TCA_G_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '3' THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') THEN Duration END),0)*100) AS TRR_G_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '4' THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') THEN Duration END),0)*100) AS TDoublons_G_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '5' THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') THEN Duration END),0)*100) AS THCible_G_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '6' THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') THEN Duration END),0)*100) AS TInjoignable_G_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '7' THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') THEN Duration END),0)*100) AS TRefusVPE_G_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '91' THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') THEN Duration END),0)*100) AS TFN_G_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),sum(CallStatus.Cost)) / NULLIF(COUNT(CASE WHEN CallStatusNum = '1' THEN Duration END),0)) AS PrimeMoyenneG_Plateau,
 
	-- GLOBAL AGENT
	COUNT(CASE WHEN CallStatusNum = '1'  AND LastAgent=@idaget THEN Duration END) AS CPG_Agent, 
	COUNT(CASE WHEN CallStatusNum = '2'  AND LastAgent=@idaget THEN Duration END) AS CAG_Agent, 
	COUNT(CASE WHEN CallStatusNum = '3'  AND LastAgent=@idaget THEN Duration END) AS RefusRepondreG_Agent, 
	COUNT(CASE WHEN CallStatusNum = '4'  AND LastAgent=@idaget THEN Duration END) AS DoublonsG_Agent, 
	COUNT(CASE WHEN CallStatusNum = '5'  AND LastAgent=@idaget THEN Duration END) AS HorsCibleG_Agent, 
	COUNT(CASE WHEN CallStatusNum = '6'  AND LastAgent=@idaget THEN Duration END) AS InjoignableG_Agent, 
	COUNT(CASE WHEN CallStatusNum = '7'  AND LastAgent=@idaget THEN Duration END) AS RefusVPEG_Agent, 
	COUNT(CASE WHEN CallStatusNum = '91' AND LastAgent=@idaget THEN Duration END) AS FauxNumG_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '1' AND LastAgent=@idaget THEN Duration END)) / NULLIF((COUNT(CASE WHEN CallStatusNum = '1' AND LastAgent=@idaget THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '2' AND LastAgent=@idaget THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '7' AND LastAgent=@idaget THEN Duration END)),0)*100) AS TAccord_G_Agent, 
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '2' AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastAgent=@idaget THEN Duration END),0)*100) AS TCA_G_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '3' AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastAgent=@idaget THEN Duration END),0)*100) AS TRR_G_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '4' AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastAgent=@idaget THEN Duration END),0)*100) AS TDoublons_G_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '5' AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastAgent=@idaget THEN Duration END),0)*100) AS THCible_G_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '6' AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastAgent=@idaget THEN Duration END),0)*100) AS TInjoignable_G_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '7' AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastAgent=@idaget THEN Duration END),0)*100) AS TRefusVPE_G_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '91' AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastAgent=@idaget THEN Duration END),0)*100) AS TFN_G_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),(SELECT sum(CallStatus.cost) FROM HN_ONDATA2.dbo.odcalls  CROSS JOIN HN_ADMIN.dbo.CallStatus WHERE (HN_ONDATA2.dbo.odcalls.CallStatusGroup = HN_ADMIN.dbo.callstatus.statusgroup) AND (HN_ONDATA2.dbo.odcalls.CallStatusNum = HN_ADMIN.dbo.callstatus.StatusCode) AND (HN_ONDATA2.dbo.odcalls.CallStatusDetail = HN_ADMIN.dbo.callstatus.StatusDetail) AND (CustomerID = '1') AND (CallStatusNum = 1 OR CallStatusNum = 2 OR CallStatusNum = 3 OR CallStatusNum = 4 OR CallStatusNum = 5 OR CallStatusNum = 6 OR CallStatusNum= 7 OR CallStatusNum = 91) AND (LEFT(CallLocalTimeString, 8) = CONVERT(nvarchar, GETDATE(), 112)) AND LASTAGENT=@idaget )) / NULLIF(COUNT(CASE WHEN CallStatusNum = '1' AND LastAgent=@idaget THEN Duration END),0)) AS PrimeMoyenneG_Agent,
 
	-- CAMPAGNE1 PLATEAU
	COUNT(CASE WHEN CallStatusNum = '1'  AND LastCampaign = @campagne1 THEN Duration END) AS CPC1_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '2'  AND LastCampaign = @campagne1 THEN Duration END) AS CAC1_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '3'  AND LastCampaign = @campagne1 THEN Duration END) AS RefusRepondreC1_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '4'  AND LastCampaign = @campagne1 THEN Duration END) AS DoublonsC1_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '5'  AND LastCampaign = @campagne1 THEN Duration END) AS HorsCibleC1_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '6'  AND LastCampaign = @campagne1 THEN Duration END) AS InjoignableC1_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '7'  AND LastCampaign = @campagne1 THEN Duration END) AS RefusVPEC1_Plateau,
	COUNT(CASE WHEN CallStatusNum = '91' AND LastCampaign = @campagne1 THEN Duration END) AS FauxNumC1_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne1 THEN Duration END)) / NULLIF((COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne1 THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '2' AND LastCampaign = @campagne1 THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '7' AND LastCampaign = @campagne1 THEN Duration END)),0)*100) AS TAccord_C1_Plateau, 
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '2' AND LastCampaign = @campagne1 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 THEN Duration END),0)*100) AS TCA_C1_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '3' AND LastCampaign = @campagne1 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 THEN Duration END),0)*100) AS TRR_C1_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '4' AND LastCampaign = @campagne1 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 THEN Duration END),0)*100) AS TDoublons_C1_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '5' AND LastCampaign = @campagne1 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 THEN Duration END),0)*100) AS THCible_C1_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '6' AND LastCampaign = @campagne1 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 THEN Duration END),0)*100) AS TInjoignable_C1_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '7' AND LastCampaign = @campagne1 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 THEN Duration END),0)*100) AS TRefusVPE_C1_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '91' AND LastCampaign = @campagne1 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 THEN Duration END),0)*100) AS TFN_C1_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),(SELECT sum(CallStatus.cost) FROM HN_ONDATA2.dbo.odcalls  CROSS JOIN HN_ADMIN.dbo.CallStatus WHERE (HN_ONDATA2.dbo.odcalls.CallStatusGroup = HN_ADMIN.dbo.callstatus.statusgroup) AND (HN_ONDATA2.dbo.odcalls.CallStatusNum = HN_ADMIN.dbo.callstatus.StatusCode) AND (HN_ONDATA2.dbo.odcalls.CallStatusDetail = HN_ADMIN.dbo.callstatus.StatusDetail) AND (CustomerID = '1') AND (CallStatusNum = 1 OR CallStatusNum = 2 OR CallStatusNum = 3 OR CallStatusNum = 4 OR CallStatusNum = 5 OR CallStatusNum = 6 OR CallStatusNum=7 OR CallStatusNum = 91) AND (LEFT(CallLocalTimeString, 8) = CONVERT(nvarchar, GETDATE(), 112)) AND LastCampaign = @campagne1)) / NULLIF(COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne1 THEN Duration END),0)) AS PrimeMoyenneC1_Plateau,
 
	-- CAMPAGNE1 AGENT
	COUNT(CASE WHEN CallStatusNum = '1'  AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END) AS CPC1_Agent, 
	COUNT(CASE WHEN CallStatusNum = '2'  AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END) AS CAC1_Agent, 
	COUNT(CASE WHEN CallStatusNum = '3'  AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END) AS RefusRepondreC1_Agent, 
	COUNT(CASE WHEN CallStatusNum = '4'  AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END) AS DoublonsC1_Agent, 
	COUNT(CASE WHEN CallStatusNum = '5'  AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END) AS HorsCibleC1_Agent, 
	COUNT(CASE WHEN CallStatusNum = '6'  AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END) AS InjoignableC1_Agent, 
	COUNT(CASE WHEN CallStatusNum = '7'  AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END) AS RefusVPEC1_Agent, 
	COUNT(CASE WHEN CallStatusNum = '91' AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END) AS FauxNumC1_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END)) / NULLIF((COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '2' AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '7' AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END)),0)*100) AS TAccord_C1_Agent,	
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '2' AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END),0)*100) AS TCA_C1_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '3' AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END),0)*100) AS TRR_C1_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '4' AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END),0)*100) AS TDoublons_C1_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '5' AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END),0)*100) AS THCible_C1_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '6' AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END),0)*100) AS TInjoignable_C1_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '7' AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END),0)*100) AS TRefusVPE_C1_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '91' AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END),0)*100) AS TFN_C1_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),(SELECT sum(CallStatus.cost) FROM HN_ONDATA2.dbo.odcalls  CROSS JOIN HN_ADMIN.dbo.CallStatus WHERE (HN_ONDATA2.dbo.odcalls.CallStatusGroup = HN_ADMIN.dbo.callstatus.statusgroup) AND (HN_ONDATA2.dbo.odcalls.CallStatusNum = HN_ADMIN.dbo.callstatus.StatusCode) AND (HN_ONDATA2.dbo.odcalls.CallStatusDetail = HN_ADMIN.dbo.callstatus.StatusDetail) AND (CustomerID = '1') AND (CallStatusNum = 1 OR CallStatusNum = 2 OR CallStatusNum = 3 OR CallStatusNum = 4 OR CallStatusNum = 5 OR CallStatusNum = 6 OR CallStatusNum=7 OR CallStatusNum = 91) AND (LEFT(CallLocalTimeString, 8) = CONVERT(nvarchar, GETDATE(), 112)) AND LastCampaign = @campagne1 AND LastAgent=@idaget)) / NULLIF(COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne1 AND LastAgent=@idaget THEN Duration END),0)) AS PrimeMoyenneC1_Agent,
 
	-- CAMPAGNE2 PLATEAU
	COUNT(CASE WHEN CallStatusNum = '1'  AND LastCampaign = @campagne2 THEN Duration END) AS CPC2_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '2'  AND LastCampaign = @campagne2 THEN Duration END) AS CAC2_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '3'  AND LastCampaign = @campagne2 THEN Duration END) AS RefusRepondreC2_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '4'  AND LastCampaign = @campagne2 THEN Duration END) AS DoublonsC2_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '5'  AND LastCampaign = @campagne2 THEN Duration END) AS HorsCibleC2_Plateau,
	COUNT(CASE WHEN CallStatusNum = '6'  AND LastCampaign = @campagne2 THEN Duration END) AS InjoignableC2_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '7'  AND LastCampaign = @campagne2 THEN Duration END) AS RefusVPEC2_Plateau,		
	COUNT(CASE WHEN CallStatusNum = '91' AND LastCampaign = @campagne2 THEN Duration END) AS FauxNumC2_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne2 THEN Duration END)) / NULLIF((COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne2 THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '2' AND LastCampaign = @campagne2 THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '7' AND LastCampaign = @campagne2 THEN Duration END)),0)*100) AS TAccord_C2_Plateau, 
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '2' AND LastCampaign = @campagne2 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 THEN Duration END),0)*100) AS TCA_C2_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '3' AND LastCampaign = @campagne2 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 THEN Duration END),0)*100) AS TRR_C2_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '4' AND LastCampaign = @campagne2 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 THEN Duration END),0)*100) AS TDoublons_C2_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '5' AND LastCampaign = @campagne2 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 THEN Duration END),0)*100) AS THCible_C2_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '6' AND LastCampaign = @campagne2 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 THEN Duration END),0)*100) AS TInjoignable_C2_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '7' AND LastCampaign = @campagne2 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 THEN Duration END),0)*100) AS TRefusVPE_C2_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '91' AND LastCampaign = @campagne2 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 THEN Duration END),0)*100) AS TFN_C2_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),(SELECT sum(CallStatus.cost) FROM HN_ONDATA2.dbo.odcalls  CROSS JOIN HN_ADMIN.dbo.CallStatus WHERE (HN_ONDATA2.dbo.odcalls.CallStatusGroup = HN_ADMIN.dbo.callstatus.statusgroup) AND (HN_ONDATA2.dbo.odcalls.CallStatusNum = HN_ADMIN.dbo.callstatus.StatusCode) AND (HN_ONDATA2.dbo.odcalls.CallStatusDetail = HN_ADMIN.dbo.callstatus.StatusDetail) AND (CustomerID = '1') AND (CallStatusNum = 1 OR CallStatusNum = 2 OR CallStatusNum = 3 OR CallStatusNum = 4 OR CallStatusNum = 5 OR CallStatusNum = 6 OR CallStatusNum = 7 OR CallStatusNum = 91) AND (LEFT(CallLocalTimeString, 8) = CONVERT(nvarchar, GETDATE(), 112)) AND LastCampaign = @campagne2)) / NULLIF(COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne2 THEN Duration END),0)) AS PrimeMoyenneC2_Plateau,
 
	-- CAMPAGNE2 AGENT
	COUNT(CASE WHEN CallStatusNum = '1'  AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END) AS CPC2_Agent, 
	COUNT(CASE WHEN CallStatusNum = '2'  AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END) AS CAC2_Agent, 
	COUNT(CASE WHEN CallStatusNum = '3'  AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END) AS RefusRepondreC2_Agent, 
	COUNT(CASE WHEN CallStatusNum = '4'  AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END) AS DoublonsC2_Agent, 
	COUNT(CASE WHEN CallStatusNum = '5'  AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END) AS HorsCibleC2_Agent,
	COUNT(CASE WHEN CallStatusNum = '6'  AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END) AS InjoignableC2_Agent, 
	COUNT(CASE WHEN CallStatusNum = '7'  AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END) AS RefusVPEC2_Agent, 
	COUNT(CASE WHEN CallStatusNum = '91' AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END) AS FauxNumC2_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END)) / NULLIF((COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '2' AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '3' AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END)),0)*100) AS TAccord_C2_Agent, 
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '2' AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END),0)*100) AS TCA_C2_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '3' AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END),0)*100) AS TRR_C2_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '4' AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END),0)*100) AS TDoublons_C2_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '5' AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END),0)*100) AS THCible_C2_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '6' AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END),0)*100) AS TInjoignable_C2_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '7' AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END),0)*100) AS TRefusVPE_C2_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '91' AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END),0)*100) AS TFN_C2_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),(SELECT sum(CallStatus.cost) FROM HN_ONDATA2.dbo.odcalls  CROSS JOIN HN_ADMIN.dbo.CallStatus WHERE (HN_ONDATA2.dbo.odcalls.CallStatusGroup = HN_ADMIN.dbo.callstatus.statusgroup) AND (HN_ONDATA2.dbo.odcalls.CallStatusNum = HN_ADMIN.dbo.callstatus.StatusCode) AND (HN_ONDATA2.dbo.odcalls.CallStatusDetail = HN_ADMIN.dbo.callstatus.StatusDetail) AND (CustomerID = '1') AND (CallStatusNum = 1 OR CallStatusNum = 2 OR CallStatusNum = 3 OR CallStatusNum = 4 OR CallStatusNum = 5 OR CallStatusNum = 6 OR CallStatusNum = 7 OR CallStatusNum = 91) AND (LEFT(CallLocalTimeString, 8) = CONVERT(nvarchar, GETDATE(), 112)) AND LastCampaign = @campagne2 AND LastAgent=@idaget)) / NULLIF(COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne2 AND LastAgent=@idaget THEN Duration END),0)) AS PrimeMoyenneC2_Agent,
 
	-- CAMPAGNE3 PLATEAU
	COUNT(CASE WHEN CallStatusNum = '1'  AND LastCampaign = @campagne3 THEN Duration END) AS CPC3_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '2'  AND LastCampaign = @campagne3 THEN Duration END) AS CAC3_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '3'  AND LastCampaign = @campagne3 THEN Duration END) AS RefusRepondreC3_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '4'  AND LastCampaign = @campagne3 THEN Duration END) AS DoublonsC3_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '5'  AND LastCampaign = @campagne3 THEN Duration END) AS HorsCibleC3_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '6'  AND LastCampaign = @campagne3 THEN Duration END) AS InjoignableC3_Plateau, 
	COUNT(CASE WHEN CallStatusNum = '7'  AND LastCampaign = @campagne3 THEN Duration END) AS RefusVPEC3_Plateau,
	COUNT(CASE WHEN CallStatusNum = '91' AND LastCampaign = @campagne3 THEN Duration END) AS FauxNumC3_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne3 THEN Duration END)) / NULLIF((COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne3 THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '2' AND LastCampaign = @campagne3 THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '3' AND LastCampaign = @campagne3 THEN Duration END)),0)*100) AS TAccord_C3_Plateau, 
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '2' AND LastCampaign = @campagne3 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 THEN Duration END),0)*100) AS TCA_C3_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '3' AND LastCampaign = @campagne3 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 THEN Duration END),0)*100) AS TRR_C3_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '4' AND LastCampaign = @campagne3 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 THEN Duration END),0)*100) AS TDoublons_C3_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '5' AND LastCampaign = @campagne3 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 THEN Duration END),0)*100) AS THCible_C3_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '6' AND LastCampaign = @campagne3 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 THEN Duration END),0)*100) AS TInjoignable_C3_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '7' AND LastCampaign = @campagne3 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 THEN Duration END),0)*100) AS TRefusVPE_C3_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '91' AND LastCampaign = @campagne3 THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 THEN Duration END),0)*100) AS TFN_C3_Plateau,
	Convert(decimal(16,2), convert(decimal(16,2),(SELECT sum(CallStatus.cost) FROM HN_ONDATA2.dbo.odcalls  CROSS JOIN HN_ADMIN.dbo.CallStatus WHERE (HN_ONDATA2.dbo.odcalls.CallStatusGroup = HN_ADMIN.dbo.callstatus.statusgroup) AND (HN_ONDATA2.dbo.odcalls.CallStatusNum = HN_ADMIN.dbo.callstatus.StatusCode) AND (HN_ONDATA2.dbo.odcalls.CallStatusDetail = HN_ADMIN.dbo.callstatus.StatusDetail) AND (CustomerID = '1') AND (CallStatusNum = 1 OR CallStatusNum = 2 OR CallStatusNum = 3 OR CallStatusNum = 4 OR CallStatusNum = 5 OR CallStatusNum = 6 OR CallStatusNum = 7 OR CallStatusNum = 91) AND (LEFT(CallLocalTimeString, 8) = CONVERT(nvarchar, GETDATE(), 112)) AND LastCampaign = @campagne3)) / NULLIF(COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne3 THEN Duration END),0)) AS PrimeMoyenneC3_Plateau,
 
	-- CAMPAGNE3 AGENT
	COUNT(CASE WHEN CallStatusNum = '1'  AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END) AS CPC3_Agent, 
	COUNT(CASE WHEN CallStatusNum = '2'  AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END) AS CAC3_Agent, 
	COUNT(CASE WHEN CallStatusNum = '3'  AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END) AS RefusRepondreC3_Agent, 
	COUNT(CASE WHEN CallStatusNum = '4'  AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END) AS DoublonsC3_Agent, 
	COUNT(CASE WHEN CallStatusNum = '5'  AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END) AS HorsCibleC3_Agent,
	COUNT(CASE WHEN CallStatusNum = '6'  AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END) AS InjoignableC3_Agent, 
	COUNT(CASE WHEN CallStatusNum = '7'  AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END) AS RefusVPEC3_Agent, 
	COUNT(CASE WHEN CallStatusNum = '91' AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END) AS FauxNumC3_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END)) / NULLIF((COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '2' AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END) + COUNT(CASE WHEN CallStatusNum = '3' AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END)),0)*100) AS TAccord_C3_Agent, 
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '2' AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END),0)*100) AS TCA_C3_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '3' AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END),0)*100) AS TRR_C3_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '4' AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END),0)*100) AS TDoublons_C3_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '5' AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END),0)*100) AS THCible_C3_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '6' AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END),0)*100) AS TInjoignable_C3_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '7' AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END),0)*100) AS TRefusVPE_C3_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),COUNT(CASE WHEN CallStatusNum = '91' AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END)) / NULLIF(COUNT(CASE WHEN CallStatusNum in('1','2','3','4','5','6','7','91') AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END),0)*100) AS TFN_C3_Agent,
	Convert(decimal(16,2), convert(decimal(16,2),(SELECT sum(CallStatus.cost) FROM HN_ONDATA2.dbo.odcalls  CROSS JOIN HN_ADMIN.dbo.CallStatus WHERE (HN_ONDATA2.dbo.odcalls.CallStatusGroup = HN_ADMIN.dbo.callstatus.statusgroup) AND (HN_ONDATA2.dbo.odcalls.CallStatusNum = HN_ADMIN.dbo.callstatus.StatusCode) AND (HN_ONDATA2.dbo.odcalls.CallStatusDetail = HN_ADMIN.dbo.callstatus.StatusDetail) AND (CustomerID = '1') AND (CallStatusNum = 1 OR CallStatusNum = 2 OR CallStatusNum = 3 OR CallStatusNum = 4 OR CallStatusNum = 5 OR CallStatusNum = 6 OR CallStatusNum = 7 OR CallStatusNum = 91) AND (LEFT(CallLocalTimeString, 8) = CONVERT(nvarchar, GETDATE(), 112)) AND LastCampaign = @campagne3 AND LastAgent=@idaget)) / NULLIF(COUNT(CASE WHEN CallStatusNum = '1' AND LastCampaign = @campagne3 AND LastAgent=@idaget THEN Duration END),0)) AS PrimeMoyenneC3_Agent
 
 
	FROM HN_ONDATA2.dbo.odcalls CROSS JOIN HN_ADMIN.dbo.CallStatus 
	WHERE(HN_ONDATA2.dbo.odcalls.CallStatusGroup = HN_ADMIN.dbo.CallStatus.statusgroup)
	AND  (HN_ONDATA2.dbo.odcalls.CallStatusNum   = HN_ADMIN.dbo.CallStatus.StatusCode)
	AND  (HN_ONDATA2.dbo.odcalls.CallStatusDetail = HN_ADMIN.dbo.CallStatus.StatusDetail)
	AND  (CustomerID = '97') 
	AND  (CallStatusNum = 1 OR CallStatusNum = 2 OR CallStatusNum = 3 OR CallStatusNum = 4 OR CallStatusNum = 5 OR CallStatusNum = 6 OR CallStatusNum =7 OR CallStatusNum = 91) 
	AND (LastCampaign = @campagne1 OR LastCampaign = @campagne2 OR LastCampaign = @campagne3)
	AND  (LEFT(CallLocalTimeString, 8) = CONVERT(nvarchar, GETDATE(), 112))
)
 
-----------------------------------------------------
 
UPDATE DEV_SUPERVISION.dbo.Tableau_Final
SET 
	-- GLOBAL PLATEAU
	CG_PLATEAU = CASE INDICATEUR
			     WHEN 'VENTES'            THEN PremierTableau.CPG_Plateau
			     WHEN 'CA'                THEN PremierTableau.CAG_Plateau
				 WHEN 'REFUS DE REPONDRE' THEN PremierTableau.RefusRepondreG_Plateau
				 WHEN 'DOUBLONS'          THEN PremierTableau.DoublonsG_Plateau
				 WHEN 'HORS CIBLES'       THEN PremierTableau.HorsCibleG_Plateau
				 WHEN 'INJOIGNABLES'      THEN PremierTableau.InjoignableG_Plateau
                 WHEN 'REFUS VPE'         THEN PremierTableau.RefusVPEG_Plateau
				 WHEN 'FAUX NUMEROS'      THEN PremierTableau.FauxNumG_Plateau
			     ELSE CG_PLATEAU
    END,
 
    -- GLOBAL AGENT
    CG_PERSO = CASE INDICATEUR
			     WHEN 'VENTES'            THEN PremierTableau.CPG_Agent
			     WHEN 'CA'                THEN PremierTableau.CAG_Agent
				 WHEN 'REFUS DE REPONDRE' THEN PremierTableau.RefusRepondreG_Agent
				 WHEN 'DOUBLONS'          THEN PremierTableau.DoublonsG_Agent
				 WHEN 'HORS CIBLES'       THEN PremierTableau.HorsCibleG_Agent
				 WHEN 'INJOIGNABLES'      THEN PremierTableau.InjoignableG_Agent
                 WHEN 'REFUS VPE'         THEN PremierTableau.RefusVPEG_Agent
				 WHEN 'FAUX NUMEROS'      THEN PremierTableau.FauxNumG_Agent
			     ELSE CG_PERSO
    END,
 
    -- CAMPAGNE1 PLATEAU
    C1_PLATEAU = CASE INDICATEUR
			     WHEN 'VENTES'            THEN PremierTableau.CPC1_Plateau
			     WHEN 'CA'                THEN PremierTableau.CAC1_Plateau
				 WHEN 'REFUS DE REPONDRE' THEN PremierTableau.RefusRepondreC1_Plateau
				 WHEN 'DOUBLONS'          THEN PremierTableau.DoublonsC1_Plateau
				 WHEN 'HORS CIBLES'       THEN PremierTableau.HorsCibleC1_Plateau
				 WHEN 'INJOIGNABLES'      THEN PremierTableau.InjoignableC1_Plateau
                 WHEN 'REFUS VPE'         THEN PremierTableau.RefusVPEC1_Plateau
				 WHEN 'FAUX NUMEROS'      THEN PremierTableau.FauxNumC1_Plateau
			     ELSE C1_PLATEAU
    END,
 
    -- CAMPAGNE1 AGENT
    C1_PERSO = CASE INDICATEUR
			     WHEN 'VENTES'            THEN PremierTableau.CPC1_Agent
			     WHEN 'CA'                THEN PremierTableau.CAC1_Agent
				 WHEN 'REFUS DE REPONDRE' THEN PremierTableau.RefusRepondreC1_Agent
				 WHEN 'DOUBLONS'          THEN PremierTableau.DoublonsC1_Agent
				 WHEN 'HORS CIBLES'       THEN PremierTableau.HorsCibleC1_Agent
				 WHEN 'INJOIGNABLES'      THEN PremierTableau.InjoignableC1_Agent
                 WHEN 'REFUS VPE'         THEN PremierTableau.RefusVPEC1_Agent
				 WHEN 'FAUX NUMEROS'      THEN PremierTableau.FauxNumC1_Agent
			     ELSE C1_PERSO
    END,
 
    -- CAMPAGNE2 PLATEAU
    C2_PLATEAU = CASE INDICATEUR
			     WHEN 'VENTES'            THEN PremierTableau.CPC2_Plateau
			     WHEN 'CA'                THEN PremierTableau.CAC2_Plateau
				 WHEN 'REFUS DE REPONDRE' THEN PremierTableau.RefusRepondreC2_Plateau
				 WHEN 'DOUBLONS'          THEN PremierTableau.DoublonsC2_Plateau
				 WHEN 'HORS CIBLES'       THEN PremierTableau.HorsCibleC2_Plateau
				 WHEN 'INJOIGNABLES'      THEN PremierTableau.InjoignableC2_Plateau
                 WHEN 'REFUS VPE'         THEN PremierTableau.RefusVPEC2_Plateau
				 WHEN 'FAUX NUMEROS'      THEN PremierTableau.FauxNumC2_Plateau
			     ELSE C2_PLATEAU
    END,
 
    -- CAMPAGNE2 AGENT
    C2_PERSO = CASE INDICATEUR
			     WHEN 'VENTES'            THEN PremierTableau.CPC2_Agent
			     WHEN 'CA'                THEN PremierTableau.CAC2_Agent
				 WHEN 'REFUS DE REPONDRE' THEN PremierTableau.RefusRepondreC2_Agent
				 WHEN 'DOUBLONS'          THEN PremierTableau.DoublonsC2_Agent
				 WHEN 'HORS CIBLES'       THEN PremierTableau.HorsCibleC2_Agent
				 WHEN 'INJOIGNABLES'      THEN PremierTableau.InjoignableC2_Agent
                 WHEN 'REFUS VPE'         THEN PremierTableau.RefusVPEC2_Agent
				 WHEN 'FAUX NUMEROS'      THEN PremierTableau.FauxNumC2_Agent
			     ELSE C2_PERSO
    END,
 
    -- CAMPAGNE3 PLATEAU
    C3_PLATEAU = CASE INDICATEUR
			     WHEN 'VENTES'            THEN PremierTableau.CPC3_Plateau
			     WHEN 'CA'                THEN PremierTableau.CAC3_Plateau
				 WHEN 'REFUS DE REPONDRE' THEN PremierTableau.RefusRepondreC3_Plateau
				 WHEN 'DOUBLONS'          THEN PremierTableau.DoublonsC3_Plateau
				 WHEN 'HORS CIBLES'       THEN PremierTableau.HorsCibleC3_Plateau
				 WHEN 'INJOIGNABLES'      THEN PremierTableau.InjoignableC3_Plateau
                 WHEN 'REFUS VPE'         THEN PremierTableau.RefusVPEC3_Plateau
				 WHEN 'FAUX NUMEROS'      THEN PremierTableau.FauxNumC3_Plateau
			     ELSE C3_PLATEAU
    END,
 
    -- CAMPAGNE3 AGENT
    C3_PERSO = CASE INDICATEUR
			     WHEN 'VENTES'            THEN PremierTableau.CPC3_Agent
			     WHEN 'CA'                THEN PremierTableau.CAC3_Agent
				 WHEN 'REFUS DE REPONDRE' THEN PremierTableau.RefusRepondreC3_Agent
				 WHEN 'DOUBLONS'          THEN PremierTableau.DoublonsC3_Agent
				 WHEN 'HORS CIBLES'       THEN PremierTableau.HorsCibleC3_Agent
				 WHEN 'INJOIGNABLES'      THEN PremierTableau.InjoignableC3_Agent
                 WHEN 'REFUS VPE'         THEN PremierTableau.RefusVPEC3_Agent
				 WHEN 'FAUX NUMEROS'      THEN PremierTableau.FauxNumC3_Agent
			     ELSE C3_PERSO
    END
FROM PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final
 
-----------------------------------------------------
 
UPDATE DEV_SUPERVISION.dbo.Tableau_Final2
SET 
	-- GLOBAL PLATEAU
	CG_PLATEAU = CASE INDICATEUR
			     WHEN 'TAUX D ACCORD'         THEN PremierTableau.TAccord_G_Plateau
			     WHEN 'TAUX CA/FT'            THEN PremierTableau.TCA_G_Plateau
				 WHEN 'TAUX RR/FT'            THEN PremierTableau.TRR_G_Plateau
				 WHEN 'TAUX DOUBLONS/FT'      THEN PremierTableau.TDoublons_G_Plateau
				 WHEN 'TAUX DE HC/FT'         THEN PremierTableau.THCible_G_Plateau
				 WHEN 'TAUX INJOIGNABLE/FT'   THEN PremierTableau.TInjoignable_G_Plateau
                 WHEN 'TAUX REFUS VPE/FT'     THEN PremierTableau.TRefusVPE_G_Plateau
				 WHEN 'TAUX FN/FT'            THEN PremierTableau.TFN_G_Plateau
                 WHEN 'PRIME MOYENNE'         THEN PremierTableau.PrimeMoyenneG_Plateau
			     ELSE CG_PLATEAU
    END,
 
    -- GLOBAL AGENT
      CG_AGENT = CASE INDICATEUR
			     WHEN 'TAUX D ACCORD'         THEN PremierTableau.TAccord_G_Agent
			     WHEN 'TAUX CA/FT'            THEN PremierTableau.TCA_G_Agent
				 WHEN 'TAUX RR/FT'            THEN PremierTableau.TRR_G_Agent
				 WHEN 'TAUX DOUBLONS/FT'      THEN PremierTableau.TDoublons_G_Agent
				 WHEN 'TAUX DE HC/FT'         THEN PremierTableau.THCible_G_Agent
				 WHEN 'TAUX INJOIGNABLE/FT'   THEN PremierTableau.TInjoignable_G_Agent
                 WHEN 'TAUX REFUS VPE/FT'     THEN PremierTableau.TRefusVPE_G_Agent
				 WHEN 'TAUX FN/FT'            THEN PremierTableau.TFN_G_Agent
                 WHEN 'PRIME MOYENNE'         THEN PremierTableau.PrimeMoyenneG_Agent
			     ELSE CG_PLATEAU
    END,
 
    -- CAMPAGNE1 PLATEAU
	C1_PLATEAU = CASE INDICATEUR
			     WHEN 'TAUX D ACCORD'         THEN PremierTableau.TAccord_C1_Plateau
			     WHEN 'TAUX CA/FT'            THEN PremierTableau.TCA_C1_Plateau
				 WHEN 'TAUX RR/FT'            THEN PremierTableau.TRR_C1_Plateau
				 WHEN 'TAUX DOUBLONS/FT'      THEN PremierTableau.TDoublons_C1_Plateau
				 WHEN 'TAUX DE HC/FT'         THEN PremierTableau.THCible_C1_Plateau
				 WHEN 'TAUX INJOIGNABLE/FT'   THEN PremierTableau.TInjoignable_C1_Plateau
                 WHEN 'TAUX REFUS VPE/FT'     THEN PremierTableau.TRefusVPE_C1_Plateau
				 WHEN 'TAUX FN/FT'            THEN PremierTableau.TFN_C1_Plateau
                 WHEN 'PRIME MOYENNE'         THEN PremierTableau.PrimeMoyenneC1_Plateau
			     ELSE CG_PLATEAU
    END,
 
    -- CAMPAGNE1 AGENT
	C1_AGENT = CASE INDICATEUR
			     WHEN 'TAUX D ACCORD'         THEN PremierTableau.TAccord_C1_Agent
			     WHEN 'TAUX CA/FT'            THEN PremierTableau.TCA_C1_Agent
				 WHEN 'TAUX RR/FT'            THEN PremierTableau.TRR_C1_Agent
				 WHEN 'TAUX DOUBLONS/FT'      THEN PremierTableau.TDoublons_C1_Agent
				 WHEN 'TAUX DE HC/FT'         THEN PremierTableau.THCible_C1_Agent
				 WHEN 'TAUX INJOIGNABLE/FT'   THEN PremierTableau.TInjoignable_C1_Agent
                 WHEN 'TAUX REFUS VPE/FT'     THEN PremierTableau.TRefusVPE_C1_Agent
				 WHEN 'TAUX FN/FT'            THEN PremierTableau.TFN_C1_Agent
                 WHEN 'PRIME MOYENNE'         THEN PremierTableau.PrimeMoyenneC1_Agent
			     ELSE CG_PLATEAU
    END,
 
    -- CAMPAGNE2 PLATEAU
	C2_PLATEAU = CASE INDICATEUR
			     WHEN 'TAUX D ACCORD'         THEN PremierTableau.TAccord_C2_Plateau
			     WHEN 'TAUX CA/FT'            THEN PremierTableau.TCA_C2_Plateau
				 WHEN 'TAUX RR/FT'            THEN PremierTableau.TRR_C2_Plateau
				 WHEN 'TAUX DOUBLONS/FT'      THEN PremierTableau.TDoublons_C2_Plateau
				 WHEN 'TAUX DE HC/FT'         THEN PremierTableau.THCible_C2_Plateau
				 WHEN 'TAUX INJOIGNABLE/FT'   THEN PremierTableau.TInjoignable_C2_Plateau
                 WHEN 'TAUX REFUS VPE/FT'     THEN PremierTableau.TRefusVPE_C2_Plateau
				 WHEN 'TAUX FN/FT'            THEN PremierTableau.TFN_C2_Plateau
                 WHEN 'PRIME MOYENNE'         THEN PremierTableau.PrimeMoyenneC2_Plateau
			     ELSE CG_PLATEAU
    END,
 
    -- CAMPAGNE2 AGENT
	C2_AGENT = CASE INDICATEUR
			     WHEN 'TAUX D ACCORD'         THEN PremierTableau.TAccord_C2_Agent
			     WHEN 'TAUX CA/FT'            THEN PremierTableau.TCA_C2_Agent
				 WHEN 'TAUX RR/FT'            THEN PremierTableau.TRR_C2_Agent
				 WHEN 'TAUX DOUBLONS/FT'      THEN PremierTableau.TDoublons_C2_Agent
				 WHEN 'TAUX DE HC/FT'         THEN PremierTableau.THCible_C2_Agent
				 WHEN 'TAUX INJOIGNABLE/FT'   THEN PremierTableau.TInjoignable_C2_Agent
                 WHEN 'TAUX REFUS VPE/FT'     THEN PremierTableau.TRefusVPE_C2_Agent
				 WHEN 'TAUX FN/FT'            THEN PremierTableau.TFN_C2_Agent
                 WHEN 'PRIME MOYENNE'         THEN PremierTableau.PrimeMoyenneC2_Agent
			     ELSE CG_PLATEAU
    END,
 
    -- CAMPAGNE3 PLATEAU
	C3_PLATEAU = CASE INDICATEUR
			     WHEN 'TAUX D ACCORD'         THEN PremierTableau.TAccord_C3_Plateau
			     WHEN 'TAUX CA/FT'            THEN PremierTableau.TCA_C3_Plateau
				 WHEN 'TAUX RR/FT'            THEN PremierTableau.TRR_C3_Plateau
				 WHEN 'TAUX DOUBLONS/FT'      THEN PremierTableau.TDoublons_C3_Plateau
				 WHEN 'TAUX DE HC/FT'         THEN PremierTableau.THCible_C3_Plateau
				 WHEN 'TAUX INJOIGNABLE/FT'   THEN PremierTableau.TInjoignable_C3_Plateau
                 WHEN 'TAUX REFUS VPE/FT'     THEN PremierTableau.TRefusVPE_C3_Plateau
				 WHEN 'TAUX FN/FT'            THEN PremierTableau.TFN_C3_Plateau
                 WHEN 'PRIME MOYENNE'         THEN PremierTableau.PrimeMoyenneC3_Plateau
			     ELSE CG_PLATEAU
    END,
 
    -- CAMPAGNE2 AGENT
	C3_AGENT = CASE INDICATEUR
			     WHEN 'TAUX D ACCORD'         THEN PremierTableau.TAccord_C3_Agent
			     WHEN 'TAUX CA/FT'            THEN PremierTableau.TCA_C3_Agent
				 WHEN 'TAUX RR/FT'            THEN PremierTableau.TRR_C3_Agent
				 WHEN 'TAUX DOUBLONS/FT'      THEN PremierTableau.TDoublons_C3_Agent
				 WHEN 'TAUX DE HC/FT'         THEN PremierTableau.THCible_C3_Agent
				 WHEN 'TAUX INJOIGNABLE/FT'   THEN PremierTableau.TInjoignable_C3_Agent
                 WHEN 'TAUX REFUS VPE/FT'     THEN PremierTableau.TRefusVPE_C3_Agent
				 WHEN 'TAUX FN/FT'            THEN PremierTableau.TFN_C3_Agent
                 WHEN 'PRIME MOYENNE'         THEN PremierTableau.PrimeMoyenneC3_Agent
			     ELSE CG_PLATEAU
    END
FROM PremierTableau,DEV_SUPERVISION.dbo.Tableau_Final2

(8*ligne(s) affectée(s))
Msg*208, Niveau*16, État*1, Procédure*ProcStockDevSupervision, Ligne*290
Nom d'objet 'PremierTableau' non valide.

J'ai cette erreur sur mon 2eme update, c'est interdit de mettre 2 updates? si non, où est ce que je me suis loupé ?

Pour cette ligne mon champ CallLocalTimeString contient quelques chose de ce genre : 20140119100642, voila pourquoi j'ai utilisé ceci, ca n'a pas l'air de me poser soucis

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

AND  (LEFT(CallLocalTimeString, 8) = CONVERT(nvarchar, GETDATE(), 112))

Cordialement,

**aieeeuuuuu** · 22/01/2014, 09h37

Envoyé par gardou

J'ai cette erreur sur mon 2eme update, c'est interdit de mettre 2 updates? si non, où est ce que je me suis loupé ?

La CTE n'est valide que pour la requete à laquelle elle est attachée.
Si vous devez faire deux updates, sur le principe, il faudrait reprendre la CTE/// mais...

Pour quoi avez-vous deux tables DEV_SUPERVISION.dbo.Tableau_Final1 et DEV_SUPERVISION.dbo.Tableau_Final2 ?

Par ailleurs, ne contiennent-elles qu'une seule ligne (vous n'avez pas mis de condition de jointure avec votre cte...

Expliquez un peu le contexte et le but de cette procédure, car là j'ai du mal a en comprendre le sens...

**gardou** · 22/01/2014, 10h10

Envoyé par aieeeuuuuu

La CTE n'est valide que pour la requete à laquelle elle est attachée.
Si vous devez faire deux updates, sur le principe, il faudrait reprendre la CTE/// mais...

Pour quoi avez-vous deux tables DEV_SUPERVISION.dbo.Tableau_Final1 et DEV_SUPERVISION.dbo.Tableau_Final2 ?

Par ailleurs, ne contiennent-elles qu'une seule ligne (vous n'avez pas mis de condition de jointure avec votre cte...

Expliquez un peu le contexte et le but de cette procédure, car là j'ai du mal a en comprendre le sens...

Bonjour,

J'ai 2 tables mais en y réflichissant je peux me permettre de mettre tout en une, juste un peu plus de dev coté javascript plus tard.
Dans les tables j'ai mes colonnes INDICATEUR,CG_PERSO,CG_PLATEAU,ect...
Dans la colonne INDICATEUR j'ai mes valeurs en dur VENTE,CA,ect...
Le but de la manipulation etait d'avoir un tableau avec la bonne mise en page direct pour pouvoir le mettre dans un datagrid en javascript.

Procédure stockée + table temporaire

Développement SQL Server

Discussions similaires

Partager

Partager