Evolution de stock en quantité et en valeur

**christtophe11** · 01/07/2019, 17h00

Bonjour à tous,

Je rencontre un problème pour calculer l'évolution de stock en quantité et en valeur
Les données sources sont les colonnes A à E du fichier joint
Je calcule via des tables CTE les colonnes F et G
Le calcul de la colonne G pose problème quand l'élément MRP est "consommation", il faut alors consommer le stock au prix en cours, à calculer à partir de la ligne précédente
J'ai essayé de donné un exemple concret

Merci d'avance pour votre aide

Christophe

**aieeeuuuuu** · 02/07/2019, 15h32

bonjour,

Postez la requete qui fait le calcul, on pourra vous aider à la corriger

**christtophe11** · 03/07/2019, 00h02

Hello,

Je joins le cas
En plus des infos de mon 1er message, le calcul doit tenir des partitions
ROW_NUMBER() me permettra de récupérer la ligne avec le numéro maxi pour chaque mois par couple article/date, et ainsi faire un prévisionnel en quantité et en valeur du stock à chaque fin de mois par article (c'est en fait une SQL Task dans SSIS)

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
USE [SAP]
GO
/****** Object:  Table [dbo].[MRP]    Script Date: 7/2/2019 11:01:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MRP](
	[article] [nchar](10) NULL,
	[date] [smalldatetime] NULL,
	[element_MRP] [nchar](30) NULL,
	[qte] [numeric](8, 3) NULL,
	[montant] [numeric](8, 3) NULL,
	[evolution_qte] [numeric](8, 3) NULL,
	[evolution_montant] [numeric](8, 3) NULL,
	[row_id] [numeric](3, 0) NULL
) ON [PRIMARY]
 
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'A         ', CAST(N'2019-07-01 00:00:00' AS SmallDateTime), N'stock                         ', CAST(100.000 AS Numeric(8, 3)), CAST(50.000 AS Numeric(8, 3)), NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'A         ', CAST(N'2019-07-02 00:00:00' AS SmallDateTime), N'livraison entrante            ', CAST(100.000 AS Numeric(8, 3)), CAST(70.000 AS Numeric(8, 3)), NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'A         ', CAST(N'2019-07-10 00:00:00' AS SmallDateTime), N'retour marchandise            ', CAST(-50.000 AS Numeric(8, 3)), CAST(-30.000 AS Numeric(8, 3)), NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'A         ', CAST(N'2019-07-26 00:00:00' AS SmallDateTime), N'consommation                  ', CAST(-10.000 AS Numeric(8, 3)), NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'A         ', CAST(N'2019-08-05 00:00:00' AS SmallDateTime), N'consommation                  ', CAST(-20.000 AS Numeric(8, 3)), NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'A         ', CAST(N'2019-08-30 00:00:00' AS SmallDateTime), N'livraison entrante            ', CAST(100.000 AS Numeric(8, 3)), CAST(60.000 AS Numeric(8, 3)), NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'A         ', CAST(N'2019-09-10 00:00:00' AS SmallDateTime), N'consommation                  ', CAST(-110.000 AS Numeric(8, 3)), NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'A         ', CAST(N'2019-10-15 00:00:00' AS SmallDateTime), N'livraison entrante            ', CAST(20.000 AS Numeric(8, 3)), CAST(10.000 AS Numeric(8, 3)), NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'B         ', CAST(N'2019-07-01 00:00:00' AS SmallDateTime), N'stock                         ', CAST(100.000 AS Numeric(8, 3)), CAST(50.000 AS Numeric(8, 3)), NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'B         ', CAST(N'2019-07-02 00:00:00' AS SmallDateTime), N'livraison entrante            ', CAST(100.000 AS Numeric(8, 3)), CAST(70.000 AS Numeric(8, 3)), NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'B         ', CAST(N'2019-07-10 00:00:00' AS SmallDateTime), N'retour marchandise            ', CAST(-50.000 AS Numeric(8, 3)), CAST(-30.000 AS Numeric(8, 3)), NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'B         ', CAST(N'2019-07-26 00:00:00' AS SmallDateTime), N'consommation                  ', CAST(-10.000 AS Numeric(8, 3)), NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'B         ', CAST(N'2019-08-05 00:00:00' AS SmallDateTime), N'consommation                  ', CAST(-20.000 AS Numeric(8, 3)), NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'B         ', CAST(N'2019-08-30 00:00:00' AS SmallDateTime), N'livraison entrante            ', CAST(100.000 AS Numeric(8, 3)), CAST(60.000 AS Numeric(8, 3)), NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'B         ', CAST(N'2019-09-10 00:00:00' AS SmallDateTime), N'consommation                  ', CAST(-110.000 AS Numeric(8, 3)), NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[MRP] ([article], [date], [element_MRP], [qte], [montant], [evolution_qte], [evolution_montant], [row_id]) VALUES (N'B         ', CAST(N'2019-10-15 00:00:00' AS SmallDateTime), N'livraison entrante            ', CAST(20.000 AS Numeric(8, 3)), CAST(10.000 AS Numeric(8, 3)), NULL, NULL, NULL)
GO

Code :

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

1
2
3
4
5
6
7
8
-- alimentation colonne ROW_ID
;WITH cte AS
(
 SELECT ROW_ID, ROW_NUMBER() OVER(PARTITION BY article ORDER BY article, date) AS RowNum
 FROM MRP
)
UPDATE cte
    SET ROW_ID = RowNum

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
--calcul de evolution_qte (logique non utilisable pour le evolution_montant, voir exemple de calcul dans le fichier Excel)
;WITH cte AS
(
SELECT 
    article,
	date,
	evolution_qte,
	SUM(qte) OVER(PARTITION BY article ORDER BY article, date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS evo_qte
FROM MRP
)
UPDATE cte
 SET evolution_qte = evo_qte

Super merci d'avance pour votre aide

Christophe

**aieeeuuuuu** · 03/07/2019, 11h42

quand vous parliez de CTE, je pensais à une CTE récursive.

Je ne pense pas que l'on puisse y échapper ici, puisque le calcul d'une ligne dépend du calcul de la ligne "précédente", qui lui même dépend du calcul de la ligne "précédente"...

La requête ci-dessous doit vous donner ce que vous voulez :

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
 
;WITH RN AS
(
 SELECT *, ROW_NUMBER() OVER(PARTITION BY article ORDER BY date) AS RowNum
 FROM MRP
)
, tmp AS (
	SELECT	
			article
		,	date
		,	element_MRP
		,	qte
		,	montant
		,	qte		AS evolution_qte
		,	montant	AS evolution_montant
		,	RowNum
	FROM	RN
	WHERE RowNum = 1
 
	UNION ALL
 
	SELECT 
			RN.article
		,	RN.date
		,	RN.element_MRP
		,	RN.qte
		,	RN.montant
		,	CAST(tmp.evolution_qte + RN.qte	AS numeric(8,3)) 			AS evolution_qte
		,	CAST(COALESCE(tmp.evolution_montant + RN.montant,tmp.evolution_montant + (tmp.evolution_montant/tmp.evolution_qte*RN.qte)) 	AS numeric(8,3))		AS evolution_montant
		,	RN.rowNum
	FROM	tmp
	INNER JOIN RN
		ON tmp.article = RN.article
		AND tmp.RowNum + 1 = RN.RowNum
 
)
SELECT 
			article
		,	date
		,	element_MRP
		,	qte
		,	montant
		,	evolution_qte
		,	evolution_montant
FROM tmp
ORDER BY article, date

partant de là, vous pouvez mettre à jour votre table :

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
 
;WITH RN AS
(
 SELECT *, ROW_NUMBER() OVER(PARTITION BY article ORDER BY date) AS RowNum
 FROM MRP
)
, tmp AS (
	SELECT	
			article
		,	date
		,	element_MRP
		,	qte
		,	montant
		,	qte		AS evolution_qte
		,	montant	AS evolution_montant
		,	RowNum
	FROM	RN
	WHERE RowNum = 1
 
	UNION ALL
 
	SELECT 
			RN.article
		,	RN.date
		,	RN.element_MRP
		,	RN.qte
		,	RN.montant
		,	CAST(tmp.evolution_qte + RN.qte	AS numeric(8,3)) 			AS evolution_qte
		,	CAST(COALESCE(tmp.evolution_montant + RN.montant,tmp.evolution_montant + (tmp.evolution_montant/tmp.evolution_qte*RN.qte)) 	AS numeric(8,3))		AS evolution_montant
		,	RN.rowNum
	FROM	tmp
	INNER JOIN RN
		ON tmp.article = RN.article
		AND tmp.RowNum + 1 = RN.RowNum
 
)
UPDATE MRP
	SET evolution_qte = tmp.evolution_qte
	,	evolution_montant = tmp.evolution_montant
FROM MRP
INNER JOIN tmp
	ON tmp.article = MRP.article
	AND tmp.date = MRP.date --> on suppose une unicité sur (article,date)
 
;

Cependant, on évite habituellement de stocker des valeurs calculées, théoriquement, il serait donc préférable de faire une vue effectuant les calculs. Cela dit, étant donnée la complexité du calcul évoquée pus haut (nécessité d'avoir l"historique" des calculs depuis le début pour calculer la valeur d'une ligne donnée), la dénormalisation peut effectivement s'avérer nécessaire. Je vous laisse le soin d’évaluer la pertinence de la dénormalisation dans votre contexte (il sera utile de faire les tests sur un volume et un repartition des données proche de la réalité, ie les données de production)

concernant la requete proposée :
1/ Elle suppose que la colonne montant sera toujours NULL pour les lignes de type "consommation", et uniquement pour celles-là. Sinon, il faudra remplacer le COALESCE par une CASE.
2/ Elle ne gère pas la possibilité que la première ligne soit de type "consommation". Si ce cas est possible, alors il faudra définir les règles de gestion et modifier la partie d'ancrage de la CTE.

**christtophe11** · 03/07/2019, 15h44

merci, ça fonctionne parfaitement sur le cas de test, je vais essayer en prod, j'aurais peut être à remplacer COALESCE par un CASE en effet
la première ligne de chaque partition est toujours stock (donc jamais consommation)

je vais suivre vos conseils et utiliser une vue pour les calculs

Evolution de stock en quantité et en valeur [2017]

Développement SQL Server

Discussions similaires

Partager

Partager