Calcul d’accroissement des montants annuels

Version imprimable

1 pièce(s) jointe(s)

Salut les développeurs!
J'ai une requête qui me donne la somme des montants pour chaque année (en USD), c'est-à-dire elle convertit toutes les devises en dollar américain puis elle calcule la somme pour chaque année, voici les codes SQL de cette requête:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT LEFT(OPERATION.Dat_operation,4) AS Année, SUM(OPERATION.Mont[1] /DEVISE.Taux[1]) AS TOT_USD FROM DEVISE, OPERATION WHERE OPERATION.Cod_devise = DEVISE.Cod_devise AND ( OPERATION.Typ_opération = {Param1} ) GROUP BY LEFT(OPERATION.Dat_operation,4)
Ce qui me donne :

Années Montants
2012 150
2013 200
2014 500

A partir de ces résultats je veux ajouter une 3è colonne pour calculer l'accroissement en % des montants avec la formule :
Acc = (Montant de l'année après - Montant de l'année avant) - Montant de l'année avant * 100

Ex : (200 - 150) / 150 * 100 = 33,33 %

Merci d'avance pour votre aide!

10/04/2014, 15h29
tatayo
Bonjour,
Le faire en SQL est possible, mais c'est chaud car il faut pour chaque ligne connaitre le montant de la ligne précédente.
Donc il faudrait faire une vue avec cette requête, puis faire une auto-jointure de la vue.
Si la vue s'appelle VueResultat, pour comparer une année avec la précédente:
Code:

1 2 3 4 select v1.annee, v1.montants, (le calcul qui va bien) from VueResultat v1 left outer join VueResultat v2 on v1.année = v2.année - 1
La jointure externe est nécessaire pour la première année, donc il faut prévoir le cas dans le calcul.

On pourrait passer par une CTE, mais je doute qu'HF le permette.

Tatayo.
10/04/2014, 15h47
djenny

Merci Tatayo pour ta réponse.

J'avoue que quand tu me parles de faire une vue avec cette requête, puis faire une auto-jointure de cette vue en tout cas je ne comprend rien.

Comment crée-t-on une vue?
Si je comprend bien avec les codes SQL tout simplement ce n'est pas possible?

Citation:

Envoyé par djenny

J'avoue que quand tu me parles de faire une vue avec cette requête, puis faire une auto-jointure de cette vue en tout cas je ne comprend rien.

Comment crée-t-on une vue?
Si je comprend bien avec les codes SQL tout simplement ce n'est pas possible?

Je viens de voir qu'on ne peux pas le faire avec WinDev.
En SQL pur c'est possible, mais il faut une jointure de 2 sous-requêtes, ce qui devient un peu lourd:
Code:

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 select t1.année,t1.montant,t2.montant from ( SELECT LEFT(OPERATION.Dat_operation,4) AS Année, SUM(OPERATION.Mont[1] /DEVISE.Taux[1]) AS TOT_USD FROM DEVISE, OPERATION WHERE OPERATION.Cod_devise = DEVISE.Cod_devise AND ( OPERATION.Typ_opération = {Param1} ) GROUP BY LEFT(OPERATION.Dat_operation,4) ) as t1 left outer join ( SELECT LEFT(OPERATION.Dat_operation,4) AS Année, SUM(OPERATION.Mont[1] /DEVISE.Taux[1]) AS TOT_USD FROM DEVISE, OPERATION WHERE OPERATION.Cod_devise = DEVISE.Cod_devise AND ( OPERATION.Typ_opération = {Param1} ) GROUP BY LEFT(OPERATION.Dat_operation,4) ) as t2 on t1.année = t2.année - 1
Je pense que le plus simple est de le faire en programmation.

Tatayo.

10/04/2014, 17h52
djenny

Merci Tatayo!

J'ai un petit message d'erreur(Warning) quand je teste cette requête:

"Rubrique montant inconnue dans le fichier t1. Vérifiez que la requête est en phase avec la description des fichiers. REQ_Requête1, Code SQL, ligne 2, colonne 22"

Merci.
10/04/2014, 17h55
tatayo

