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
| DECLARE @valeur_a_decoder VARCHAR(100),
@critere1 VARCHAR(100),
@critere2 INT,
@critere3 INT,
@total_numeros_etat1_table2 INT,
@total_numeros_etat1_table INT,
@total_numeros_etat1_table_tmp INT,
@total_numeros_etat2_table INT,
@total_numeros_etat2_table_tmp INT,
@total_numeros1_etat1_table INT,
@total_numeros1_etat1_table_tmp INT,
@total_numeros1_etat2_table INT,
@total_numeros1_etat2_table_tmp INT,
@Start_Period_Date VARCHAR(300),
@End_Period_Date VARCHAR(300)
SELECT @Start_Period_Date = '20090101',
@End_Period_Date = '20090804'
--------------------------
-- SET TEMPORARY VARIABLES
--------------------------
SELECT @total_numeros_etat1_table_tmp = 0,
@total_numeros_etat2_table_tmp = 0,
@total_numeros1_etat1_table_tmp = 0,
@total_numeros1_etat2_table_tmp = 0
DECLARE champs_a_decoder_cursor CURSOR FOR
SELECT champs_a_decoder
FROM dbo.table
WHERE champs1 = 'status'
AND operation_date >= @Start_Period_Date
AND operation_date <= @End_Period_Date
AND error_text = 'status'
FOR READ ONLY
OPEN champs_a_decoder_cursor
FETCH NEXT FROM champs_a_decoder_cursor INTO @valeur_a_decoder
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @critere1 = SUBSTRING(@valeur_a_decoder, 7, 1),
@critere2 = SUBSTRING(@valeur_a_decoder, 8, 3),
@critere3 = SUBSTRING(@valeur_a_decoder, 11, 5)
SELECT CASE @critere1
WHEN 1 THEN 'XXXXX'
ELSE 'YYYYY'
END
-----------
-- SELECT 1
-----------
SELECT @total_numeros_etat1_table2 = COUNT(*)
FROM dbo.table2
WHERE champ1 = @critere1
AND champ2 = @critere2
AND champ3 = @critere3
AND state = 'etat1'
IF @total_numeros_etat1_table2 = 1
BEGIN
SET @total_numeros_etat1_table2_tmp = @total_numeros_etat1_table2_tmp + 1
END
-----------
-- SELECT 2
-----------
SELECT @total_numeros_etat1_table = COUNT(*)
FROM dbo.table2
WHERE champ1 = 'YYYYY'
AND champ2 = @critere2
AND champ3 = @critere3
AND state = 'etat1'
IF @total_numeros_etat1_table_tmp = 1
BEGIN
SET @total_numeros_etat1_table_tmp = @total_numeros_etat1_table_tmp + 1
END
-----------
-- SELECT 3
-----------
SELECT @total_numeros_etat2_table = COUNT(*)
FROM dbo.table2
WHERE champ1 = @critere1
AND champ2 = @critere2
AND champ3 = @critere3
AND state = 'etat2'
IF @total_numeros_etat2_table = 1
BEGIN
SET @total_numeros_etat2_table_tmp = @total_numeros_etat2_table_tmp + 1
END
-----------
-- SELECT 4
-----------
SELECT @total_numeros1_etat1_table = COUNT(*)
FROM dbo.table2
WHERE champ1 = 'XXXXX'
AND champ2 = @critere2
AND champ3 = @critere3
AND state = 'etat1'
IF @total_numeros1_etat1_table = 1
BEGIN
SET @total_numeros1_etat1_table_tmp = @total_numeros1_etat1_table_tmp + 1
END
-----------
-- SELECT 5
-----------
SELECT @total_numeros1_etat2_table = COUNT(*)
FROM dbo.table2
WHERE champ1 = @critere1
AND champ2 = @critere2
AND champ3 = @critere3
AND state = 'etat2'
IF @total_numeros1_etat2_table = 1
BEGIN
SET @total_numeros1_etat2_table_tmp = @total_numeros1_etat2_table_tmp + 1
END
FETCH NEXT FROM champs_a_decoder_cursor INTO @valeur_a_decoder
END
DEALLOCATE champs_a_decoder_cursor
PRINT '-------------- End -------------' |
