comment faire un cumul progressif par mois en sql

Version imprimable

20/01/2011, 18h04
cana13

comment faire un cumul progressif par mois en sql

Bonjour à tous,

Je voudrais faire une requête sql qui me renvoi la somme des valeurs de façon cumulative sur les mois de l'année depuis le début de l'année pour voir la progression. Je m'explique :

j'ai une table contenant par date des produits et des quantités vendues.
Je voudrais faire une requête qui me renvoi par produit et par mois la somme des quantités depuis le début de l'année de la façon suivante :

pour janvier : somme des quantités de janvier
pour février : somme des quantités de janvier + février
pour mars : somme des quantités de janvier + février + mars
etc ...

est ce possible et facilement réalisable ?

Merci d'avance de votre aide.

Vous n'avez pas beaucoup cherché !
http://sqlpro.developpez.com/article...clause-window/

De plus vous n'avez pas respecté la charte de postage :
http://www.developpez.net/forums/d96...vement-poster/
Code:

1 2 3 4 5 6 7 8 -- jeu d'essais : CREATE TABLE T_VENTE_VTE (JOUR DATETIME, MONTANT DECIMAL(16,2)) GO INSERT INTO T_VENTE_VTE SELECT CAST(CAST(CURRENT_TIMESTAMP AS FLOAT) - 900 * RAND() AS DATETIME), RAND() * 10000 GO 1000
Code:

1 2 3 4 5 6 7 8 -- solution : SELECT YEAR(JOUR), MONTH(JOUR), MONTANT, (SELECT SUM(Tin.MONTANT) FROM T_VENTE_VTE AS Tin WHERE YEAR(Tin.JOUR) = YEAR(Tout.JOUR) AND MONTH(Tin.JOUR) <= MONTH(Tout.JOUR)) AS CUMUL_AN_MOIS FROM T_VENTE_VTE AS Tout ORDER BY 1, 2;
Et pour apprendre le SQL, mon site web, comme mon bouquin, peuvent vous y aider !

A +

20/01/2011, 20h53
elsuket

Bonjour,

C'est faisable dès SQL Server 2005 en utilisant une requête récursive introduite par une expression de table commune.

Donnez la liste des tables qui participent à votre requête ainsi que leur définition pour que nous puissions vous aider à écrire votre requête ;)

@++ ;)
20/01/2011, 21h14
cana13

Sqlpro : J'ai cherché (pas des masses je le reconnais mais un peu tout de même) mais j'aurai pu chercher longtemps avant de découvrir les fonctions OLAP ou fenestrées pour solutionner mon problème ! Et puis le forum n'est il pas la pour ça ? En tout cas, Merci pour ta solution. Je vais la tester.

Elsuket : Ma table est toute simple : Code_produit, Date, Quantité.
Qu'entends tu par 'requête récursive introduite par une expression de table commune' ? est ce que ça rejoint la proposition de Sqlpro ?
20/01/2011, 22h19
SQLpro

Citation:

Envoyé par cana13

Elsuket : Ma table est toute simple : Code_produit, Date, Quantité.
Qu'entends tu par 'requête récursive introduite par une expression de table commune' ? est ce que ça rejoint la proposition de Sqlpro ?

Oui, mais pas tout à fait de la même façon et avec les performances en moins !!!!!

A +
21/01/2011, 10h33
cana13

Merci, ça fonctionne super bien. Bonne journée à tous.
02/02/2011, 13h35
cana13

Bonjour,

J'ai placé cette requête dans mon rapport RS mais après quelques tests il s'avère que cela ne fonctionne pas dans le cas suivant :
si je n'ai pas de valeur pour le dernier mois.

par exemple si j'interroge mon rapport en mars mais que je n'ai pas encore de valeur pour mars, il ne m'affiche rien alors que je voudrais qu'il me répète la valeur qu'il y avait sur février. (et ainsi de suite sur les autres mois de l'année).

existe il un moyen de faire cela ?

