Problème de script SQL : left join et sélection MAX(date)

**yoann29200** · 11/01/2016, 11h46

bonjour à tous.

Voilà, j'ai un projet qui mobilise mes compétences SQL, qui datent de quelques années... J'ai l'impression de repartir de zéro, et ce n'est pas simple!!!!
Je commence mes scripts simples, et j'ai déjà des soucis...

J'attaque une base de données via un petit outil de Business intelligence. L'idée est d'attaquer une table (Ensemble_des_pleins) et d'en extraire des infos précises. Cette table répertorie tous les pleins réalisés par mes véhicules (grosse masse d'information). Mon but, c'est d'être capable de sommer les volumes de carburant sur une période donnée, et de connaître le nombre de km parcouru (à chaque fois qu'un chauffeur fait le plein, il indique le nombre de km au compteur).

Voici une vue de la table Ensemble_des_pleins (qui ne s'appelle pas comme cela dans ma base réelle) (cf piece jointe "table des pleins").
Nom : table des pleins.PNG
Affichages : 322
Taille : 25,2 Ko

Nom : table des pleins.PNG
Affichages : 322
Taille : 25,2 Ko

Dans un premier temps, j'essaye de récupérer le dernier plein de la période (la période sera un champ de filtre dans la feuille excel de restitution).

Pour cela, j'ai rédigé ce morceaux de script :

Code :

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

1
2
3
4
Select PLEINS.ID_VEHICULE,
    Max(PLEINS.DATPLN) As DATE_DERNIER_PLEIN
  From PLEINS
  Group By PLEINS.ID_VEHICULE

Je récupère bien le dernier plein réalisé pour chaque véhicule. Maintenant, l'idée c'est de n'avoir qu'une ligne par véhicule, de la table Ensemble_des_pleins, et d'y rajouter la date de mon dernier plein.

J'ai donc commencé à rédiger ceci, sans succès :

Code :

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

1
2
3
4
5
6
7
8
9
10
Select PLEINS.ID_VEHICULE, SUM(VOLPLN) as VOLUME_DES_PLEINS, DERNIERPLEIN.DATE_DERNIER_PLEIN
From PLEINS
 
  Left Join (Select PLEINS.ID_VEHICULE,
                Max(PLEINS.DATPLN) As DATE_DERNIER_PLEIN
                From PLEINS
                Group By PLEINS.ID_VEHICULE) As DERNIERPLEIN
  ON PLEINS.ID = DERNIERPLEIN.ID
 
GROUP BY PLEINS.ID_VEHICULE

Et je n'ai rien en faisant cela. Je dois avoir un soucis de logique SQL, mais je n'arrive pas à savoir où.

Merci d'avance pour votre aide.

PS : je ne suis pas habitué du site, peut être y a t il un moyen plus sexy d'insérer du contenu SQL?

**tatayo** · 11/01/2016, 11h53

Bonjour,
Je pense que tu mélanges les Id dans la requête. La sous-requête renvoie un Id_Vehicule de la table Ensemble_des_pleins, que tu compares ensuite avec Id de la même table.

Tatayo.

P.S. pour la mise en page des requêtes, il faut utiliser la balise CODE=SQL.

**yoann29200** · 11/01/2016, 12h01

Effectivement j'ai corrigé le script après la première version et j'ai oublié que je ne récupère dans ma vue que l'ID_VEHICULE, et non plus l'ID de la table originelle. Je teste cela de suite.

**yoann29200** · 11/01/2016, 12h10

J'ai corrigé comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
Select PLEINS.ID_VEHICULE,
  Sum(PLEINS.VOLPLN) As VOLUME_DES_PLEINS,
  DERNIERPLEIN.DATE_DERNIER_PLEIN
From PLEINS
  Left Join (Select PLEINS.ID_VEHICULE,
                Max(PLEINS.DATPLN) As DATE_DERNIER_PLEIN
                From PLEINS
                Group By PLEINS.ID_VEHICULE) As DERNIERPLEIN 
  On PLEINS.ID_VEHICULE = DERNIERPLEIN.ID_VEHICULE
Group By PLEINS.ID_VEHICULE

Mais toujours rien...

**yoann29200** · 11/01/2016, 15h17

Bon j'ai réussi à sortir des données en bornant mon script pour limiter la volumétrie :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Select PLEINS.ID_VEHICULE,
  Sum(PLEINS.VOLPLN) As VOLUME_DES_PLEINS,
  DERNIERPLEIN.DATE_DERNIER_PLEIN
From PLEINS
  Left Join (Select PLEINS.ID_VEHICULE,
              Max(PLEINS.DATPLN) As DATE_DERNIER_PLEIN
              From PLEINS
              Group By PLEINS.ID_VEHICULE) As DERNIERPLEIN 
  On PLEINS.ID_VEHICULE = DERNIERPLEIN.ID_VEHICULE
WHERE YEAR(PLEINS.DATPLN) ="2015"
AND PLEINS.ID_VEHICULE < "1000"
Group By PLEINS.ID_VEHICULE

J'obtiens des résultats, mais l'outil que j'utilise pour extraire les données (INSIDE STUDIO) plante et me met un message d'erreur :
« Une exception non gérée est survenue dans un composant dans votre application. Si vous cliquez sur "Continuer", l'application va ignorer cette erreur et tenter de continuer.
La référence d'objet n'est pas définie à une instance d'un objet. »

Pourtant je ne vois pas ce que mon script peut avoir comme soucis. Je ne comprends pas.

**Emmanuel R** · 12/01/2016, 14h19

Bonjour,

Lorsqu'on utilise des guillemets doubles, en général en SQL cela fait référence à un champ. Du coup je trouve étonnant que votre application sorte quand même des données.
Dans votre filtre where essayez de les retirer, vu que vous restreignez sur des nombres.

Code :

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

1
2
WHERE YEAR(PLEINS.DATPLN) =2015
AND PLEINS.ID_VEHICULE < 1000

**soazig** · 13/01/2016, 15h49

Si la date du dernier plein est sur la même période que le volume des pleins, je ne vois pas pourquoi il y aurait une jointure externe on peut obtenir le même résultat directement. Ce qui donne

Code :

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

1
2
3
4
5
6
7
elect PLEINS.ID_VEHICULE,
  Sum(PLEINS.VOLPLN) As VOLUME_DES_PLEINS,
	Max(PLEINS.DATPLN) As DATE_DERNIER_PLEIN
From PLEINS
WHERE YEAR(PLEINS.DATPLN) =2015
AND PLEINS.ID_VEHICULE < 1000
Group By PLEINS.ID_VEHICULE

Avec cette requête, date_dernier_plein sera en la date du dernier plein effectué en 2015, s'il y a eu un plein en 2016, il ne sera pas pris en compte
J'ai laissé ton critère sur la date des pleins, mais si la date des pleins est indexé il vaudrait mieux comparer les dates ainsi

Code :

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

1
2
3
4
5
6
7
select PLEINS.ID_VEHICULE,
  Sum(PLEINS.VOLPLN) As VOLUME_DES_PLEINS,
	Max(PLEINS.DATPLN) As DATE_DERNIER_PLEIN
From PLEINS
WHERE  PLEINS.DATPLN>='20150101' and PLEINS.DATPLN<'20160101'
AND PLEINS.ID_VEHICULE < 1000
Group By PLEINS.ID_VEHICULE

[edit] J'ai modifié le critère de fin de date < au lieu de <=[/edit]

Bonne journée

**yoann29200** · 14/01/2016, 11h28

Bonjour et merci pour les précisions.
J'ai retravaillé le code avec les contraintes dont je souffre.
Je vais expliquer par l'exemple :
- nous sommes en décembre 2015 (PERIODE = 1512). Le champ période est au format texte.
- Je souhaite avoir la date du dernier plein fait sur la période 1512, ainsi que le kilométrage renseigné par le chauffeur.
- je souhaite avoir la date du dernier plein de la période antérieure, ainsi que le kilométrage renseigné par le chauffeur.
- En faisant la différence de ces deux indices kilométriques, j'ai le km de la période.

C'est important de comprendre que ces deux dates me permettent de définir la période d'analyse, et de ressortir un km pour la période en question. Donc, d'un véhicule à l'autre, on n'a pas la même chose.

Voici mon script retravaillé :

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
Select histas24.*,DATE_PREMIER_PLEIN,KM_PREMIER_PLEIN,DATE_DERNIER_PLEIN,KM_DERNIER_PLEIN
From histas24
Left Join (Select histas24.ID_VEHICULE,
          Max(histas24.DATPLN) As DATE_PREMIER_PLEIN,
          max(histas24.IDXKLM) As KM_PREMIER_PLEIN
          From histas24
          Where Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') = Date_Sub(Str_To_Date(Concat('1512', '01'), '%y%m%d'), Interval 1 Month)
          Group By histas24.ID_VEHICULE) As PREMIERPLEINPER 
On histas24.ID_VEHICULE = PREMIERPLEINPER.ID_VEHICULE
 
Left Join (Select histas24.ID_VEHICULE,
          Max(histas24.DATPLN) As DATE_DERNIER_PLEIN,
          MAX(histas24.IDXKLM) As KM_DERNIER_PLEIN
          From histas24
          Where Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') = Str_To_Date(Concat('1512', '01'), '%y%m%d')
          Group By histas24.ID_VEHICULE) As DERNIERPLEINPER 
On histas24.ID_VEHICULE = DERNIERPLEINPER.ID_VEHICULE
Group By histas24.ID_VEHICULE

Ce code n'est qu'un morceau d'un code plus développé. L'idée c'est que ce morceau de code soit une vue dans laquelle je vienne piocher des informations dans ma requête principale.
Pour l'instant, si je prends les select de jointure, individuellement ils fonctionnent bien. Mais dans ce morceau code, je n'ai pas d'info en restitution.
Là franchement, je sèche complètement!!!

**yoann29200** · 14/01/2016, 11h53

Petite modification, dans les LEFT JOIN, j'ai borné la période d'étude comme dans la requête principale.

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
Select histas24PER.*,DATE_PREMIER_PLEIN,KM_PREMIER_PLEIN,DATE_DERNIER_PLEIN,KM_DERNIER_PLEIN, 
From 
  (SELECT histas24.*
  FROM histas24
  WHERE (Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') = Str_To_Date(Concat('1512', '01'), '%y%m%d')) 
  Or (Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') = Date_Sub(Str_To_Date(Concat('1512', '01'), '%y%m%d'), Interval 1 Month))) as histas24PER
 
Left Join (Select histas24PER1.ID_VEHICULE,
          Max(histas24PER1.DATPLN) As DATE_PREMIER_PLEIN,
          max(histas24PER1.IDXKLM) As KM_PREMIER_PLEIN
          From (SELECT histas24.*
                FROM histas24
                WHERE (Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') = Str_To_Date(Concat('1512', '01'), '%y%m%d')) 
                Or (Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') = Date_Sub(Str_To_Date(Concat('1512', '01'), '%y%m%d'), Interval 1 Month))) as histas24PER1
          Where Str_To_Date(Concat(histas24PER1.PERIODE, '01'), '%y%m%d') = Date_Sub(Str_To_Date(Concat('1512', '01'), '%y%m%d'), Interval 1 Month)
          Group By histas24PER1.ID_VEHICULE) As PREMIERPLEINPER 
On histas24PER.ID_VEHICULE = PREMIERPLEINPER.ID_VEHICULE
 
Left Join (Select histas24PER2.ID_VEHICULE,
          Max(histas24PER2.DATPLN) As DATE_DERNIER_PLEIN,
          MAX(histas24PER2.IDXKLM) As KM_DERNIER_PLEIN
          From (SELECT histas24.*
                FROM histas24
                WHERE (Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') = Str_To_Date(Concat('1512', '01'), '%y%m%d')) 
                Or (Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') = Date_Sub(Str_To_Date(Concat('1512', '01'), '%y%m%d'), Interval 1 Month))) as histas24PER2
          Where Str_To_Date(Concat(histas24PER2.PERIODE, '01'), '%y%m%d') = Str_To_Date(Concat('1512', '01'), '%y%m%d')
          Group By histas24PER2.ID_VEHICULE) As DERNIERPLEINPER 
On histas24PER.ID_VEHICULE = DERNIERPLEINPER.ID_VEHICULE
Group By histas24PER.ID_VEHICULE

Je récupère bien les champs mais j'ai des valeurs nulles dans mes tables/vues. Il faut que je trouve un moyen de gérer le cas.

**soazig** · 17/01/2016, 11h42

Bonjour,
Ta dernière requete n'a rien à voir avec ta demande initiale, j'ai un peu de mal à la comprendre et notamment je ne comprends pas pourquoi tu filtre dans ta sous requete histas24PER1 avec des critères concernant histas24PER.
Je n'ai pas vu non plus la différence entre histas24PER1, et histas24PER2.
Pour simplifier tes requetes et t'y retrouver plus facilement je te conseille

Si ton SGD le supporte d'utiliser une CTL definissant les données de la p
D'utiliser une table calendrier contenant la date et la période, avec éventuellement une table période afin de pouvoir calculer le mois précédent facilement.Voir http://sqlpro.developpez.com/cours/gestiontemps/#L3.1 C'est assez facile à faire et cela te simplifiera la vie, une petite recherche et tu devrais trouver ton bonheur. La clé de ta table période doit être un entier séquentiel, par exemple le nombre de mois depuis un évenement donné, car sinon avec ton 1512 comment tu fais pour trouver facilement le mois suivant 1601, sans algorithme compliqué ou 15 cast.

Cordialement
Soazig

**aieeeuuuuu** · 18/01/2016, 10h06

Bonjour,

Vos filtres sur les dates ne sont pas cherchables, aucun index ne pourra être utilisé, ce qui nuit principalement aux temps de réponse.
Vous devriez les transformer en recherches sur des plages de dates.

les fonctions analytiques pourraient aussi simplifier la requete et la rendre plus performante, encore faut-il que votre SGBD les supporte, quel est-il ?

Enfin, un jeu d'essai + résultat attendu augmenterait vos chances d'obtenir une aide précise et rapide.

**yoann29200** · 21/01/2016, 12h07

En réalité, il s'agit de la continuité de ma demande initiale (pour des soucis de confidentialité, je renommais mes tables au début. Sur les derniers j'ai arrêté).
Il faut avoir en tête que je ne travaille pas sur du VB, mais bien sur du SQL, avec les contraintes que cela représente.
D'autre part, je ne suis pas développeur, ni même informaticien. Alors mes connaissances SQL sont malheureusement limitées.
Mais j'ai tout de même réussi à extraire ce dont j'avais besoins.
A noter que dans le script suivant, le @periode est une variable libre, fonction qui est disponible dans l'outil que j'utilise.

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
Select histas24PER.*,
  PREMIERPLEINPER.DATE_PREMIER_PLEIN,
  PREMIERPLEINPER.KM_PREMIER_PLEIN,
  DERNIERPLEINPER.DATE_DERNIER_PLEIN,
  DERNIERPLEINPER.KM_DERNIER_PLEIN,
  typemotorisation.*,
  vehicule.*,
  typevehicule.*,
  exploit.*,
  refmarq.*,
  marque.*,
  socloc.*,
  typeproduit.*,
  chauff.*
From (Select histas24.*
  From histas24
  Where ((Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') =
        Str_To_Date(Concat(@periode, '01'), '%y%m%d')) Or
      (Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') =
        Date_Sub(Str_To_Date(Concat(@periode, '01'), '%y%m%d'), Interval 1
        Month))) And histas24.ID_TYPEPRODUIT Like '3') As histas24PER
  Left Join (Select histas24PER1.ID_VEHICULE,
    Max(histas24PER1.DATPLN) As DATE_PREMIER_PLEIN,
    Max(histas24PER1.IDXKLM) As KM_PREMIER_PLEIN
  From (Select histas24.*
    From histas24
    Where (Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') =
      Str_To_Date(Concat(@periode, '01'), '%y%m%d')) Or
      (Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') =
      Date_Sub(Str_To_Date(Concat(@periode, '01'), '%y%m%d'), Interval
      1 Month))) As histas24PER1
  Where Str_To_Date(Concat(histas24PER1.PERIODE, '01'), '%y%m%d') =
    Date_Sub(Str_To_Date(Concat(@periode, '01'), '%y%m%d'), Interval 1 Month)
  Group By histas24PER1.ID_VEHICULE) As PREMIERPLEINPER
    On histas24PER.ID_VEHICULE = PREMIERPLEINPER.ID_VEHICULE
  Left Join (Select histas24PER2.ID_VEHICULE,
    Max(histas24PER2.DATPLN) As DATE_DERNIER_PLEIN,
    Max(histas24PER2.IDXKLM) As KM_DERNIER_PLEIN
  From (Select histas24.*
    From histas24
    Where (Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') =
      Str_To_Date(Concat(@periode, '01'), '%y%m%d')) Or
      (Str_To_Date(Concat(histas24.PERIODE, '01'), '%y%m%d') =
      Date_Sub(Str_To_Date(Concat(@periode, '01'), '%y%m%d'), Interval
      1 Month))) As histas24PER2
  Where Str_To_Date(Concat(histas24PER2.PERIODE, '01'), '%y%m%d') =
    Str_To_Date(Concat(@periode, '01'), '%y%m%d')
  Group By histas24PER2.ID_VEHICULE) As DERNIERPLEINPER
    On histas24PER.ID_VEHICULE = DERNIERPLEINPER.ID_VEHICULE
  Left Join vehicule On histas24PER.ID_VEHICULE = vehicule.ID
  Left Join typemotorisation On vehicule.ID_TYPEMOTORISATION =
    typemotorisation.ID
  Left Join typevehicule On vehicule.ID_TYPEVEHICULE = typevehicule.ID
  Left Join exploit On vehicule.ID_EXPLOIT = exploit.ID
  Left Join refmarq On vehicule.ID_REFMARQ = refmarq.ID
  Left Join marque On refmarq.ID_MARQUE = marque.ID
  Left Join socloc On vehicule.ID_SOCLOC = socloc.ID
  Left Join typeproduit On histas24PER.ID_TYPEPRODUIT = typeproduit.ID,
  chauff

Le résultat en restituion EXCEL :

Nom : Capture.PNG
Affichages : 282
Taille : 27,9 Ko

**yoann29200** · 21/01/2016, 12h08

C'est perfectible, et j'aurais bien aimé traité le cas d'une absence de plein en periode P, ou P-1. Il va falloir que j'enrichisse mon code.

**yoann29200** · 21/01/2016, 15h49

Je tiens à préciser que mon SGBD ne permet que de faire de la requête sql. C'est un outil de restitution, alors les logiques de programmation ne s'applique pas. Pour que vous rigoliez, voila ce qu'on me demande de migrer dans notre nouvel outil :

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
SELECT DISTINCT CONSO.PER, conso.TYPE,conso.MARQUE, conso.REF_MARQUE, conso.vehicule, CONSO.STATUT AS statut, 
conso.km, 
conso.gasoil AS litre, 
conso.km0 AS KM2, 
conso.gasoil0 AS litre2, 
conso.conso  AS CONSO, 
conso.conso0  AS CONSO_2M, 
isnull(log.conso,0) AS ConsoAnVeh,
isnull(log.KM,0) AS KMAnVeh,
isnull(log.GASOIL,0) AS GOAnVeh,
isnull(logmo.conso,0) AS ConsoAnMod,
isnull(logmo.KM,0) AS KMAnMod,
isnull(logmo.GASOIL,0) AS GOAnMod,
conso.periode AS periode, 
conso.analytique,
DATEPART(YEAR,conso.datedebut)*10000+datepart(MONTH,conso.datedebut)*100+datepart(DAY,conso.datedebut) AS datedebut, 
datepart(YEAR,conso.dateFin)*10000+datepart(MONTH,conso.dateFin)*100+datepart(DAY,conso.dateFin) AS  dateFin,
CAST((CONSO.datefin-conso.DateDebut) AS INTEGER) AS duree,
 
CASE WHEN ((aff.Utilisateur IS  NULL) OR ( aff.Utilisateur LIKE '%A DEFINIR%')) THEN det.LIBSOCLOC  ELSE (CASE WHEN aff.Utilisateur LIKE '%LAHAYE LOCATION%' THEN 'LAH_LOCATION' ELSE (CASE WHEN aff.Utilisateur LIKE '%LAHAYE LE MANS%' THEN 'LAH_LMANS' ELSE (CASE WHEN aff.Utilisateur LIKE '%LAHAYE NANTES%' THEN 'LAH_NANTES' ELSE aff.Utilisateur END)  END)  END) END  AS Utilisateur,
CASE WHEN  ((aff.service IS  NULL) OR ( aff.service LIKE '%A DEFINIR%')) THEN det.NOM_MECANO ELSE aff.service END  AS service,
CASE WHEN  aff.statut IS NOT NULL THEN aff.statut ELSE 'Roulant' END  AS old_statut,
CASE WHEN (conso.kmpost<=0) THEN 
	(CASE WHEN ((conso.km)<26500 AND (conso.km)>0 )THEN conso.km ELSE 
	(CASE WHEN ((CONSO.kmcertain+conso.kmestimeDeb)<26500 AND (CONSO.kmcertain+conso.kmestimeDeb)>0) THEN (CONSO.kmcertain+conso.kmestimeDeb) ELSE 
	(CASE WHEN ((CONSO.kmcertain)<26500 AND (CONSO.kmcertain)>0 )THEN (CONSO.kmcertain) ELSE 0 END ) END )  END ) ELSE 
	(CASE WHEN (abs(CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain)>26500 OR (CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain)<=0) THEN 
	(CASE WHEN (CONSO.kmestimefin*conso.kmestimeDeb=0 AND conso.conso>20) THEN (CASE WHEN ((conso.km)<26500 AND (conso.km)>0 )THEN conso.km ELSE CONSO.kmcertain END) ELSE 0 END) ELSE 
	(CASE WHEN (CONSO.kmestimefin*conso.kmestimeDeb=0 AND conso.conso>20) THEN (CASE WHEN ((conso.km)<26500 AND (conso.km)>0 )THEN conso.km ELSE 0 END) ELSE CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain END) END) END 
AS kmper_old,
 
CASE WHEN ((conso.km)<26500 AND (conso.km)>0 AND conso.conso>18  AND CAST((CONSO.datefin-conso.DateDebut) AS INTEGER)>27 )THEN conso.km ELSE 
	(CASE WHEN (abs(CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain)>26500 OR (CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain)<=0) THEN 
		(CASE WHEN (CONSO.kmcertain<0 OR CONSO.kmcertain>1200)  THEN 0 ELSE CONSO.kmcertain END)
+ (CASE WHEN (CONSO.kmestimefin<0 OR CONSO.kmestimefin>9000)  THEN 0 ELSE CONSO.kmestimefin END)  ELSE (CASE WHEN (CONSO.kmestimefin*conso.kmestimeDeb<=0) THEN 
	(CASE WHEN (CONSO.kmcertain<0 OR CONSO.kmcertain>26500)  THEN 0 ELSE CONSO.kmcertain END)  
ELSE CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain END) END) END 
AS kmper,
 
ISNULL(tvkp.KMS,0) AS KM_Tmd, ISNULL(tvkp.KMSFIN,0) AS KMSFIN_Tmd,
 
CASE WHEN ((CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain)<26500 AND (CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain)>0 
AND (CONSO.kmestimefin)>0 AND CAST((CONSO.datefin-conso.DateDebut) AS INTEGER)<90 )
THEN (CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain) ELSE 
(CASE WHEN (abs(CONSO.km)<26500 AND (CONSO.km)>0 AND CAST((CONSO.datefin-conso.DateDebut) AS INTEGER)<120 ) 
THEN 
CONSO.km
ELSE
(CASE WHEN (CONSO.kmcertain<0 OR CONSO.kmcertain>26500)  THEN 0 ELSE CONSO.kmcertain END)
+ (CASE WHEN (CONSO.kmestimefin<0 OR CONSO.kmestimefin>10000)  THEN 0 ELSE CONSO.kmestimefin END)  
END)
END AS kmper40,
 
conso.goper AS goper,
 
CASE WHEN (CASE WHEN ((conso.km)<26500 AND (conso.km)>0 AND conso.conso>18  AND CAST((CONSO.datefin-conso.DateDebut) AS INTEGER)>27 )THEN conso.km ELSE 
(CASE WHEN (abs(CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain)>26500 OR (CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain)<=0) 
THEN 
(CASE WHEN (CONSO.kmcertain<0 OR CONSO.kmcertain>1200)  THEN 0 ELSE CONSO.kmcertain END)
+ (CASE WHEN (CONSO.kmestimefin<0 OR CONSO.kmestimefin>9000)  THEN 0 ELSE CONSO.kmestimefin END)  
ELSE 
(CASE WHEN (CONSO.kmestimefin*conso.kmestimeDeb<=0) 
THEN (CASE WHEN (CONSO.kmcertain<0 OR CONSO.kmcertain>26500)  THEN 0 ELSE CONSO.kmcertain END)  
ELSE CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain END) END) END)<>0 
THEN 
(CASE WHEN ((conso.km)<26500 AND (conso.km)>0 AND conso.conso>18  AND CAST((CONSO.datefin-conso.DateDebut) AS INTEGER)>27 )THEN conso.km ELSE 
(CASE WHEN (abs(CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain)>26500 OR (CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain)<=0) 
THEN 
(CASE WHEN (CONSO.kmcertain<0 OR CONSO.kmcertain>1200)  THEN 0 ELSE CONSO.kmcertain END)
+ (CASE WHEN (CONSO.kmestimefin<0 OR CONSO.kmestimefin>9000)  THEN 0 ELSE CONSO.kmestimefin END)  
ELSE 
(CASE WHEN (CONSO.kmestimefin*conso.kmestimeDeb<=0) 
THEN (CASE WHEN (CONSO.kmcertain<0 OR CONSO.kmcertain>26500)  THEN 0 ELSE CONSO.kmcertain END)  
ELSE CONSO.kmestimefin+conso.kmestimeDeb+CONSO.kmcertain END) END) END)/100*isnull(log.conso,isnull(logmo.conso,0))
ELSE 0 END 
AS Consomme,
 
conso.gasoil+conso.stk_FdM_km-conso.stk_DdM_km AS Consomme_varStk_preleve,
conso.Reservoir AS Reservoir,
CASE WHEN (conso.Reservoir<>0 AND (conso.Reservoir-kmestimefin/100*isnull(log.conso,isnull(logmo.conso,0)))>0)THEN conso.Reservoir-kmestimefin/100*isnull(log.conso,isnull(logmo.conso,0))ELSE 0 END AS stk_FdM_reservoir_old1,
CASE WHEN conso.Reservoir<>0 THEN conso.Reservoir-kmestimeDeb/100*isnull(log.conso,isnull(logmo.conso,0))ELSE 0 END AS stk_DdM_reservoir_old1,
 
CASE WHEN (conso.Reservoir>0 AND (conso.Reservoir-kmestimefin/100*isnull(log.conso,isnull(logmo.conso,33)))>0 AND kmestimefin>=0)
THEN conso.Reservoir-kmestimefin/100*isnull(log.conso,isnull(logmo.conso,33))ELSE conso.Reservoir END AS stk_FdM_reservoir,
CASE WHEN (conso.Reservoir>0 AND (conso.Reservoir-kmestimeDeb/100*isnull(log.conso,isnull(logmo.conso,33)))>0 AND kmestimeDeb>=0)
THEN conso.Reservoir-kmestimeDeb/100*isnull(log.conso,isnull(logmo.conso,33))ELSE conso.Reservoir END AS stk_DdM_reservoir,
 
(conso.stk_FdM_km) AS stk_FdM_reservoir_old,
(conso.stk_DdM_km) AS stk_DdM_reservoir_old,
YEAR(conso.DATE_MES)*10000+MONTH(conso.DATE_MES)*100+DAY(conso.DATE_MES) AS MES,
CONSO.kmestimefin,
conso.kmestimeDeb,
CONSO.kmcertain,
conso.kmpost,
CONSO.KMINCOHERENT
 
FROM
	(SELECT 
	'M' AS PER,
	conso.TYPE,
	conso.periode AS periode,
	conso.vehicule,
	conso.datedebut, 
	conso.dateFin,
	isnull(PremPln.volPln,0) AS volPln,
	SUM(conso.Km) AS kmper,
	SUM(conso.gasoil) AS goper,
	SUM(conso.CONSOKm) AS km,
	isnull(SUM(conso0.CONSOKm),0) AS kmX,
	SUM(conso.CONSOVOLPLN) AS gasoil1,
	isnull(SUM(conso0.CONSOVOLPLNFIN),0) AS gasoilX,
	(SUM(conso.CONSOVOLPLNFIN)-isnull(PremPln.volPln*0,0)) AS gasoil_OLD,
	(SUM(conso.CONSOVOLPLNFIN)- (CASE WHEN PremPln.periode=conso.periode THEN isnull(PremPln.volPln,0) ELSE 0 END)) AS gasoil,
	CAST((
		(CASE WHEN SUM(conso.CONSOKm)=0 THEN 0 ELSE
		(CASE WHEN (SUM(conso.CONSOVOLPLNFIN)/SUM(conso.CONSOKm)*100)<3  THEN 0 ELSE
		(CASE WHEN (SUM(conso.CONSOVOLPLNFIN)/SUM(conso.CONSOKm)*100)>160  THEN 0 ELSE
		((SUM(conso.CONSOVOLPLNFIN)- (CASE WHEN PremPln.periode=conso.periode THEN isnull(PremPln.volPln,0) ELSE 0 END))/SUM(conso.CONSOKm)*100)END)END)
	END))AS DECIMAL(8,2)) 
	AS CONSO,
 
	CAST((
		(CASE WHEN (SUM(conso.CONSOKm)+isnull(SUM(conso0.CONSOKm),0))=0 THEN 0 ELSE
		(CASE WHEN (((SUM(conso.CONSOVOLPLNFIN)-isnull(PremPln.volPln,0))+isnull(SUM(conso0.CONSOVOLPLNFIN),0)-isnull(conso0.volPln,0))/(SUM(conso.CONSOKm)+isnull(SUM(conso0.CONSOKm),0))*100)<3  THEN 0 ELSE
		(CASE WHEN (((SUM(conso.CONSOVOLPLNFIN)-isnull(PremPln.volPln,0))+isnull(SUM(conso0.CONSOVOLPLNFIN),0)-isnull(conso0.volPln,0))/(SUM(conso.CONSOKm)+isnull(SUM(conso0.CONSOKm),0))*100)>160  THEN 0 ELSE
		(((SUM(conso.CONSOVOLPLNFIN)- (CASE WHEN PremPln.periode=conso.periode THEN isnull(PremPln.volPln,0) ELSE 0 END))
		+isnull(SUM(conso0.CONSOVOLPLNFIN),0)-isnull(conso0.volPln,0))/(SUM(conso.CONSOKm)+isnull(SUM(conso0.CONSOKm),0))*100)
		END)
		END)
		END))AS DECIMAL(8,2)) 
	AS CONSO0,
 
	CAST((
		(CASE WHEN (SUM(conso.CONSOKm)+isnull(SUM(conso0.CONSOKm),0))=0 THEN 0 ELSE
		(CASE WHEN ((SUM(conso.CONSOVOLPLNFIN)+isnull(SUM(conso0.CONSOVOLPLNFIN),0))/(SUM(conso.CONSOKm)+isnull(SUM(conso0.CONSOKm),0))*100)<3  THEN 0 ELSE
		(CASE WHEN ((SUM(conso.CONSOVOLPLNFIN)+isnull(SUM(conso0.CONSOVOLPLNFIN),0))/(SUM(conso.CONSOKm)+isnull(SUM(conso0.CONSOKm),0))*100)>160  THEN 0 ELSE
		SUM(conso.CONSOKm)+isnull(SUM(conso0.CONSOKm),0)END)END)END))AS DECIMAL(8,2)) 
	AS km0,
 
	CAST((
		(CASE WHEN (SUM(conso.CONSOKm)+isnull(SUM(conso0.CONSOKm),0))=0 THEN 0 ELSE
		(CASE WHEN (((SUM(conso.CONSOVOLPLNFIN)-isnull(PremPln.volPln,0))+isnull(SUM(conso0.CONSOVOLPLNFIN),0)-isnull(conso0.volPln,0))/(SUM(conso.CONSOKm)+isnull(SUM(conso0.CONSOKm),0))*100)<3  THEN 0 ELSE
		(CASE WHEN (((SUM(conso.CONSOVOLPLNFIN)-isnull(PremPln.volPln,0))+isnull(SUM(conso0.CONSOVOLPLNFIN),0)-isnull(conso0.volPln,0))/(SUM(conso.CONSOKm)+isnull(SUM(conso0.CONSOKm),0))*100)>160  THEN 0 ELSE
		(SUM(conso.CONSOVOLPLNFIN)- (CASE WHEN PremPln.periode=conso.periode THEN isnull(PremPln.volPln,0) ELSE 0 END))
		+isnull(SUM(conso0.CONSOVOLPLNFIN),0)-isnull(conso0.volPln,0)END)END)END))AS DECIMAL(8,2)) 
	AS GASOIL0,
 
	logv.analytique,
	logv.MARQUE, 
	logv.REF_MARQUE,
	LOGV.DATE_MES,
	LOGV.STATUT,
	SUM(conso.Qtereserv1)+SUM(conso.Qtereserv2) AS Reservoir,
	SUM(conso.kmAnt) AS kmAnt,
	SUM(conso.KmPost) AS kmPost,
	SUM(conso.kmestimefin) AS kmestimefin,
	SUM(CASE WHEN (conso.kmestimeDeb<(((conso.Qtereserv1)+(conso.Qtereserv2))/25*100) AND ((conso.Qtereserv1)+(conso.Qtereserv2))<>0) THEN conso.kmestimedeb ELSE 0 END) AS kmestimedeb,
	SUM(conso.kmcertain) AS kmcertain,
	conso.KMincoherent,
	CASE WHEN conso.KMincoherent='O' THEN 0 ELSE ((SUM(conso.KmPost)-SUM(conso.kmestimefin))) END AS Stk_Fdm_km_old,
	SUM(isnull(conso.qtegasoil,0)) AS stk_fdm_km,
	SUM(isnull(conso0.qtegasoil1,0)) AS stk_ddm_km
 
	FROM vehicule_Km_conso_Gasoil AS conso 
	LEFT OUTER JOIN (
 
		SELECT DISTINCT vkcg.vehicule, vkcg.periode, CASE WHEN vkcg.CONSOVOLPLNFIN IS NULL THEN 0 ELSE vkcg.CONSOVOLPLNFIN END AS CONSOVOLPLNFIN,
		CASE WHEN vkcg.CONSOKm IS NULL THEN 0 ELSE vkcg.CONSOKm END AS CONSOKm, isnull(vkcg.qtegasoil,0) AS qtegasoil1,  isnull(PremPln.volPln,0) AS volPln, 
		PremPln.periode AS pervolpln
		FROM vehicule_Km_conso_Gasoil AS vkcg
		LEFT OUTER JOIN
 
			(SELECT minu.vehicule, minu.dateD, SUM(VKCGD.volPln) AS volPln, VKCGD.periode
			FROM
 
				(SELECT vehicule,  MIN(datepln) AS dateD   
				FROM vehicule_Km_conso_Gasoil_detail
				WHERE vehicule LIKE '%%'  
				GROUP by vehicule) AS minu
 
			LEFT OUTER JOIN vehicule_Km_conso_Gasoil_detail AS VKCGD
			ON (MINU.VEHICULE=VKCGD.VEHICULE AND MINU.dateD=VKCGD.DatePln)
			WHERE minu.dateD>'01/01/2011'
			GROUP by minu.vehicule, minu.dateD, VKCGD.periode) AS PremPln
 
		ON (PremPln.VEHICULE=vkcg.vehicule AND PremPln.dateD=vkcg.datedebut)) AS conso0
 
	ON ((conso.periode+(CASE WHEN MONTH(conso.dateFin)=1 THEN-100 ELSE -1 END))=conso0.periode AND conso.vehicule=conso0.vehicule)
	LEFT OUTER JOIN
		(SELECT minu.vehicule, minu.dateD, SUM(VKCGD.volPln) AS volPln, VKCGD.periode
		FROM
 
			(SELECT vehicule,  MIN(datepln) AS dateD   
			FROM vehicule_Km_conso_Gasoil_detail
			WHERE vehicule LIKE '%%'  
			GROUP by vehicule) AS minu
 
		LEFT OUTER JOIN vehicule_Km_conso_Gasoil_detail AS VKCGD
		ON (MINU.VEHICULE=VKCGD.VEHICULE AND MINU.dateD=VKCGD.DatePln)
		WHERE minu.dateD>'01/01/2011'
		GROUP by minu.vehicule, minu.dateD, VKCGD.periode) AS PremPln
 
	ON (PremPln.VEHICULE=conso.vehicule AND PremPln.dateD=conso.datedebut)
	LEFT OUTER JOIN LOGIPARC_Vehicule AS LOGV ON (LOGV.Vehicule =CONSO.Vehicule)
 
	WHERE  conso.periode>='201101'
	GROUP by conso.periode, conso.TYPE,conso.vehicule, conso.datedebut, conso.dateFin,logv.analytique,logv.MARQUE, logv.REF_MARQUE,conso.KMincoherent, LOGV.DATE_MES, PremPln.volPln, conso0.volPln, LOGV.STATUT, PremPln.periode
	) AS CONSO 
 
LEFT OUTER JOIN LOGIPARC_historique_affectation_journalier AS aff
ON (aff.Vehicule=CONSO.Vehicule AND AFF.periode=conso.periode AND AFF.jour=conso.DateFin )
LEFT OUTER JOIN
 vehicule_Km_conso_Gasoil_detail AS det
ON (det.Vehicule=CONSO.Vehicule AND conso.DateFin=det.datePln)
LEFT OUTER JOIN 
	(SELECT 
	conso.vehicule, 
	SUM(conso.CONSOKm) AS km,
	SUM(conso.CONSOVOLPLNFIN) AS gasoil,
	CAST((
		(CASE WHEN SUM(conso.CONSOKm)=0 THEN 0 ELSE
			(CASE WHEN (SUM(conso.CONSOVOLPLNFIN)/SUM(conso.CONSOKm)*100)<3  THEN 0 ELSE
				(CASE WHEN (SUM(conso.CONSOVOLPLNFIN)/SUM(conso.CONSOKm)*100)>160  THEN 0 ELSE
					(SUM(conso.CONSOVOLPLNFIN)/SUM(conso.CONSOKm)*100)
					END)
				END)
			END)
	)AS DECIMAL(8,2)) AS CONSO
 
	FROM vehicule_Km_conso_Gasoil AS conso 
	WHERE conso.periode>='200801'
	AND ((conso.CONSOVOLPLNFIN>0) 
	AND	(conso.CONSOKm <>0) 
	AND ((conso.CONSOVOLPLNFIN)/(conso.CONSOKm)*100)>3  
	AND((conso.CONSOVOLPLNFIN)/(conso.CONSOKm)*100)<160)
	GROUP by conso.vehicule ) AS log
	ON (LOG.vehicule=conso.vehicule)
 
LEFT OUTER JOIN 
	(SELECT 
	logv.MARQUE, 
	logv.REF_MARQUE,
	SUM(conso.CONSOKm) AS km,
	SUM(conso.CONSOVOLPLNFIN) AS gasoil,
	CAST((
		(CASE WHEN SUM(conso.CONSOKm)=0 THEN 0 ELSE
			(CASE WHEN (SUM(conso.CONSOVOLPLNFIN)/SUM(conso.CONSOKm)*100)<3  THEN 0 ELSE
				(CASE WHEN (SUM(conso.CONSOVOLPLNFIN)/SUM(conso.CONSOKm)*100)>160  THEN 0 ELSE
				(SUM(conso.CONSOVOLPLNFIN)/SUM(conso.CONSOKm)*100)
				END)
			END)
		END)
	)AS DECIMAL(8,2)) AS CONSO
 
	FROM vehicule_Km_conso_Gasoil AS conso LEFT OUTER JOIN LOGIPARC_Vehicule AS LOGV
	ON (LOGV.Vehicule =CONSO.Vehicule)
	WHERE conso.periode>='200801'
	AND ((conso.CONSOVOLPLNFIN>0) 
	AND (conso.CONSOKm <>0) 
	AND ((conso.CONSOVOLPLNFIN)/(conso.CONSOKm)*100)>3  
	AND ((conso.CONSOVOLPLNFIN)/(conso.CONSOKm)*100)<160)
	GROUP by logv.MARQUE, logv.REF_MARQUE ) 
	AS logmo
ON (logmo.MARQUE=conso.MARQUE AND logmo.REF_MARQUE=conso.REF_MARQUE )
 
LEFT OUTER JOIN timedisc_vehicule_kms_periode AS tvkp
ON (tvkp.Vehicule=conso.Vehicule AND ((YEAR(tvkp.periode)*100+MONTH(tvkp.periode))=conso.periode))
 
WHERE conso.TYPE <>'xxxCTRR' AND conso.vehicule   LIKE '%%' AND conso.periode>='201301' 
ORDER BY conso.TYPE,conso.MARQUE, conso.REF_MARQUE, conso.vehicule, conso.PERIODE

Ce que j'ai présenté plus haut, ce n'est qu'un petit bout de script, que j'ai géré moi même.

Problème de script SQL : left join et sélection MAX(date)

Langage SQL

Discussions similaires

Partager

Partager