Discussion :

[Sixool]

Bonjour à tous,

Je débute en SQL et je suis amené à en faire de manière très occasionnelle.
Aujourd'hui je doit réaliser une requete permettant de faire un cumul progressif.

Voici ma table mon resultat de requete :

MOIS OUVERTS CLOS STOCK
--------------------------------------------------------
1 10 8 2
2 5 6 1
3 3 9 -5
etc.

Pourriez vous m'aiguiller vers une fonction qui me permettrait d'obtenir la colonne STOCK svp ???

[elsuket]

Bonjour,

Tout simplement :

Code :

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

1
2
SELECT STOCK
FROM dbo.maTable

@++

[Sixool]

J'aurai bien voulu que la solution soit si simple mais ma colonne STOCK doit être calculée à partir des colonnes OUVERTS et CLOS

[pradiergael]

l'utilisation d'une fonction n'est pas forçément appropriée .
quelle est la regle de calcul du stock ?

sur la 1ere ligne , on pense stock = ouvert - clos
sur la deuxieme , c'est le contraire :stock = clos - ouvert
sur la troisime, rien ne marche...

[Sixool]

la regle de calcul du stock est la suivante

sur la 1ere ligne , stock = ouvert - clos
sur la deuxieme , stock = ouvert - clos + (stock 1ère ligne)
sur la troisime, stock = ouvert - clos + (stock 2ème ligne)

[Kropernic]

Code :

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

select sum(ouvert-clos) from dbo.matable

[Sixool]

Le SUM me renvoie un message d'erreur :
"Impossible d'exécuter une fonction d'agrégation sur une expression comportant un agrégat ou une sous-requête."

Car les ouverts et clos sont obtenus à partir de COUNT

[Kropernic]

Ah... Ca change pas mal de choses...

Il serait p-e utile de poster la structure de votre table.

[Sixool]

Voici la requête me permettant d'obtenir le delta mois par mois :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
     MONTH(PLANNINGS.D_JOUR) AS MOIS,
     COUNT(DISTINCT CASE WHEN OUVERTURES.C_NATURE = '1' AND OUVERTURES.I_APP_ENVIRONN = '2' AND OUVERTURES.C_EQUIPE <> '3' THEN OUVERTURES.IDT_APPEL ELSE NULL END) - COUNT(DISTINCT CASE WHEN CLOTURES.C_NATURE = '1' AND CLOTURES.I_APP_ENVIRONN = '2' AND CLOTURES.C_EQUIPE <> '3' THEN CLOTURES.IDT_APPEL ELSE NULL END) AS DELTA
FROM
     APPEL AS OUVERTURES RIGHT OUTER JOIN
     PLANNINGS ON CONVERT(Char(10), OUVERTURES.D_APPEL, 103) = CONVERT(Char(10), PLANNINGS.D_JOUR, 103) LEFT OUTER JOIN
     APPEL AS CLOTURES ON CONVERT(Char(10), PLANNINGS.D_JOUR, 103) = CONVERT(Char(10), CLOTURES.D_CLOTTECH, 103)
WHERE
     (YEAR(PLANNINGS.D_JOUR) = YEAR(CURRENT_TIMESTAMP))
GROUP BY
     MONTH(PLANNINGS.D_JOUR)
ORDER BY
     MOIS

Voici le résultat de la requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
1	16
2	-101
3	-2
4	-9
5	-4
6	94
7	89
8	-69
9	-109
10	-6
11	-24
12	-10

Le résultat attendu est le suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
1	16
2	-85 (16-101)
3	-87 (16-101-2)
4	-96 (16-101-2-9)
5	-100 (16-101-2-9-4)
6	-6 (16-101-2-9-4+94)
7	83 (16-101-2-9-4+94+89)
8	14 (16-101-2-9-4+94+89-69)
9	-95 (16-101-2-9-4+94+89-69-109)
10	-101 (16-101-2-9-4+94+89-69-109-6)
11	-125 (16-101-2-9-4+94+89-69-109-6-24)
12	-135 (16-101-2-9-4+94+89-69-109-6-24-10)

[Kropernic]

Ok et donc si j'ai bien compris, il vous faut maintenant la somme des stocks de chaque mois.

Ce n'est surement pas la méthode la plus propre/performante (je laisse le soin à elsuket et sqlpro (ou autres, je ne voudrais vexer personne^^) de vous la donner/proposer ^^) mais si vous êtes dans l'urgence du résultat, une manière rapide de l'obtenir est de travailler avec une CTE (Common Table Expression).

Code :

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

1
2
3
4
5
With T1 (mois, stock) as (
--placez ici votre requête
)
 
select SUM(stock) from T1

edit : D'après votre édition, il semblerait que ce ne soit pas ce que vous recherchez (ma requête vous donne le résultat de la dernière ligne)... Je cogite ^^

[Sixool]

D'après votre édition, il semblerait que ce ne soit pas ce que vous recherchez (ma requête vous donne le résultat de la dernière ligne)... Je cogite ^^

Merci beaucoup de l'intérêt que vous portez à ma recherche !

[Kropernic]

Voilà, j'ai planché un peu sur ce problème (j'aime bien les énigmes^^) et je suis arrivé à ceci :

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
create table #tmp(
	mois int,
	stock int)

insert into #tmp (mois,stock) values (1,16)
insert into #tmp (mois,stock) values (2,-101)
insert into #tmp (mois,stock) values (3,-2)
insert into #tmp (mois,stock) values (4,-9)
insert into #tmp (mois,stock) values (5,-4)
insert into #tmp (mois,stock) values (6,94)
insert into #tmp (mois,stock) values (7,89)
insert into #tmp (mois,stock) values (8,-69)
insert into #tmp (mois,stock) values (9,-109)
insert into #tmp (mois,stock) values (10,-6)
insert into #tmp (mois,stock) values (11,-24)
insert into #tmp (mois,stock) values (12,-10)

select 
	a.mois,
	(select sum(b.stock) from #tmp b where b.mois <= a.mois)
from 
	#tmp a


drop table #tmp

Ce script crée donc une table temporaire contenant les valeurs retournées par votre requête. Je fais ensuite un select sur cette table.

Donc, si je ne suis pas dans l'erreur, vous pouvez utiliser une CTE à la place de ma table temporaire comme dans mon exemple précédent.

Vous n'avez plus qu'à faire la requête indiquée en grand en remplaçant #tmp par le nom de votre CTE.

[Sixool]

Je viens d'essayer la requete, en revanche elle tombe en timeout si je la lance à partir du concepteur de requete.

(Pour info, je travaille sous SSRS)

[Kropernic]

Etrange ce timeout...

Votre requête de base (celle qui fourni le delta par mois) s'exécute sans problème ? Dans quel délai ?

Donnez également le code exact de la requête qui vous donne le timeout (on ne sait jamais^^)

[Sixool]

La requete de base est assez rapide (env 2sec.)

Je lance la requete dans le gestionnaire de rapport SSRS (en Web), je mesure le temps de réponse.

Résultat : 1min40s

Mais les résultats sont là !!

Merci bcp

[Kropernic]

Si votre requête qui fournit le delta ne prend que 2 secondes, l'ensemble ne devrait pas fournir de timeout.

Que faites-vous exactement ?

Edit :

Pour être complet, vous devriez avoir quelque chose dans ce goût là.

Code :

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

1
2
3
4
5
6
7
8
9
10
 
with T1(mois, stock) as (
--la requête qui fourni le delta
)
 
select 
	a.mois,
	(select sum(b.stock) from T1 b where b.mois <= a.mois)
from 
	T1 a

Cela ne devrait donc vraiment pas prendre longtemps.

[Sixool]

Vu que mon graphe SSRS doit renvoyer également les OUVERTS et les CLOS j'ai fait un UNION, voici la 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
WITH T1(MOIS, STOCKS) AS
	(SELECT
		MONTH(PLANNINGS.D_JOUR) AS MOIS,
		COUNT(DISTINCT CASE WHEN OUVERTURES.C_NATURE = 'INC' AND OUVERTURES.C_EQUIPE <> 'HDBANQUE' THEN OUVERTURES.IDT_APPEL ELSE NULL END) - COUNT(DISTINCT CASE WHEN CLOTURES.C_NATURE = 'INC' AND CLOTURES.I_APP_ENVIRONN = 'Production' AND CLOTURES.C_EQUIPE <> 'HDBANQUE' THEN CLOTURES.IDT_APPEL ELSE NULL END) AS DELTA
	FROM
		APPEL AS OUVERTURES RIGHT OUTER JOIN
		PLANNINGS ON CONVERT(Char(10), OUVERTURES.D_APPEL, 103) = CONVERT(Char(10), PLANNINGS.D_JOUR, 103) LEFT OUTER JOIN
		APPEL AS CLOTURES ON CONVERT(Char(10), PLANNINGS.D_JOUR, 103) = CONVERT(Char(10), CLOTURES.D_CLOTTECH, 103)
	WHERE
		(YEAR(PLANNINGS.D_JOUR) = YEAR(CURRENT_TIMESTAMP))
	GROUP BY MONTH(PLANNINGS.D_JOUR))
 
SELECT
	MOIS, 0 AS OUVERTS, 0 AS CLOS,(SELECT SUM(STOCKS) AS STOCKS FROM T1 AS b WHERE (MOIS <= a.MOIS)) AS STOCK
FROM
	T1 AS a
GROUP BY MOIS
 
UNION
 
SELECT
	MONTH(PLANNINGS_1.D_JOUR) AS MOIS,
	COUNT(DISTINCT CASE WHEN OUVERTURES.C_NATURE = 'INC' AND OUVERTURES.I_APP_ENVIRONN = 'Production' AND OUVERTURES.C_EQUIPE <> 'HDBANQUE' THEN OUVERTURES.IDT_APPEL ELSE NULL END) AS OUVERTS,
	COUNT(DISTINCT CASE WHEN CLOTURES.C_NATURE = 'INC' AND CLOTURES.I_APP_ENVIRONN = 'Production' AND CLOTURES.C_EQUIPE <> 'HDBANQUE' THEN CLOTURES.IDT_APPEL ELSE NULL END) AS CLOS,
	0 AS STOCK
FROM
	APPEL AS OUVERTURES RIGHT OUTER JOIN
	PLANNINGS AS PLANNINGS_1 ON CONVERT(char(10), OUVERTURES.D_APPEL, 103) = CONVERT(char(10), PLANNINGS_1.D_JOUR, 103) LEFT OUTER JOIN
	APPEL AS CLOTURES ON CONVERT(char(10), PLANNINGS_1.D_JOUR, 103) = CONVERT(char(10), CLOTURES.D_CLOTTECH, 103)
WHERE
	YEAR(PLANNINGS_1.D_JOUR) = YEAR(CURRENT_TIMESTAMP)
GROUP BY
	MONTH(PLANNINGS_1.D_JOUR)

En revanche, je ne parviens pas à faire de GROUP BY sur mon UNION, il me renvoie une erreur.

[Sixool]

J'ai solutionné mon pb de la manière suivante :

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
WITH T1(MOIS, STOCKS) AS 
	(SELECT
		MONTH(PLANNINGS.D_JOUR) AS MOIS,
		COUNT(DISTINCT CASE WHEN OUVERTURES.C_NATURE = 'INC' AND OUVERTURES.C_EQUIPE <> 'HDBANQUE' THEN OUVERTURES.IDT_APPEL ELSE NULL END) - COUNT(DISTINCT CASE WHEN CLOTURES.C_NATURE = 'INC' AND CLOTURES.I_APP_ENVIRONN = 'Production' AND CLOTURES.C_EQUIPE <> 'HDBANQUE' THEN CLOTURES.IDT_APPEL ELSE NULL END) AS DELTA
	FROM
		APPEL AS OUVERTURES RIGHT OUTER JOIN 
		PLANNINGS ON CONVERT(Char(10), OUVERTURES.D_APPEL, 103) = CONVERT(Char(10), PLANNINGS.D_JOUR, 103) LEFT OUTER JOIN APPEL AS CLOTURES ON CONVERT(Char(10), PLANNINGS.D_JOUR, 103) = CONVERT(Char(10), CLOTURES.D_CLOTTECH, 103)
	WHERE
		YEAR(PLANNINGS.D_JOUR) = YEAR(CURRENT_TIMESTAMP)
	GROUP BY
		MONTH(PLANNINGS.D_JOUR)
	)
 
SELECT
	T2.MOIS,
	SUM(T3.OUVERTS) AS OUVERTS,
	SUM(T3.CLOS) AS CLOS,
	SUM(T2.STOCK) AS STOCK
FROM
	(SELECT
		MOIS,
		0 AS OUVERTS,
		0 AS CLOS,
		(SELECT
			SUM(STOCKS) AS STOCKS
		FROM
			T1 AS b
		WHERE
			MOIS <= a.MOIS
		) AS STOCK
	FROM T1 AS a
	GROUP BY MOIS
	) AS T2 INNER JOIN
 
	(SELECT
		MONTH(PLANNINGS_1.D_JOUR) AS MOIS,
		COUNT(DISTINCT CASE WHEN OUVERTURES.C_NATURE = 'INC' AND OUVERTURES.I_APP_ENVIRONN = 'Production' AND OUVERTURES.C_EQUIPE <> 'HDBANQUE' THEN OUVERTURES.IDT_APPEL ELSE NULL END) AS OUVERTS, 
		COUNT(DISTINCT CASE WHEN CLOTURES.C_NATURE = 'INC' AND CLOTURES.I_APP_ENVIRONN = 'Production' AND CLOTURES.C_EQUIPE <> 'HDBANQUE' THEN CLOTURES.IDT_APPEL ELSE NULL END) AS CLOS,
		0 AS STOCK
	FROM
		APPEL AS OUVERTURES RIGHT OUTER JOIN
		PLANNINGS AS PLANNINGS_1 ON CONVERT(char(10), OUVERTURES.D_APPEL, 103) = CONVERT(char(10), PLANNINGS_1.D_JOUR, 103) LEFT OUTER JOIN
		APPEL AS CLOTURES ON CONVERT(char(10), PLANNINGS_1.D_JOUR, 103) = CONVERT(char(10), CLOTURES.D_CLOTTECH, 103)
	WHERE
		YEAR(PLANNINGS_1.D_JOUR) = YEAR(CURRENT_TIMESTAMP)
	GROUP BY
		MONTH(PLANNINGS_1.D_JOUR)
	) AS T3 ON T2.MOIS = T3.MOIS
GROUP BY T2.MOIS

Un grand merci à tous ceux qui ont porté attention à mon post !!!

[Kropernic]

Pourquoi ne pas simplement ajouter ouvert et clos dans la CTE nommée T1 ?

Cela simplifierait grandement votre requête je pense.

[Sixool]

Quelque chose dans ce goût là ?

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
WITH T1(MOIS, OUVERTS, CLOS, DELTA) AS 
	(SELECT
		MONTH(PLANNINGS.D_JOUR) AS MOIS,
		COUNT(DISTINCT CASE WHEN OUVERTURES.C_NATURE = 'INC' AND OUVERTURES.I_APP_ENVIRONN = 'Production' AND OUVERTURES.C_EQUIPE <> 'HDBANQUE' THEN OUVERTURES.IDT_APPEL ELSE NULL END) AS OUVERTS, 
		COUNT(DISTINCT CASE WHEN CLOTURES.C_NATURE = 'INC' AND CLOTURES.I_APP_ENVIRONN = 'Production' AND CLOTURES.C_EQUIPE <> 'HDBANQUE' THEN CLOTURES.IDT_APPEL ELSE NULL END) AS CLOS,
		COUNT(DISTINCT CASE WHEN OUVERTURES.C_NATURE = 'INC' AND OUVERTURES.C_EQUIPE <> 'HDBANQUE' THEN OUVERTURES.IDT_APPEL ELSE NULL END) - COUNT(DISTINCT CASE WHEN CLOTURES.C_NATURE = 'INC' AND CLOTURES.I_APP_ENVIRONN = 'Production' AND CLOTURES.C_EQUIPE <> 'HDBANQUE' THEN CLOTURES.IDT_APPEL ELSE NULL END) AS DELTA
	FROM
		APPEL AS OUVERTURES RIGHT OUTER JOIN
		PLANNINGS ON CONVERT(char(10), OUVERTURES.D_APPEL, 103) = CONVERT(char(10), PLANNINGS.D_JOUR, 103) LEFT OUTER JOIN
		APPEL AS CLOTURES ON CONVERT(char(10), PLANNINGS.D_JOUR, 103) = CONVERT(char(10), CLOTURES.D_CLOTTECH, 103)
	WHERE
		YEAR(PLANNINGS.D_JOUR) = YEAR(CURRENT_TIMESTAMP)
	GROUP BY
		MONTH(PLANNINGS.D_JOUR)
	)
 
SELECT
	T2.MOIS,
	SUM(T1.OUVERTS) AS OUVERTS,
	SUM(T1.CLOS) AS CLOS,
	SUM(T2.STOCK) AS STOCK
FROM
	(SELECT
		MOIS,
		0 AS OUVERTS,
		0 AS CLOS,
		(SELECT
			SUM(DELTA) AS DELTA
		FROM
			T1
		WHERE
			MOIS <= a.MOIS
		) AS STOCK
	FROM T1 AS a
	GROUP BY MOIS
	) AS T2 INNER JOIN
 	T1 ON T2.MOIS = T1.MOIS
GROUP BY T2.MOIS