Discussion :

[cavo789]

Bonjour

Je suis face à un problème que je viens de résoudre via l'utilisation d'un curseur. J'ai d'abord préféré l'éviter mais j'avoue ne pas savoir si cela est possible (via une CTE?).

Voici une situation : j'ai un centre de coût sur lequel je dois fournir l'évolution des dépenses, mois après mois. A priori, c'est simple. Il suffit de prendre le mois de la dépense et de sommer le montant.

Mois N° Dépense Montant
01 11111 10€
02 12121 10€
02 22222 85€
05 22222 60€

Soit 10€ pour Janvier, 95€ pour Février et 60€ pour Mai.

Maintenant, ça se complique : certaines dépenses sont faites dans une devise comme p.e. le dollar. Donc, au moment de prévoir la dépense, il est tenu compte du cours du change à ce moment-là. Et, lors du paiement, p.e. deux mois après, le montant de la dépense est réajusté.

L'exemple devient

Mois N° Dépense Montant Code
01 11111 10€ 01 (<-- dépense en EUR)
02 12121 10€ 01
02 22222 85€ 75 (<-- dépense en USD)
05 22222 60€ 76 (<-- régularisation code 75)

Le record pour le mois de Mai vient donc régulariser le montant de 85€ du mois de Février et l'adapte pour une dépense de 60€.


Ce qui fait que, lors de mon rapport évolutif mensuel, lorsque j'établis mon rapport au mois de Mars, la dépense pour Février est bien de 95€ (étant donné que la régularisation de Mai n'est pas encore introduite dans la DB). En Avril, toujours 95€ pour Février.

En Mai, le montant de 60€ vient régulariser la dépense prévue de 85€. A ce moment, le montant de la dépense pour Février tombe à 10€ tandis que la dépense (réellement payée) pour Mai est de 60€.


Ma solution : j'ai fais un curseur avec un Group By sur le numéro de la dépense et un MAX sur le Mois. Je traite les lignes une à une et si je trouve un code 76, cela signifie obligatoirement que j'ai un code 75 pour un mois précédent celui trouvé. A ce moment, je fais un UPDATE pour montre le montant à 0 pour le record 75.


Voilà, ma solution fonctionne et vu que je n'ai, au plus que quelques centaines de records par CostCenter, la vitesse est tout à fait acceptable.


Comment auriez-vous fait ?

[elsuket]

Bonjour,

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
CREATE TABLE TbDepense
(
	MOIS CHAR(2),
	IDDepense INT,
	Montant INT,
	Code CHAR(2)
)
GO
 
INSERT INTO TbDepense VALUES ('01', 11111, 10, '01')
INSERT INTO TbDepense VALUES ('02', 12121, 10, '01')
INSERT INTO TbDepense VALUES ('02', 22222, 85, '75')
INSERT INTO TbDepense VALUES ('05', 22222, 60, '76')
GO
 
-- Au mois de Février
;WITH
	CTE_DERNIERE_DEPENSE AS
	(
		SELECT IDDepense, MAX(CAST(MOIS AS INT)) Mois
		FROM dbo.TbDepense
		WHERE CAST(MOIS AS INT) <= 2
		GROUP BY IDDepense
	)
SELECT D.IDDepense, SUM(Montant) Montant
FROM dbo.TbDepense D
JOIN CTE_DERNIERE_DEPENSE C
	ON C.IDDepense = D.IDDepense
	AND C.Mois = CAST(D.MOIS AS INT)
GROUP BY D.IDDepense
 
-- Au mois de Mai
;WITH
	CTE_DERNIERE_DEPENSE AS
	(
		SELECT IDDepense, MAX(CAST(MOIS AS INT)) Mois
		FROM dbo.TbDepense
		WHERE CAST(MOIS AS INT) <= 5
		GROUP BY IDDepense
	)
SELECT D.IDDepense, SUM(Montant) Montant
FROM dbo.TbDepense D
JOIN CTE_DERNIERE_DEPENSE C
	ON C.IDDepense = D.IDDepense
	AND C.Mois = CAST(D.MOIS AS INT)
GROUP BY D.IDDepense

Est-ce que c'est ce que vous devez obtenir ?