OTAN pour moi, il faut utiliser TOT_USD au lieu de montant (ou changer l'alias dans les sous-requêtes).

Tatayo.

Salut Tatayo!

J'ai bien observé ce que tu m'avais demandé de faire et j'ai pu ajouter la formule de l'accroissement :
Code:

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 SELECT t1.Année AS Année, t1.TOT_USD1 AS TOT, ((t2.TOT_USD2 - t1.TOT_USD1) - t1.TOT_USD1) / 100 AS Acc FROM ( SELECT LEFT(OPERATION.Dat_operation,4) AS Année, SUM(OPERATION.Mont[1] /DEVISE.Taux[1]) AS TOT_USD1 FROM DEVISE, OPERATION WHERE OPERATION.Cod_devise = DEVISE.Cod_devise AND ( OPERATION.Typ_opération = {Param1} ) GROUP BY LEFT(OPERATION.Dat_operation,4) ) AS t1 LEFT OUTER JOIN ( SELECT LEFT(OPERATION.Dat_operation,4) AS Année, SUM(OPERATION.Mont[1] /DEVISE.Taux[1]) AS TOT_USD2 FROM DEVISE, OPERATION WHERE OPERATION.Cod_devise = DEVISE.Cod_devise AND ( OPERATION.Typ_opération = {Param1} ) GROUP BY LEFT(OPERATION.Dat_operation,4) ) AS t2 ON t1.Année = t2.Année - 1
Mais quand je teste ma requete j'ai toujours ce message d'info : "Aucun n'enregistrement ne correspond à votre recherche" alors qu'il y a des données qui correspondent bien à cette recherche.

Merci d'avance à Tatayo et à tous ceux qui vont m'aider à palier ce problème!

12/04/2014, 13h30
tatayo

Bonjour,
Je dirai qu'il faut commencer par tester chaque requête séparément, pour voir si elles envoient bien des lignes.
Ensuite je vois un problème potentiel: LEFT(OPERATION.Dat_operation,4)
Left renvoie une chaine de caractères, donc l'année est considérée ici comme une chaine.
Et là t1.Année = t2.Année - 1 risque de ne pas fonctionner.
C'est peut-être pour celà que la requête ne renvoie rien.
Il faut soit convertir les deux années en entier, soit traiter les dates comme telle et utiliser les fonctions qui vont bien pour extraire l'année.

Tatayo.

1 pièce(s) jointe(s)

Merci Tatoyo!

J'ai pu faire cette conversion avec la fonction VAL avec le prefixe WL. Et les codes deviennent :
Code:

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 SELECT t1.Année AS Année, t1.TOT_USD1 AS TOT, ((t2.TOT_USD2 - t1.TOT_USD1) / t1.TOT_USD1) * 100 AS Acc FROM ( SELECT LEFT(OPERATION.Dat_operation,4) AS Année, SUM(OPERATION.Mont[1] /DEVISE.Taux[1]) AS TOT_USD1 FROM DEVISE, OPERATION WHERE OPERATION.Cod_devise = DEVISE.Cod_devise AND ( OPERATION.Typ_opération = {Param1} ) GROUP BY LEFT(OPERATION.Dat_operation,4) ) AS t1 LEFT OUTER JOIN ( SELECT LEFT(OPERATION.Dat_operation,4) AS Année, SUM(OPERATION.Mont[1] /DEVISE.Taux[1]) AS TOT_USD2 FROM DEVISE, OPERATION WHERE OPERATION.Cod_devise = DEVISE.Cod_devise AND ( OPERATION.Typ_opération = {Param1} ) GROUP BY LEFT(OPERATION.Dat_operation,4) ) AS t2 ON t1.Année = WL.Val(t2.Année) - 1
Seulement il se pose un problème d'ordre dans la rubrique Acc, normalement dans le premier enregistrement on doit avoir une valeur nulle parce qu'il n'a pas de précédent mais ce qui se fait c'est plutôt le contraire, la valeur nulle c'est à la dernière année!

Je sais pas si je me suis fait comprendre, pour plus de détail j'attache l'image de la requête crée!

Merci d'avance!

12/04/2014, 17h01
tatayo

Le classement d'un NULL dépend en fait du bon vouloir du SGBD: certains le considère comme inférieur à toute autre valeur, d'autre l'inverse, et certains le mettent toujours en pemier (ou en dernier).
Bref le plus efficace est d'ajouter une colonne calculée pour le rang, et de trier sur cette colonne.

Tatayo.

Essaye ça...

Code:

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
SELECT
	t1.Année AS Année,
	t1.TOT_USD1 AS TOT,
	((t1.TOT_USD2 - t2.TOT_USD1) / t2.TOT_USD1) * 100 AS Acc
FROM 
	(
		SELECT 
			LEFT(OPERATION.Dat_operation,4) AS Année,	
			SUM(OPERATION.Mont[1] /DEVISE.Taux[1]) AS TOT_USD1
		FROM 
			DEVISE,	
			OPERATION
		WHERE 
			OPERATION.Cod_devise	=	DEVISE.Cod_devise
		AND
			(
				OPERATION.Typ_opération = {Param1}
			)
		GROUP BY LEFT(OPERATION.Dat_operation,4)
	) AS t1
	LEFT OUTER JOIN
	(
		SELECT 
			LEFT(OPERATION.Dat_operation,4) AS Année,	
			SUM(OPERATION.Mont[1] /DEVISE.Taux[1]) AS TOT_USD2
		FROM 
			DEVISE,	
			OPERATION
		WHERE 
			OPERATION.Cod_devise	=	DEVISE.Cod_devise
		AND
			(
				OPERATION.Typ_opération = {Param1}
			)
		GROUP BY LEFT(OPERATION.Dat_operation,4)
	) AS t2 ON t1.Année = WL.Val(t2.Année) + 1

Pas modifié grand chose :

- La condition de la jointure

Code:

ON t1.Année = WL.Val(t2.Année) + 1

Au lieu de faire la jointure sur le précédent on le fait sur le suivant (+1 au lieu de -1)

- et la formule de calcul où t1 devient t2 et vice versa

Code:

((t1.TOT_USD2 - t2.TOT_USD1) / t2.TOT_USD1) * 100 AS Acc

Je n'ai pas testé, dis nous si c'est bon...

Bonjour,

pour moi il faut juste tester les valeurs :

Code:

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
 
SELECT 
t1.Année AS Année,
t1.TOT_USD1 AS TOT,
((ISNULL(t2.TOT_USD2,0) - ISNULL(t1.TOT_USD1,0)) / ISNULL(t1.TOT_USD1,1)) * 100 AS Acc
FROM 
	(
	SELECT 
	LEFT(OPERATION.Dat_operation,4) AS Année,	
	SUM(OPERATION.Mont[1] /DEVISE.Taux[1]) AS TOT_USD1
	FROM 
	DEVISE,	
	OPERATION
	WHERE 
	OPERATION.Cod_devise	=	DEVISE.Cod_devise
	AND
	(
	OPERATION.Typ_opération = {Param1}
	)
	GROUP BY 
	LEFT(OPERATION.Dat_operation,4)
	) AS t1
LEFT OUTER JOIN
		(
		SELECT 
		LEFT(OPERATION.Dat_operation,4) AS Année,	
		SUM(OPERATION.Mont[1] /DEVISE.Taux[1]) AS TOT_USD2
		FROM 
		DEVISE,	
		OPERATION
		WHERE 
		OPERATION.Cod_devise	=	DEVISE.Cod_devise
		AND
		(
		OPERATION.Typ_opération = {Param1}
		)
GROUP BY 
LEFT(OPERATION.Dat_operation,4)
) AS t2 ON t1.Année = WL.Val(t2.Année) - 1

Merci Laurent, Tatayo et Nicolas_Jeanneau pour votre disponibilité et votre aide!

La réponse de Laurent est seule que j'avais pensée aussi et que j'avais déjà appliquée et ça marche très bien. D'ailleurs Je me connectais justement pour résoudre cette discussion tout en vous montrant ma résolution. Seulement Laurent en inversant les rubriques a commis une petite erreur dans la 4è ligne seule que j'ai mise en rouge. Voici mes codes:
Code:

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 SELECT t1.Année AS Année, t1.TOT_USD1 AS TOT, t2.TOT_USD2 AS TOT, ((t1.TOT_USD1 - t2.TOT_USD2) / t2.TOT_USD2) * 100 AS Acc FROM ( SELECT LEFT(OPERATION.Dat_operation,4) AS Année, SUM(OPERATION.Mont[1] /DEVISE.Taux[1]) AS TOT_USD1 FROM DEVISE, OPERATION WHERE OPERATION.Cod_devise = DEVISE.Cod_devise AND ( OPERATION.Typ_opération = {Param1} ) GROUP BY LEFT(OPERATION.Dat_operation,4) ) AS t1 LEFT OUTER JOIN ( SELECT LEFT(OPERATION.Dat_operation,4) AS Année, SUM(OPERATION.Mont[1] /DEVISE.Taux[1]) AS TOT_USD2 FROM DEVISE, OPERATION WHERE OPERATION.Cod_devise = DEVISE.Cod_devise AND ( OPERATION.Typ_opération = {Param1} ) GROUP BY LEFT(OPERATION.Dat_operation,4) ) AS t2 ON t1.Année = WL.Val(t2.Année) + 1
Merci Nicolas_Jeanneau pour ton apport très important sur le teste des valeurs avec ISNULL.

Bon développement à tous!