merci d'avance.
02/02/2011, 13h46
SQLpro

Faites un CROSS JOIN avec une table de temps à la granularité voulue. En effet SQL ne sait pas inventer par miracle des données qu'il n'a pas déjà dans la base....

A +
02/02/2011, 14h27
cana13

Oui bien sur, je sais bien que ce phénomène est logique ! c'était justement comment contourner ce fonctionnement que je cherchais. Je vais essayer avec le CROSS JOIN.

Un grand merci, A+

Au secours sqlpro !! je ne m'en sors absolument pas !

voila ma table de test :

Code:

1
2
3
4
5
6
7
 
CODE_PRODUIT	DATE	QUANTITE
A         	2011-01-01 00:00:00.000	2
B         	2011-01-01 00:00:00.000	3
C         	2011-01-01 00:00:00.000	4
A         	2011-02-01 00:00:00.000	5
B         	2011-02-01 00:00:00.000	3

ou A et B ont des valeurs en Janvier et février mais C n'a que des valeur en Janvier

et ma requête d'origine :

Code:

1
2
3
4
5
6
SELECT CODE_PRODUIT ,DATE
     ,(SELECT SUM(QUANTITE) FROM  dbo.VENTE_TEST AS Tin
        WHERE  YEAR(Tin.DATE) = YEAR(MAITRE.DATE) AND  MONTH(Tin.DATE) <= MONTH(MAITRE.DATE)
        AND Tin.CODE_PRODUIT = MAITRE.CODE_PRODUIT) AS VALEUR
 
  FROM dbo.VENTE_TEST AS MAITRE

qui me renvoie 5 lignes (2 pour A, 2 pour B et 1 pour C)

Code:

1
2
3
4
5
6
 
A         	2011-01-01 00:00:00.000	2
B         	2011-01-01 00:00:00.000	3
C         	2011-01-01 00:00:00.000	4
A         	2011-02-01 00:00:00.000	7
B         	2011-02-01 00:00:00.000	6

Lorsque je croise avec ma table temps :

Code:

1
2
3
4
5
CROSS JOIN
  dbo.DIM_TEMPS
   WHERE TEMPS_ANNEE = '2011' AND TEMPS_MOIS = 2
   GROUP BY  CODE_PRODUIT,DATE
  ORDER BY 2

ça ne change absolument rien. Je pense que c'est normal car rien ne lui indique de forcer le mois de février sur le produit C.

J'ai donc essayé le code suivant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT CODE_PRODUIT ,DATE
     ,(SELECT SUM(QUANTITE) FROM  dbo.VENTE_TEST AS Tin
        WHERE  YEAR(Tin.DATE) = YEAR(MAITRE.DATE) AND  MONTH(Tin.DATE) <= MONTH(MAITRE.DATE)
        AND Tin.CODE_PRODUIT = MAITRE.CODE_PRODUIT) AS VALEUR
 
  FROM dbo.VENTE_TEST AS MAITRE
UNION
SELECT CODE_PRODUIT ,'01/02/2011' AS DATE
     ,(SELECT SUM(QUANTITE) FROM  dbo.VENTE_TEST AS Tin
        WHERE  YEAR(Tin.DATE) = YEAR(MAITRE.DATE) AND  MONTH(Tin.DATE) <= MONTH(MAITRE.DATE)
        AND Tin.CODE_PRODUIT = MAITRE.CODE_PRODUIT) AS VALEUR
 
  FROM dbo.VENTE_TEST AS MAITRE
CROSS JOIN
  dbo.DIM_TEMPS
   WHERE TEMPS_ANNEE = '2011' AND TEMPS_MOIS = 2
   GROUP BY  CODE_PRODUIT,DATE
  ORDER BY 2

mais la, je récupère bien mon C sur février avec la valeur de janvier mais également 2 lignes en trop avec les produits A et B en date de février mais avec les valeurs de Janvier. c'est à dire que j'ai 8 lignes au lieu de 6 (3 A, 3 B et 2 C).