@++

[cavo789]

Wow! Impressionnant !!! Merci pour le temps que tu m'as consacré, ton code et ton exemple.

Toutefois, je constate que tu ne tiens pas compte du champs code. Si je change le jeu de données comme ceci :

Code :

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

1
2
3
4
INSERT INTO TbDepense VALUES ('01', 11111, 10, '01')
INSERT INTO TbDepense VALUES ('02', 22222, 85, '75')
INSERT INTO TbDepense VALUES ('02', 22222, 05, '75')
INSERT INTO TbDepense VALUES ('05', 22222, 65, '75')

Pour la dépense 22222, j'ai donc un dépense de 85 euros suivi d'une autre dépense de 05 euros et, en Mai, encore une dépense de 65 euros. Le code étant toujours à 75, cela signifie que le montant initialement prévu pour la dépense est chaque fois revu à la hausse.

La dépense pour Février est donc de 85+5 soit 90 euros et une dépense additionnelle en Mai pour 65 euros.

Ensuite, je pourrais avoir

Code :

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

1
2
 
INSERT INTO TbDepense VALUES ('08', 22222, 140, '76')

Ici, le code 76 et donc une régularisation : le montant prévu pour la dépense était de 85+5+65 soit 155 euros et, en Août, on procède au paiement et là, le taux de change fait que la dépense ne coûte plus que 140 euros.

[elsuket]

Merci pour le temps que tu m'as consacré, ton code et ton exemple.

Avec plaisir, j'ai trouvé ton problème intéressant, et j'y ai vu l'occasion d'utiliser des CTE dont je suis un grand adepte

Toutefois, je constate que tu ne tiens pas compte du champs code

Effectivement je n'en ai pas tenu compte parce que je n'en voyais pas l'utilité pour la requête.

J'espère que la requête suivante répond mieux à ta question :

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
SELECT MONTANTS.IDDepense, MONTANTS.MontantTotal
FROM
	(
		SELECT IDDepense, Code, SUM(Montant) MontantTotal
		FROM TbDepense
		WHERE CAST(MOIS AS INT) <= 2
		GROUP BY IDDepense, Code
	) MONTANTS
JOIN
	(
		SELECT IDDepense, MAX(CAST(Code AS INT)) CodeDerRegulation
		FROM TbDepense
		WHERE CAST(MOIS AS INT) <= 2
		GROUP BY IDDepense
	) REGU
ON MONTANTS.IDDepense = REGU.IDDepense
AND MONTANTS.Code = REGU.CodeDerRegulation

ou encore, avec des CTE, pour le mois de mai :

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
WITH
	MONTANTS AS
	(
		SELECT IDDepense, Code, SUM(Montant) MontantTotal
		FROM TbDepense
		WHERE CAST(MOIS AS INT) <= 5
		GROUP BY IDDepense, Code
	),
	REGU AS
	(
		SELECT IDDepense, MAX(CAST(Code AS INT)) CodeDerRegulation
		FROM TbDepense
		WHERE CAST(MOIS AS INT) <= 5
		GROUP BY IDDepense
	)
SELECT MONTANTS.IDDepense, MONTANTS.MontantTotal
FROM MONTANTS
JOIN REGU
	ON MONTANTS.IDDepense = REGU.IDDepense
	AND MONTANTS.Code = REGU.CodeDerRegulation

@++

[cavo789]

Merci!!!

Je devrais pouvoir m'en sortir avec ton code toutefois un truc (et une seul) m'ennuie un peu : sur base de mon exemple, tu pars du postulat que le code de régularisation est forcément supérieur. C'est exact dans l'exemple mais pas dans la réalité :

* Le 76 régularise le 75 (exemple que j'ai donné et excellente réponse que tu as fourni)
* Le 15 entérine le 76 (il s'agit d'une opération nécessaire pour la comptabilité)

Et j'ai d'autres exemples, avec d'autres suites de codes qui annulent les précédents.


Ne perds plus de temps à m'aider; tu en as déjà suffisamment fait

[cavo789]

J'ai déjà tiré profit d'une de tes CTEs (sur ton site) pour simplifier une partie de ma stored proc. Merci