Discussion :

[yoyodemars]

Bonjour,
j'aurai besoin d'aide pour résoudre un problème.
Je dois réaliser des calculs sur une table qui ce compose ainsi:

TABLE :MOUVEMENT
DATE ID_PRODUIT QTE_IN QTE_OUT TYPE_MOUVEMENT

01/01/2012 1 1 0 ACH
01/01/2012 1 1 0 ACH
01/01/2012 2 0 1 VTE
01/02/2012 1 10 0 INV
03/01/2012 1 0 3 VTE


LE but est d'obtenir une table STOCK contenant tous les ID_PRODUIT et une colonne STOCK

Je parviens a faire le regroupement et la somme de QTE_IN et QTE_OUT par cette requête.

Code :

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

1
2
insert into STOCK (ID_PRODUIT,TOTAL_QTE_IN,TOTAL_QTE_OUT)
        select distinct ID_PRODUIT ,sum(QTE_IN),sum(QTE_OUT) from MOUVEMENT where GROUP BY ID_PRODUIT

Mais le problème est que chaque ligne TYPE_MOUVEMENT='INV' affecte un nouveau stock et non une entrée.

D'après vous quel serait la meilleure approche ?
Merci d'avance.

[yoyodemars]

J'ai trouvé une solution en utilisant un curseur.
Cette solution me permet de calculer l’évolution du stock sur chaque ligne et non d'avoir uniquement un stock final.

Voila un exemple du code,si cela peut servir a quelqu'un.

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
 
CREATE TABLE [dbo].[temp](
	[REFERENCE] char(13) NULL,
	[STOCK] [numeric] default 0 NULL,
	[CODE_ORIGINE] char(10)  NULL,
)
 
declare @REFERENCE char(13)
declare @REFERENCEold char(13)
declare @QTE_ENTREE numeric 
declare @QTE_SORTIE numeric 
declare @CODE_ORIGINE char(10)
declare @stock numeric
declare @stockold numeric
 
DECLARE Mon_Curseur CURSOR FOR
SELECT  REFERENCE,QTE_ENTREE,QTE_SORTIE,CODE_ORIGINE from MOUVSTK01 order by REFERENCE
OPEN Mon_Curseur;
 
FETCH Mon_Curseur INTO @REFERENCE,@QTE_ENTREE,@QTE_SORTIE,@CODE_ORIGINE
 
WHILE @@FETCH_STATUS =0
	BEGIN
		IF @REFERENCEold<>@REFERENCE OR @CODE_ORIGINE='INV'
			BEGIN
			set @stock=@QTE_ENTREE-@QTE_SORTIE
			insert into temp (REFERENCE,STOCK,CODE_ORIGINE) values (@REFERENCE,@STOCK,@CODE_ORIGINE)
 
			set @stockold=@stock
			set @REFERENCEold=@REFERENCE
			END
		ELSE
			BEGIN
			SET @stock=@STOCK+@QTE_ENTREE-@QTE_SORTIE
			insert into temp (REFERENCE,STOCK,CODE_ORIGINE) values (@REFERENCE,@STOCK,@CODE_ORIGINE)
 
			set @stockold=@stock
			set @REFERENCEold=@REFERENCE
			END
 
		FETCH Mon_Curseur INTO @REFERENCE,@QTE_ENTREE,@QTE_SORTIE,@CODE_ORIGINE
 
	END
 
 
CLOSE Mon_Curseur
DEALLOCATE Mon_Curseur

Je vais voir si je peux directement alimenter un champ stock sur ma table mouvstk01 plutot que de créer une table temp.

Je suis ouvert a toute autre solution qui pourrait être moins consommatrice pour le serveur et plus rapide (le temps de traitement des différent fichiers est autour des 1 heure 30 actuellement).

Merci par avance.

[Kropernic]

Pourrais-tu préciser ?

