Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Evolution de stock en quantité et en valeur


Sujet :

Développement SQL Server

  1. #1
    Futur Membre du Club
    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

  2. #2
    Modérateur

    bonjour,

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

  3. #3
    Futur Membre du Club
    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

  4. #4
    Modérateur

    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.

  5. #5
    Futur Membre du Club
    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