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

  1. #1
    Futur Membre du Club
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    juillet 2019
    Messages
    6
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Aisne (Picardie)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux

    Informations forums :
    Inscription : juillet 2019
    Messages : 6
    Points : 5
    Points
    5

    Par défaut 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
    Fichiers attachés Fichiers attachés

  2. #2
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    5 213
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 213
    Points : 10 782
    Points
    10 782

    Par défaut

    bonjour,

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

  3. #3
    Futur Membre du Club
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    juillet 2019
    Messages
    6
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Aisne (Picardie)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux

    Informations forums :
    Inscription : juillet 2019
    Messages : 6
    Points : 5
    Points
    5

    Par défaut

    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

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    5 213
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 213
    Points : 10 782
    Points
    10 782

    Par défaut

    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
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    juillet 2019
    Messages
    6
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Aisne (Picardie)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux

    Informations forums :
    Inscription : juillet 2019
    Messages : 6
    Points : 5
    Points
    5

    Par défaut

    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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 10
    Dernier message: 25/05/2007, 15h21
  2. Calcul Stock Actuel mm lorsqu'une valeur est null
    Par priest69 dans le forum Access
    Réponses: 3
    Dernier message: 12/09/2005, 18h58
  3. [VB.Net]Procédure Stocké retournant une valeur
    Par waldo2188 dans le forum ASP.NET
    Réponses: 2
    Dernier message: 03/05/2005, 12h56
  4. Valeur de retour d'une procédure stockée
    Par Rudyweb dans le forum MFC
    Réponses: 4
    Dernier message: 25/02/2005, 17h52
  5. test d'une valeur stocké dans une table
    Par EssaiEncore dans le forum ASP
    Réponses: 3
    Dernier message: 18/02/2005, 11h39

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo