Evolution de stock en quantité et en valeur

Version imprimable

01/07/2019, 17h00
christtophe11

1 pièce(s) jointe(s)

Evolution de stock en quantité et en valeur

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
02/07/2019, 15h32
aieeeuuuuu

bonjour,

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

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:

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:

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:

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

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:

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:

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.

03/07/2019, 15h44
christtophe11

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