J'ai bien compris que ACH = achat et donc que ton stock est augmenté de la valeur de la colonne QTE_IN.
J'ai bien compris aussi que VTE = vente et donc que ton stock est diminué de la valeur de la colonne QTE_OUT.

Par contre, pour INV, je ne vois pas bien ce que tu veux dire par "une nouvelle entrée".

Aussi, quel résultat final veux-tu exactement ? Ce n'est pas très clair.

Pour finir, même si la table ne contient que 5 colonnes, pense à respecter la charte de postage (et donc à poster le code DDL pour cette table).

[yoyodemars]

Bonjour,

Effectivement je ne suis pas très clair dans ma demande car je me rend compte que ce que j'ai effectué en utilisant le curseur m'ouvre d'autre possibilités.

A la base je voulais obtenir le stock final de chaque référence se trouvant dans mouvstk01.

cette table est remplie par un import de fichiers récupéré d'un autre logiciel.

structure de la 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
 
CREATE TABLE [dbo].[MOUVSTK01](
	[CODE_ORIGINE] char(10)  NULL,
	[DATE] [smalldatetime] NULL,
	[CODE_CLIFOUR] char(8) default '' NULL,
	[CODE_MAGASIN] [numeric] NULL,
	[REFERENCE] char(13) NULL,
	[QTE_ENTREE] [numeric] default 0 NULL,
	[QTE_SORTIE] [numeric] default 0 NULL,
	[PUHT] decimal(11,5) default 0 NULL,
	[PUHTNET] decimal(11,5) default 0 NULL,
	[PUTTC] decimal(11,5) default 0 NULL,
	[PUTTCNET] decimal(11,5) default 0 NULL,
	[TAUX_REMISE] [decimal] default 0 NULL,
	[PRHT] decimal(11,5) default 0 NULL,
	[TYPE_VENTE] [numeric] default 0 NULL,
	[CODE_TARIF] [numeric] default 0 NULL,
	ACH_TOTALHT as (QTE_ENTREE)*PUHT,
	ACH_TOTALHTNET as (QTE_ENTREE)*PUHTNET,
	ACH_TOTALTTC as (QTE_ENTREE)*PUTTC,
	ACH_TOTALTTCNET as (QTE_ENTREE)*PUTTCNET,
	ACH_TOTALPRHT as (QTE_ENTREE)*PRHT,
	VTE_TOTALHT as (QTE_SORTIE)*PUHT,
	VTE_TOTALTTC as (QTE_SORTIE)*PUTTC,
	VTE_TOTALTTCNET as (QTE_SORTIE)*PUTTCNET,
	VTE_TOTALPRHT as (QTE_SORTIE)*PRHT,
	VTE_TOTALMARGE as ((QTE_SORTIE*PUHTNET)-(QTE_SORTIE*PRHT)) 
)

Le problème qu'il y a avec la ligne 'INV' c'est quelle fixe une nouvelle valeur du stock.

N'arrivant pas a faire de requette "simple" j'ai décidé d'utiliser un curseur.

J'ai donc modifier ma table mouvstk01 de cette facon:
rajout d'un champ ID_LIGNE et d'un champ STOCK

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
 