Code:

1
2
3
4
5
6
7
8
9
10
 
CODE_PRODUIT	DATE	VALEUR
A         	2011-01-01 00:00:00.000	2
B         	2011-01-01 00:00:00.000	3
C         	2011-01-01 00:00:00.000	4
A         	2011-02-01 00:00:00.000	2
A         	2011-02-01 00:00:00.000	7
B         	2011-02-01 00:00:00.000	3
B         	2011-02-01 00:00:00.000	6
C         	2011-02-01 00:00:00.000	4

Je suis désolé si mes explications sont sans doute un peu confuses ! mais enfin bref, je ne m'en sors pas ...

J' arrive finalement à mon résultat en faisant comme suis :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
SELECT CODE_PRODUIT,DATE
     ,(SELECT SUM(QUANTITE) FROM  dbo.VENTE_TEST AS Tin
        WHERE  YEAR(Tin.DATE) = YEAR(MAITRE.DATE) AND   MONTH(Tin.DATE) <= MONTH(MAITRE.DATE)
        AND Tin.CODE_PRODUIT = MAITRE.CODE_PRODUIT) AS VALEUR
 
  FROM dbo.VENTE_TEST AS MAITRE
 
   UNION
 
   SELECT  CODE_PRODUIT,'01/02/2011' AS DATE
     ,(SELECT SUM(QUANTITE) FROM  dbo.VENTE_TEST AS Tin
        WHERE  YEAR(Tin.DATE) = YEAR(MAITRE.DATE) AND  MONTH(Tin.DATE) <= MONTH(MAITRE.DATE)
        AND Tin.CODE_PRODUIT = MAITRE.CODE_PRODUIT) AS VALEUR
 
  FROM dbo.VENTE_TEST AS MAITRE
 
   WHERE CODE_PRODUIT NOT IN (SELECT CODE_PRODUIT
       FROM dbo.VENTE_TEST WHERE DATE = '01/02/2011' )
   GROUP BY  CODE_PRODUIT,DATE
  ORDER BY 2

Mais du coup, je n'utilise pas de CROSS JOIN ni de table Temps !

Par contre c'est assez long et ça ne peut pas marcher si on veut gérer au delà de 1 mois sans vente. (ou alors il faudrait faire des successions d'union ...).

Donc, je ne pense pas que ça soit la bonne solution. Si quelqu'un peut m'indiquer comment faire avec le cross join ?

Postez le DDL de vos tables !

Sinon le principe est de faire la chose suivante :

Code:

1
2
3
4
5
6
7
8
9
10
SELECT TEMPS_ANNEE, TEMPS_MOIS, 
       CODE_PRODUIT, DATE, 
       (SELECT SUM(QUANTITE) 
        FROM   dbo.VENTE_TEST AS Tin
        WHERE  YEAR(Tin.DATE) = D2.TEMPS_ANNEE 
          AND  MONTH(Tin.DATE) <= D2.TEMPS_MOIS 
          AND Tin.CODE_PRODUIT = D1.CODE_PRODUIT) AS VALEUR
FROM   dbo.VENTE_TEST AS D1
       CROSS JOIN dbo.DIM_TEMPS AS D2
WHERE TEMPS_ANNEE = '2011' AND TEMPS_MOIS = 2

A +

Cela ne marche pas :

le script de création de ma table temps :

Code:

1
2
3
4
5
6
7
8
9
 
CREATE TABLE [dbo].[DIM_TEMPS](
	[TEMPS_ID] [smallint] NOT NULL,
	[TEMPS_DATE] [datetime] NOT NULL,
	[TEMPS_ANNEE] [smallint] NOT NULL,
	[TEMPS_MOIS] [tinyint] NOT NULL,
	[TEMPS_JOUR] [tinyint] NOT NULL,
 
) ON [PRIMARY]

le script de création de ma table Vente :

Code:

1
2
3
4
5
6
 
