Bonjour à tous!!
Voila en reprenant le projet de quelqu'un on me demande d'analyser les calculs dans un tableau de reporting issus d'une vue.
voici les données de la vue:
La requète qui a créé la vue:
J'essaie de m’intéresser au calcul du 263 au mois de mai 2015 avec l'id_site =2.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8 CREATE OR REPLACE FORCE VIEW "AMI2PPR1"."POP_DM_STAT_REACTIF" ("ID_SITE", "NB", "MOIS", "ANS") AS SELECT ID_SITE,AVG((trunc(DATE_T_PROTO,'HH') - trunc(DATE_ENVOI,'HH'))*24) AS NB,to_char(DATE_T_PROTO,'mm') AS MOIS,to_char(DATE_T_PROTO,'YYYY') AS ANS FROM POP_DM JOIN POP_PROJET ON POP_DM.ID_PROJET = POP_PROJET.ID_PROJET WHERE DATE_T_PROTO is not null AND DATE_ENVOI is not null GROUP BY ID_SITE,to_char(DATE_T_PROTO,'mm'),to_char(DATE_T_PROTO,'YYYY') ORDER BY ID_SITE,ANS,MOIS;
J'ai beau essayé de farfouillé avec des requêtes pour rassembler les données pour le calcul je ne retrouve pas la valeur 263.
voici les données pour le champ DATE_T_PROTO pour le moi de mai:
Avec cette requête:
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 05/01/15 10:00:00,000000000 17/12/14 17:00:00,000000000 12/01/15 14:00:00,000000000 14/01/15 14:00:00,000000000 20/01/15 15:00:00,000000000 21/01/15 09:00:00,000000000 17/12/14 15:00:00,000000000 18/12/14 16:00:00,000000000 17/12/14 15:00:00,000000000 18/12/14 15:00:00,000000000 18/12/14 15:00:00,000000000 05/01/15 10:00:00,000000000 05/03/15 10:00:00,000000000 15/12/14 15:00:00,000000000 17/12/14 09:00:00,000000000 15/01/15 18:00:00,000000000 05/02/15 18:00:00,000000000 05/02/15 18:00:00,000000000 10/02/15 15:00:00,000000000 19/02/15 16:00:00,000000000 11/02/15 08:00:00,000000000 11/02/15 08:00:00,000000000 07/04/15 10:00:00,000000000 29/01/15 14:00:00,000000000 22/01/15 17:00:00,000000000 21/05/15 10:00:00,000000000 12/01/15 12:00:00,000000000 14/01/15 17:00:00,000000000 21/01/15 15:00:00,000000000 13/01/15 09:00:00,000000000 14/01/15 17:00:00,000000000 24/02/15 09:00:00,000000000 18/02/15 11:00:00,000000000 13/02/15 14:00:00,000000000 17/03/15 15:00:00,000000000 16/02/15 08:00:00,000000000 20/02/15 08:00:00,000000000 18/02/15 15:00:00,000000000 19/01/15 16:00:00,000000000 22/01/15 17:00:00,000000000 03/02/15 13:00:00,000000000 19/03/15 15:00:00,000000000 16/02/15 18:00:00,000000000 03/02/15 11:00:00,000000000 24/02/15 09:00:00,000000000 03/03/15 09:00:00,000000000 07/04/15 11:00:00,000000000 27/03/15 09:00:00,000000000 17/03/15 18:00:00,000000000 10/03/15 10:00:00,000000000 13/04/15 10:00:00,000000000 09/02/15 17:00:00,000000000 27/03/15 09:00:00,000000000 17/03/15 12:00:00,000000000 26/02/15 09:00:00,000000000 17/03/15 11:00:00,000000000 18/03/15 11:00:00,000000000 27/03/15 09:00:00,000000000 24/04/15 15:00:00,000000000 25/02/15 17:00:00,000000000 27/03/15 08:00:00,000000000 31/03/15 17:00:00,000000000 01/04/15 17:00:00,000000000 08/04/15 16:00:00,000000000 04/05/15 18:00:00,000000000 15/04/15 15:00:00,000000000 10/04/15 11:00:00,000000000 07/01/15 14:00:00,000000000 13/03/15 17:00:00,000000000 24/03/15 10:00:00,000000000 19/05/15 09:00:00,000000000 27/03/15 08:00:00,000000000 30/03/15 08:00:00,000000000 27/03/15 13:00:00,000000000 14/04/15 18:00:00,000000000 06/03/15 12:00:00,000000000 11/03/15 09:00:00,000000000 02/04/15 10:00:00,000000000 03/04/15 09:00:00,000000000 07/04/15 10:00:00,000000000 07/04/15 10:00:00,000000000 07/04/15 10:00:00,000000000 09/03/15 09:00:00,000000000 10/03/15 16:00:00,000000000 13/04/15 18:00:00,000000000 03/04/15 16:00:00,000000000 13/04/15 18:00:00,000000000 13/04/15 18:00:00,000000000 14/04/15 11:00:00,000000000 16/04/15 18:00:00,000000000 16/04/15 18:00:00,000000000 06/05/15 18:00:00,000000000 19/03/15 09:00:00,000000000 01/04/15 09:00:00,000000000 24/03/15 16:00:00,000000000 13/04/15 18:00:00,000000000 14/04/15 11:00:00,000000000 20/02/15 11:00:00,000000000 20/02/15 10:00:00,000000000 20/02/15 10:00:00,000000000 24/02/15 11:00:00,000000000 03/03/15 14:00:00,000000000 12/03/15 18:00:00,000000000 22/04/15 08:00:00,000000000 06/05/15 11:00:00,000000000 23/04/15 15:00:00,000000000 05/05/15 18:00:00,000000000 23/04/15 15:00:00,000000000 23/04/15 16:00:00,000000000 04/05/15 18:00:00,000000000 04/05/15 18:00:00,000000000 04/05/15 18:00:00,000000000 04/05/15 17:00:00,000000000 29/04/15 10:00:00,000000000 04/05/15 17:00:00,000000000 13/05/15 10:00:00,000000000 17/04/15 16:00:00,000000000 19/05/15 15:00:00,000000000 20/05/15 11:00:00,000000000 27/05/15 11:00:00,000000000 27/05/15 17:00:00,000000000 28/05/15 17:00:00,000000000 28/05/15 17:00:00,000000000 03/06/15 15:00:00,000000000 04/05/15 18:00:00,000000000 19/05/15 10:00:00,000000000 03/06/15 15:00:00,000000000 03/06/15 15:00:00,000000000 07/05/15 16:00:00,000000000 11/05/15 18:00:00,000000000 13/05/15 16:00:00,000000000
voici les données pour le champ DATE_ENVOI pour le moi de mai:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4 SELECT DATE_T_PROTO FROM POP_DM JOIN POP_PROJET ON POP_DM.ID_PROJET = POP_PROJET.ID_PROJET WHERE ID_SITE = '2';
Avec cette requête:
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 17/12/14 13:00:00,000000000 08/01/15 11:00:00,000000000 12/01/15 13:00:00,000000000 12/01/15 13:00:00,000000000 13/01/15 18:00:00,000000000 17/12/14 12:00:00,000000000 18/12/14 11:00:00,000000000 17/12/14 15:00:00,000000000 18/12/14 11:00:00,000000000 18/12/14 15:00:00,000000000 18/12/14 16:00:00,000000000 05/03/15 10:00:00,000000000 15/01/15 15:00:00,000000000 05/02/15 17:00:00,000000000 05/02/15 14:00:00,000000000 09/02/15 17:00:00,000000000 10/02/15 16:00:00,000000000 11/02/15 08:00:00,000000000 11/02/15 08:00:00,000000000 15/01/15 17:00:00,000000000 15/01/15 11:00:00,000000000 22/01/15 15:00:00,000000000 13/03/15 09:00:00,000000000 12/01/15 11:00:00,000000000 12/01/15 11:00:00,000000000 14/01/15 16:00:00,000000000 12/01/15 19:00:00,000000000 13/01/15 17:00:00,000000000 19/02/15 12:00:00,000000000 18/02/15 11:00:00,000000000 13/02/15 12:00:00,000000000 17/03/15 15:00:00,000000000 16/02/15 08:00:00,000000000 16/02/15 13:00:00,000000000 18/02/15 15:00:00,000000000 19/01/15 13:00:00,000000000 22/01/15 10:00:00,000000000 02/02/15 18:00:00,000000000 06/02/15 15:00:00,000000000 26/01/15 15:00:00,000000000 26/01/15 17:00:00,000000000 13/02/15 15:00:00,000000000 03/03/15 09:00:00,000000000 27/02/15 11:00:00,000000000 25/03/15 09:00:00,000000000 09/03/15 09:00:00,000000000 09/03/15 11:00:00,000000000 13/03/15 17:00:00,000000000 13/03/15 18:00:00,000000000 05/02/15 11:00:00,000000000 13/03/15 10:00:00,000000000 25/02/15 15:00:00,000000000 25/02/15 17:00:00,000000000 17/03/15 11:00:00,000000000 17/03/15 10:00:00,000000000 18/03/15 11:00:00,000000000 20/03/15 15:00:00,000000000 25/02/15 16:00:00,000000000 26/03/15 11:00:00,000000000 31/03/15 17:00:00,000000000 01/04/15 14:00:00,000000000 07/04/15 08:00:00,000000000 07/04/15 08:00:00,000000000 07/04/15 08:00:00,000000000 07/04/15 17:00:00,000000000 08/04/15 18:00:00,000000000 07/01/15 10:00:00,000000000 13/03/15 14:00:00,000000000 24/03/15 09:00:00,000000000 27/03/15 13:00:00,000000000 25/03/15 17:00:00,000000000 27/03/15 11:00:00,000000000 27/03/15 11:00:00,000000000 30/03/15 11:00:00,000000000 05/03/15 16:00:00,000000000 10/03/15 19:00:00,000000000 02/04/15 08:00:00,000000000 01/04/15 18:00:00,000000000 02/04/15 09:00:00,000000000 03/04/15 19:00:00,000000000 03/04/15 16:00:00,000000000 07/04/15 09:00:00,000000000 13/04/15 09:00:00,000000000 27/02/15 18:00:00,000000000 03/03/15 18:00:00,000000000 16/03/15 14:00:00,000000000 03/04/15 16:00:00,000000000 13/04/15 13:00:00,000000000 13/04/15 18:00:00,000000000 13/04/15 19:00:00,000000000 16/04/15 15:00:00,000000000 16/04/15 16:00:00,000000000 17/04/15 18:00:00,000000000 18/03/15 17:00:00,000000000 27/03/15 13:00:00,000000000 24/03/15 16:00:00,000000000 13/04/15 09:00:00,000000000 14/04/15 10:00:00,000000000 20/02/15 10:00:00,000000000 20/02/15 09:00:00,000000000 20/02/15 10:00:00,000000000 24/02/15 11:00:00,000000000 02/03/15 16:00:00,000000000 12/03/15 12:00:00,000000000 21/04/15 16:00:00,000000000 22/04/15 16:00:00,000000000 23/04/15 11:00:00,000000000 23/04/15 10:00:00,000000000 23/04/15 11:00:00,000000000 23/04/15 15:00:00,000000000 28/04/15 13:00:00,000000000 27/04/15 11:00:00,000000000 27/04/15 10:00:00,000000000 28/04/15 14:00:00,000000000 28/04/15 14:00:00,000000000 30/04/15 19:00:00,000000000 05/05/15 09:00:00,000000000 17/04/15 16:00:00,000000000 19/05/15 15:00:00,000000000 20/05/15 10:00:00,000000000 22/05/15 10:00:00,000000000 22/05/15 15:00:00,000000000 26/05/15 17:00:00,000000000 28/05/15 14:00:00,000000000 28/05/15 14:00:00,000000000 03/06/15 14:00:00,000000000 04/06/15 11:00:00,000000000 28/04/15 11:00:00,000000000 13/05/15 14:00:00,000000000 03/06/15 13:00:00,000000000 03/06/15 12:00:00,000000000 05/05/15 10:00:00,000000000 07/05/15 08:00:00,000000000 13/05/15 15:00:00,000000000 11/05/15 18:00:00,000000000 13/05/15 16:00:00,000000000 18/05/15 14:00:00,000000000 18/05/15 18:00:00,000000000
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4 SELECT DATE_ENVOI FROM POP_DM JOIN POP_PROJET ON POP_DM.ID_PROJET = POP_PROJET.ID_PROJET WHERE ID_SITE = '2';
Quelqu'un pourrai-t-il m'aider ? Car la je bloque.
Partager