CREATE TABLE [dbo].[MOUVSTK01](
	ID_LIGNE int IDENTITY(1,1),
	[CODE_ORIGINE] char(10)  NULL,
	[DATE] [smalldatetime] NULL,
	[CODE_CLIFOUR] char(8) default '' NULL,
	[CODE_MAGASIN] [numeric] NULL,
	[REFERENCE] char(13) NULL,
	[QTE_ENTREE] [numeric] default 0 NULL,
	[QTE_SORTIE] [numeric] default 0 NULL,
	[PUHT] decimal(11,5) default 0 NULL,
	[PUHTNET] decimal(11,5) default 0 NULL,
	[PUTTC] decimal(11,5) default 0 NULL,
	[PUTTCNET] decimal(11,5) default 0 NULL,
	[TAUX_REMISE] [decimal] default 0 NULL,
	[PRHT] decimal(11,5) default 0 NULL,
	[TYPE_VENTE] [numeric] default 0 NULL,
	[CODE_TARIF] [numeric] default 0 NULL,
	ACH_TOTALHT as (QTE_ENTREE)*PUHT,
	ACH_TOTALHTNET as (QTE_ENTREE)*PUHTNET,
	ACH_TOTALTTC as (QTE_ENTREE)*PUTTC,
	ACH_TOTALTTCNET as (QTE_ENTREE)*PUTTCNET,
	ACH_TOTALPRHT as (QTE_ENTREE)*PRHT,
	VTE_TOTALHT as (QTE_SORTIE)*PUHT,
	VTE_TOTALTTC as (QTE_SORTIE)*PUTTC,
	VTE_TOTALTTCNET as (QTE_SORTIE)*PUTTCNET,
	VTE_TOTALPRHT as (QTE_SORTIE)*PRHT,
	VTE_TOTALMARGE as ((QTE_SORTIE*PUHTNET)-(QTE_SORTIE*PRHT)), 
	[STOCK] [numeric] default 0 NULL
 
constraint pk_presence1
primary key (ID_LIGNE)
)

Voici ma requette avec le curseur qui calcul et affecte le stock

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
 
declare @REFERENCE char(13)
declare @REFERENCEold char(13)
declare @QTE_ENTREE numeric 
declare @QTE_SORTIE numeric 
declare @CODE_ORIGINE char(10)
declare @stock numeric
declare @stockold numeric
declare @id_ligne int
 
 
DECLARE Mon_Curseur CURSOR FOR
SELECT  ID_LIGNE,REFERENCE,QTE_ENTREE,QTE_SORTIE,CODE_ORIGINE from MOUVSTK01 order by REFERENCE,ID_LIGNE
OPEN Mon_Curseur;
 
FETCH Mon_Curseur INTO @ID_LIGNE,@REFERENCE,@QTE_ENTREE,@QTE_SORTIE,@CODE_ORIGINE
 
WHILE @@FETCH_STATUS =0
	BEGIN
		IF @REFERENCEold<>@REFERENCE OR @CODE_ORIGINE='<INV>'
			BEGIN
			set @stock=@QTE_ENTREE-@QTE_SORTIE
			update mouvstk01 set STOCK=@stock where ID_LIGNE=@ID_LIGNE
 
			set @stockold=@stock
			set @REFERENCEold=@REFERENCE
			END
		ELSE
			BEGIN
			SET @stock=@STOCK+@QTE_ENTREE-@QTE_SORTIE
			update mouvstk01 set STOCK=@stock where ID_LIGNE=@ID_LIGNE
 
			set @stockold=@stock
			set @REFERENCEold=@REFERENCE
			END
 
		FETCH Mon_Curseur INTO @ID_LIGNE,@REFERENCE,@QTE_ENTREE,@QTE_SORTIE,@CODE_ORIGINE
 
	END
 
 
CLOSE Mon_Curseur
DEALLOCATE Mon_Curseur

Je peux maintenant obtenir le stock de mes produits a n'importe quelle date très rapidement.

Code :

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

1
2
3
4
5
6
7
 
select mouvstk01.reference,id_ligne,stock
from (select mouvstk01.reference,MAX(id_ligne) as maxid_ligne
		from mouvstk01 where mouvstk01."date"<='01/01/2013'
			group by reference) temp
 
join mouvstk01 on mouvstk01.reference = temp.reference and mouvstk01.id_ligne = temp.maxid_ligne

Y a t'il moyen d’améliorer cette requette avec curseur qui est "trés" longue(1h30).
car mouvstk01 contient environ 5.5 millions de lignes actuellement pour 1 ans d'historique.
Elle se lancera la nuit hors production.

En espérant avoir été plus clair.

[Kropernic]

Que donne ceci ?

Je ne garantis pas les fautes de syntaxe, j'ai fait ça directement sur le forum.