CREATE TABLE [dbo].[VENTE_TEST](
	[CODE_PRODUIT] [nchar](10) NOT NULL,
	[DATE] [datetime] NULL,
	[QUANTITE] [int] NULL
) ON [PRIMARY]

Le contenu de ma table :

Code:

1
2
3
4
5
6
7
 
PRODUIT	DATE	                     QUANTITE
A         	2011-01-01 00:00:00.000	2
B         	2011-01-01 00:00:00.000	3
C         	2011-01-01 00:00:00.000	4
A         	2011-02-01 00:00:00.000	5
B         	2011-02-01 00:00:00.000	3

la requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT  DISTINCT TEMPS_ANNEE, TEMPS_MOIS, 
       CODE_PRODUIT, DATE, 
       (SELECT SUM(QUANTITE) 
        FROM  [dbo].[VENTE_TEST] AS Tin
        WHERE  YEAR(Tin.DATE) = D2.TEMPS_ANNEE 
          AND  MONTH(Tin.DATE) <= D2.TEMPS_MOIS 
          AND Tin.CODE_PRODUIT = D1.CODE_PRODUIT) AS VALEUR
FROM   [dbo].[VENTE_TEST] AS D1
       CROSS JOIN [dbo].[DIM_TEMPS] AS D2
WHERE TEMPS_ANNEE = '2011' AND TEMPS_MOIS >= 1 AND TEMPS_MOIS <= 2
ORDER BY  CODE_PRODUIT

et le résultat obtenu :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
ANNEE MOIS PRODUIT	DATE	                VALEUR
2011	1	A      	2011-01-01 00:00:00.000	2
2011	1	A      	2011-02-01 00:00:00.000	2
2011	2	A      	2011-01-01 00:00:00.000	7
2011	2	A      	2011-02-01 00:00:00.000	7
2011	1	B      	2011-01-01 00:00:00.000	3
2011	1	B      	2011-02-01 00:00:00.000	3
2011	2	B      	2011-01-01 00:00:00.000	6
2011	2	B      	2011-02-01 00:00:00.000	6
2011	1	C      	2011-01-01 00:00:00.000	4
2011	2	C      	2011-01-01 00:00:00.000	4

alors que je voudrai obtenir :

Code:

1
2
3
4
5
6
7
8
 
ANNEE MOIS PRODUIT	DATE	        VALEUR
2011 1 A    	2011-01-01 00:00:00.000	2
2011 2 A    	2011-02-01 00:00:00.000	7
2011 1 B    	2011-01-01 00:00:00.000	3
2011 2 B   	2011-02-01 00:00:00.000	6
2011 1 C    	2011-01-01 00:00:00.000	4
2011 2 C    	2011-02-01 00:00:00.000	4

Ca y est, je crois que je le tiens !
il suffit d'enlever la DATE dans la requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT  DISTINCT TEMPS_ANNEE, TEMPS_MOIS, 
       CODE_PRODUIT, 
       (SELECT SUM(QUANTITE) 
        FROM  [dbo].[VENTE_TEST] AS Tin
        WHERE  YEAR(Tin.DATE) = D2.TEMPS_ANNEE 
          AND  MONTH(Tin.DATE) <= D2.TEMPS_MOIS 
          AND Tin.CODE_PRODUIT = D1.CODE_PRODUIT) AS VALEUR
FROM   [dbo].[VENTE_TEST] AS D1
       CROSS JOIN [dbo].[DIM_TEMPS] AS D2
WHERE TEMPS_ANNEE = '2011' AND TEMPS_MOIS >= 1 AND TEMPS_MOIS <= 2
ORDER BY  CODE_PRODUIT

et le résultat :

Code:

1
2
3
4
5
6
7
 
2011	1	A         	2
2011	2	A         	7
2011	1	B         	3
2011	2	B         	6
2011	1	C         	4
2011	2	C         	4

SQLpro, Un grand merci pour votre aide et votre patience !