L'idée est de prendre la dernière date d'inventaire pour chaque produit et ensuite, de faire la somme des stocks à partir de cette date.

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
 
WITH INV(DATE,        ID_PRODUIT)
AS (
    SELECT
            MAX(DATE) AS 'DATE',
            ID_PRODUIT
    FROM
            MOUVEMENT
    WHERE
            TYPE_MOUVEMENT = 'INV'
    GROUP BY
            ID_PRODUIT
)
 
SELECT
        MVT.ID_PRODUIT,
        SUM(
        CASE MVT.TYPE_MOUVEMENT
            WHEN 'INV' THEN MVT.QTE_IN
            WHEN 'ACH' THEN MVT.QTE_IN
            WHEN 'VTE' THEN MVT.QTE_OUT*-1
        END) AS 'QTE_STOCK'
FROM
        MOUVEMENT MVT
            INNER JOIN INV
                ON   MVT.ID_PRODUIT = INV.ID_PRODUIT
                AND MVT.DATE >= INV.DATE
GROUP BY
        ID_PRODUIT

EDIT : Je me suis basé sur les noms de colonnes que tu donnes dans ton premier message car je n'ai pas retrouvé la colonne TYPE_MOUVEMENT dans le DDL que tu donnes.

[yoyodemars]

Effectivement dans mon 1er post j'avais simplifié la table pour être plus clair
Type mouvement correspond a CODE_ORIGINE et il y a plusieurs valeurs possible differente de ACH et VTE.

J'ai compris l'esprit de ta requette , je test ça demain.

EDIT:Je pense qu'il va y avoir un problème car tous les produits non pas forcement un inventaire et donc ces produits seront exclus du calcul.

Merci.

[yoyodemars]

La requette marche mais comme je le pensai les références qui n'ont pas de ligne
<INV> ne sont pas prises en compte.

La requette adapté ma 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
 
WITH INV("date",reference)
AS (
    SELECT
            MAX("DATE") AS 'DATE', reference
    FROM
            mouvstk01
    WHERE
            code_origine = '<INV>'
    GROUP BY
            reference
)
 
SELECT
        mvt.reference,
        SUM(
        CASE mvt.code_origine
            WHEN '<INV>' THEN mvt.qte_entree
            WHEN 'BRA' THEN mvt.qte_entree
            WHEN 'FAA' THEN mvt.qte_entree
            WHEN 'TIC' THEN mvt.qte_sortie*-1
        END) AS 'QTE_STOCK'
FROM
        mouvstk01 mvt
            INNER JOIN INV
                ON   mvt.reference = INV.reference
                AND mvt.DATE >= INV.DATE
GROUP BY
        mvt.reference

serait il possible de faire un truc du style :
WHEN '<INV>' THEN mvt.qte_entree-QTE_STOCK
et de faire le calcul sur toute la table?

[Kropernic]

C'est triché mais pour simplifier, pourquoi ne pas faire qqch du genre :

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
 
INSERT INTO MOUVEMENT
SELECT
            '19000101',
            ID_PRODUIT,
            0,
            0,
            'INV'
FROM
            MOUVEMENT
WHERE
            ID_PRODUIT NOT IN 
                    (
                     SELECT ID_PRODUIT FROM MOUVEMENT WHERE TYPE_MOUVEMENT = 'INV'
                    )

De cette manière, tous tes produit auront une ligne d'inventaire.

[yoyodemars]

Effectivement je n'y avait pas pensé.

[yoyodemars]

Bonjour,
Je ne comprend pas pourquoi cette requette me retourne 0 résultat :

Code :

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

1
2
3
4
5
6
7
 
SELECT REFERENCE FROM MOUVSTK01
WHERE 
REFERENCE NOT IN
(
SELECT DISTINCT REFERENCE FROM MOUVSTK01 WHERE CODE_ORIGINE ='<INV>'
)

car

Code :

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

1
2
 
SELECT DISTINCT REFERENCE FROM MOUVSTK01 WHERE CODE_ORIGINE ='<INV>'

me retourne la liste des references qui ont un inventaire:51621 lignes

Code :

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

1
2
 
SELECT DISTINCT REFERENCE FROM MOUVSTK01

me retourne la liste complete de toutes les reference:52466 lignes

Il y a donc bien des reference qui n'ont pas d'inventaire.

[Kropernic]

J'avoue que là, je sèche aussi...

A tout hasard, vous n’exécuteriez pas 2 requêtes dans la même "page" dans SSMS ? Parce qu'alors, à moins de cliquer dans le set de résultat dont on veut le nombre de lignes, le nombre de lignes indiqué en bas à droite de la fenêtre est la somme des nombres de lignes des deux requêtes.

A part ça, je ne vois pas pourquoi vous n'auriez rien comme résultat...

Je passe la main

[SQLDev]

Bonjour,

A tout hasard la requête :

Code :

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

SELECT DISTINCT REFERENCE FROM MOUVSTK01 WHERE CODE_ORIGINE ='<INV>'

ne te renverrai pas une référence avec l'identificateur d'absence de valeur (NULL) ?

Parce que si c'est le cas, c'est normal.

Code :

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

1
2
 
WHERE REFERENCE NOT IN ('Valeur1', 'Valeur2',..., NULL)

est équivalent à :

Code :

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

1
2
3
4
WHERE REFERENCE <> 'Valeur1'
OR REFERENCE <> 'Valeur2'
...
OR REFERENCE <> NULL

Voir le sujet sur NULL de SQLPro : http://sqlpro.developpez.com/cours/null/#L1

[yoyodemars]

C'est bon le problème ne vient pas de la requette
Il y avait une valeur NULL sur reference ,ce qui bloque la requette.

J'ai modifier mon script d'import pour corriger cette abbé-ration.

Merci.

[yoyodemars]

Bonjour,
Merci a vous de m'avoir aidé dans la conception de ces requettes.

Je ne suis aperçu qu'il n'y a plus besoin d'utiliser le CASE car on sélection la 1er ligne 'INV' comme point de depart du calcul de stock.
pour ne pas avoir a rajouter des lignes 'INV' pour les articles n'ayant pas d'inventaire j'ai fais une 2eme requette.

Le résultat final:

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
51
52
53
54
55
56
57
58
59
60
 
DROP TABLE STOCK;
go
CREATE TABLE STOCK
(
REFERENCE char(13) NOT NULL,
STOCK numeric null,
 
)
 
DECLARE @STOCK_DATE as date
 
 
 
SET DATEFORMAT dmy
SET @STOCK_DATE='01/01/2013'
;
/*----------------------------------------------------------------------*/
WITH INV("date",reference)
AS (
    SELECT
            MAX("DATE") AS 'DATE', reference
    FROM
            mouvstk01
    WHERE
            (code_origine = '<INV>' and mouvstk01.date<=@STOCK_DATE)
    GROUP BY
            reference
)
 
INSERT stock (REFERENCE,STOCK) 
 
SELECT
        mvt.reference,
        SUM(QTE_ENTREE-QTE_SORTIE) AS 'QTE_STOCK'
 
 
FROM
        mouvstk01 mvt
            INNER JOIN INV
                ON   mvt.reference = INV.reference
                AND mvt.DATE >= INV.DATE
GROUP BY
        mvt.reference
 
 
/* CALCUL DU STOCK POUR LES PRODUITS SANS INV */
 
INSERT stock (REFERENCE,STOCK) 
 
SELECT DISTINCT REFERENCE,SUM(QTE_ENTREE-QTE_SORTIE)
FROM
     mouvstk01
WHERE
            REFERENCE NOT IN 
                    (
                     SELECT DISTINCT REFERENCE FROM mouvstk01 WHERE CODE_ORIGINE = '<INV>'
                    )
                    and mouvstk01.date<=@STOCK_DATE
           group by reference