Création d'une requête Sql Server

Version imprimable

06/11/2016, 16h15
drknow1

Création d'une requête Sql Server

Bonjour,

Je suis sous sql server 2012 server, et je suis en train de développer une application en C#. J'ai 4 tables :

1- article (num, article,reference , code, quantite,date, prix , fourniseur)
2- mouvement d'entre ( article , reference,date, quantiteE,fournisseur)
3- mouvement de sortie ( article, reference,date,quantiteS,fourniseur,client)
4- stock (article, refernce,quantiteSt)

Comment faire pour remplir en quantiteSt la sum des (quantiteE)-(quantiteS) ?

Merci d'avance
06/11/2016, 17h11
François DORIN

Bonjour,

Si le but est d'avoir la table stock qui soit toujours fidèle aux entrées/sorties, alors le mieux est d'utiliser une vue plutôt que de mettre à jour à chaque fois la table lorsque cela est nécessaire.

Ensuite, le stock représente-t-il l'état actuel du stock, ou le delta des entrées et des sorties ? En gros, faut-il tenir compte de la valeur initiale "quantite" de la table article ?
Comment sont définies vos clés primaires ? Sur article ? reference ?
06/11/2016, 18h41
drknow1

Bonjour ,

Je n'ai aucune relation de clés entre les tables.
Je veux juste faire la delta entre les entres et sorties le stock sera base sur ce calcule en bref.
Je suis sous C# et je veux que lorsque je clique sur le bouton "etat de stock ", il me donne le résultat du calcul pour chaque article de même catégorie.

Merci pour votre collaboration.

Citation:

Envoyé par drknow1

je n'ai aucune relation des clés entre les tables

Première erreur. Une base de données est faite pour qu'il y ait des relations entre les différentes entités. Sans elles, les performances des requêtes sont loin d'être optimum.

Ensuite, s'il n'y a pas de clé explicites (pas de clé primaire, et donc pas de clé étrangère), il y a au moins des clés implicites (quelles colonnes prendre en compte pour associer un mouvement du stock à un article ? La colonne article ? La colonne référence ? Les deux ?)

Citation:

Envoyé par drknow1

je veux juste faire la delta entre les entres et sorties le stock sera base sur ce calcule en bref

Deuxième erreur. Dans ce cas, il est totalement inutile d'avoir une table stock. Une requête SELECT suffit.

Quelque chose dans le genre :
Code:

1 2 3 4 5 SELECT A.reference, E.total - S.total FROM articles AS A INNER JOIN (SELECT EA.reference, SUM(EE.quantiteE) AS total FROM articles AS EA LEFT JOIN Entree AS EE ON EA.reference = EE.reference GROUP BY EA.reference) AS E ON A.reference = E.reference INNER JOIN (SELECT SA.reference, SUM(SS.quantiteS) AS total FROM articles AS SA LEFT JOIN Sortie AS SS ON SA.reference = SS.reference GROUP BY SA.reference) AS S ON A.reference = S.reference
donnera le delta. Je suis parti du principe que la référence était la clé permettant de lier les différentes tables entre elles. A adapter donc si ce n'est pas le cas.

07/11/2016, 10h47
drknow1

1 pièce(s) jointe(s)

Bonjour,

Je vous remercie pour votre collaboration.

J'ai exécuté votre requête, mais je reçois des résultats inattendus.
Je fais un test sur un article qui avait dans les entre Q=5 et sortie 3 et je reçois dans la quantité en stock 7 comme s'il y avait des doublons.
Je mets en pièce jointe le résultat de 3 requêtes Pièce jointe 224250
07/11/2016, 11h41
François DORIN

Dans ce cas, merci de bien vouloir respecter les règles du forum : http://www.developpez.net/forums/d96...vement-poster/

Notamment, avoir le schéma de la base de données ainsi qu'un jeu de données.

Ensuite, la requête que j'ai fournie précédemment de marche pas car visiblement, la référence d'un article n'est pas unique (plusieurs articles ayant la même référence existe). Merci donc, comme je l'ai précisé plus haut, de bien vouloir nous donner les clés des différentes tables. Sans cela, nous ne pouvons rien faire.
07/11/2016, 11h52
escartefigue

Citation:

Envoyé par dorinf

Première erreur. Une base de données est faite pour qu'il y ait des relations entre les différentes entités. Sans elles, les performances des requêtes sont loin d'être optimum.

Et aussi et surtout, sans relation pas de contrôle d'intégrité possible, or, la première qualité d'une base de données bien avant la performance, est de garantir la fiabilité des données !
Pour le reste, la modélisation semble plus qu'hasardeuse :weird:
07/11/2016, 12h00
François DORIN

Citation:

Envoyé par escartefigue

Et aussi et surtout, sans relation pas de contrôle d'intégrité possible

Merci d'avoir corrigé cet honteux oubli de ma part...:oops:

Bonjour,

Je vous remercie une nouvelle fois de votre collaboration-

Vous est-il possible de m'aider à créer des relations entre les tables ?
Je crois que je vais avoir dans la table des mouvements d'entrée comme dans celle des mouvements de sortie des doublons.
C'est normal, puisqu'un article peut avoir plusieurs fois des sorties de stock avec des dates et quantités différentes.

Voici la définition de l’ensemble de mes tables :

/Table article

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
USE [GS]
GO
 
/****** Object:  Table [dbo].[Art]    Script Date: 07/11/2016 11:02:39 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Art](
	[réference] [varchar](50) NOT NULL,
	[CodeArtc] [varchar](50) NOT NULL,
	[Désignation] [nvarchar](max) NOT NULL,
	[Quantite] [numeric](18, 0) NOT NULL,
	[prix] [nchar](10) NOT NULL,
	[date] [varchar](50) NULL,
	[ENUM] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO

Table Client

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
USE [GS]
GO
 
/****** Object:  Table [dbo].[Client]    Script Date: 07/11/2016 11:03:39 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Client](
	[Nom] [varbinary](50) NOT NULL,
	[Adress] [varchar](50) NULL,
	[telephonne] [varchar](50) NULL,
	[email] [nchar](10) NULL,
	[pays] [nchar](10) NULL,
	[ville] [nchar](10) NULL,
 CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED 
(
	[Nom] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO

Table Fourniseeur

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
USE [GS]
GO
 
/****** Object:  Table [dbo].[Fournisseur]    Script Date: 07/11/2016 11:04:38 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Fournisseur](
	[Nom] [varchar](50) NOT NULL,
	[Addresse] [varchar](50) NULL,
	[telephone] [nchar](10) NULL,
	[email] [varchar](50) NULL,
	[pays] [varchar](50) NULL,
	[ville] [varchar](50) NULL,
	[Num] [int] NULL,
 CONSTRAINT [PK_Fournisseur] PRIMARY KEY CLUSTERED 
(
	[Nom] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO

Table Projet

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
USE [GS]
GO
 
/****** Object:  Table [dbo].[Projet]    Script Date: 07/11/2016 11:05:08 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Projet](
        [réference] [varchar](50) NOT NULL,
	[CodeArtc] [varchar](50) NOT NULL,
	[NProjet] [varchar](50) NULL,
	[qtedemande] [numeric](18, 0) NULL,
	[datede] [date] NULL,
	[datefin] [date] NULL,
	[client] [varchar](50) NULL
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO

Table Sortie

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
USE [GS]
GO
 
/****** Object:  Table [dbo].[sortie]    Script Date: 07/11/2016 11:05:48 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[sortie](
	[réference] [varchar](50) NOT NULL,
	[codeartc] [varchar](50) NOT NULL,
	[n.client] [varchar](50) NULL,
	[Quantite] [numeric](18, 0) NULL,
	[date] [varchar](50) NULL,
 CONSTRAINT [PK_sortie] PRIMARY KEY CLUSTERED 
(
	[codeartc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[sortie]  WITH CHECK ADD  CONSTRAINT [FK_sortie_sortie] FOREIGN KEY([codeartc])
REFERENCES [dbo].[sortie] ([codeartc])
GO
 
ALTER TABLE [dbo].[sortie] CHECK CONSTRAINT [FK_sortie_sortie]
GO

Table Stock

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
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Stock](
	[réference] [varchar](50) NOT NULL,
	[Codeartc] [varchar](50) NOT NULL,
	[quantite] [numeric](18, 0) NULL,
	[fournisseur] [varchar](50) NULL,
	[date] [varchar](50) NULL,
 CONSTRAINT [PK_Stock] PRIMARY KEY CLUSTERED 
(
	[Codeartc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[Stock]  WITH CHECK ADD  CONSTRAINT [FK_Stock_Stock] FOREIGN KEY([Codeartc])
REFERENCES [dbo].[Stock] ([Codeartc])
GO
 
ALTER TABLE [dbo].[Stock] CHECK CONSTRAINT [FK_Stock_Stock]
GO

Donc dans l'ordre

-1- commencez par ajouter des identifiants dans chacune de vos tables ! de préférence, utilisez des colonnes de type integer attribuées par le SGBD (identity)
-2- définissez ces colonnes identity comme clefs primaires
-3- créez des contraintes d'intégrité des table filles vers les tables mères en ajoutant des contraintes de type REFERENCE

Par exemple si on zoom sur la partie MCD suivante Fournisseur 0,n --- livrer --- 1,1 Article
On devrait avoir un DDL simplifié suivant
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 CREATE TABLE fournisseur (FO_id int identity(1,1) ,FO_nom varchar(50) ,CONSTRAINT PK_four PRIMARY KEY(FO_id)) CREATE TABLE article (AR_id int identity(1,1) ,AR_ref char(10) ,AR_lib varchar(50) ,FO_id int not null ,CONSTRAINT PK_article PRIMARY KEY(AR_id) ,constraint FK_FO_ID foreign KEY(FO_id) references fournisseur(FO_id) on update cascade on delete set null)

08/11/2016, 09h59
drknow1

Je vous remercie tous pour votre collaboration et votre sens du partage.
Depuis hier, j'ai décidé de re-créer ma base de données. J'ai fait une nouvelle conception et modélisation.
Maintenant je vous prie de vérifier avec moi les relations entre les clé primaires et étrangères.

pk = primary key
FK = forgein key

T_login : Username (PK ),passw, role

T_Fournisseur : f.nom (pk ,FK1 ) , f.NUM(PK),f.Address,f.tele,f.mail

T_Client : c.nom(pk).C_num(pk).C.ADD,C.tele.,c.mail

T_Projet : nom_projet (pk), N.cmd(pk),qtedemande,qtelivre,qtereste,date,article(FK2),C.nom,c.num

T_Article : article(pk,FK2),code (pk),désignation ,qte,prix,date,F.nom(FK1)

T_Sortie : article (FK),code artcile (FK),Désignation ,Quantite,Projet (FK),C.nom(FK),C.NUM(FK),date
08/11/2016, 11h16
escartefigue

J'avais indiqué

Citation:

Envoyé par escartefigue

Donc dans l'ordre

-1- commencez par ajouter des identifiants dans chacune de vos tables ! de préférence, utilisez des colonnes de type integer attribuées par le SGBD (identity)
-2- définissez ces colonnes identity comme clefs primaires
-3- créez des contraintes d'intégrité des table filles vers les tables mères en ajoutant des contraintes de type REFERENCE

Or vous proposez

Citation:

Envoyé par drknow1

pk = primary key
FK = forgein key

T_Fournisseur : f.nom (pk ,FK1 ) , f.NUM(PK),f.Address,f.tele,f.mail
T_Client : c.nom(pk).C_num(pk).C.ADD,C.tele.,c.mail

Encore une fois : un nom n'a rien à faire dans une PK, ni tout autre libellé !
Pareil pour les FK, puisque les FK sont issues des PK d'autres tables, le FK ne sont donc pas non plus des libellés
Seules les clefs alternatives AK peuvent être de type libellé (char, varchar, nchar, nvarchar, text)

Le truc c'est que vous commencez par la fin, avant de penser aux tables, rédigez clairement vos règles de gestion et notamment les cardinalités.
08/11/2016, 11h27
François DORIN

Bonjour,

Je vous invite à reprendre les points édités par escartefigue.

Citation:

T_Projet : nom_projet (pk), N.cmd(pk),qtedemande,qtelivre,qtereste,date,article(FK2),C.nom,c.num

T_Article : article(pk,FK2),code (pk),désignation ,qte,prix,date,F.nom(FK1)

Quand je lis cela, je vois qu'il y a un gros problème. Outre l'absence de colonne "id", il y a une incompréhension sur le fonctionnement des clés. Vous ne pouvez pas faire une clé étrangère "article" sur une colonne dans la table T_projet alors que la clé primaire de la table T_article est définie sur deux colonnes. Ca n'a pas de sens. Et vous aurez d'ailleurs une erreur.

Bon, voici un exemple de construction étape par étape

pré-requis :
-1- formez vous à la modélisation, pour ce faire, faites une visite ici :
http://www.developpez.net/forums/f25...thodes/merise/
-2- téléchargez un outil gratuit de modélisation : DBMain ou Jmerise par exemple

Puis
1ère étape : collecter les règles de gestion en les numérotant, rédiger un glossaire des termes métier, et faire valider le tout par les référents métier. Etape omise ici, mais indispensable !

2ème étape : établir le MCD, en voici une proposition plausible compte tenu de l'énnoncé. Faute de règles de gestion précises, j'ai supposé ici qu'un article pouvait être livré par plusieurs fournisseurs et ca permettra surtout d'avoir une table issue d'une association grâce aux cardinalités maxi n de chaque coté.

ici l'énoncé est assez simple, dans la réalité, il est fort probable que d'autres entités-types soient à ajouter (ne seraient-ce que les commandes fournisseur, les commandes clients, les engagements de commande etc...). Et bien sur il faudra aussi ajouter les associations qui en découlent
Voici donc ce que ça peut donner :

Pièce jointe 224349

3ème étape : générer le MLD à partir du MCD. La plupart des outils de modélisation permettent de le faire automatiquement en quelques clicks

4ème étape : générer le MPD à partir du MLD ou directement à partir du MCD. La aussi c'est très facile avec les outils de modélisation, voici ce qu'on obtient dans mon exemple :
Pièce jointe 224350

A partir de la, vous pouvez vérifier le script proposé par votre génération MPD, voici un extrait de ce que ca donne :

Tout d'abord, 2 tables issues de 2 entités-type du MLCD (fournisseur et article)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 create table FOURNISSEUR ( FO_IDENT numeric identity, FO_NOM varchar(50) not null, FO_NUM numeric(5,0) not null, constraint PK_FOURNISSEUR primary key (FO_IDENT) ) create table ARTICLE ( AR_IDENT numeric identity, AR_REF char(10) not null, AR_LIBELLE varchar(50) not null, constraint PK_ARTICLE primary key (AR_IDENT) )
Vous remarquerez que, comme il s'agit de tables issues d'entité type, nulle mention de FK dans ces tables
Vous remarquerez aussi que, ayant choisi des identifiants de type "séquentiel" dans le MCD, on retrouve des PK de type integer avec le mot-clef identity

Intéressons nous maintenant à une table issue d'une association, en l'occurrence le lien entre article et fournisseur, donc la table "fournir"
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 create table FOURNIR ( FO_IDENT numeric not null, AR_IDENT numeric not null, CA_DATE datetime not null, FR_QTE decimal(9,2) not null, FR_UNITE char(3) not null, constraint PK_FOURNIR primary key (FO_IDENT, AR_IDENT, CA_DATE) ) create nonclustered index FOURNIR_FK on FOURNIR (FO_IDENT ASC) create nonclustered index FOURNIR2_FK on FOURNIR (AR_IDENT ASC) alter table FOURNIR add constraint FK_FOURNIR_FOURNIR_FOURNISS foreign key (FO_IDENT) references FOURNISSEUR (FO_IDENT) alter table FOURNIR add constraint FK_FOURNIR_FOURNIR2_ARTICLE foreign key (AR_IDENT) references ARTICLE (AR_IDENT)
Remarquez ici les ordres alter, en fin de DDL, qui posent des contraintes de type REFERENCE.
Ce sont ces contraintes qui permettent de vérifier l'intégrité entre les tables fournissant les identifiants (Fournisseur et Articles) et celles qui en héritent(Fournir).
Car c'est bien ici : dans les tables issues des relations du MCD, qu'apparaissent les clef étrangères (FK)
Note : des FK sont possibles dans des tables issues d'entité-type si la cardinalité maxi est 1 d'un coté de la relation, mais ce n'est pas le cas ici.
Ici l'outil de modélisation a choisi de procéder par ALTER, mais on peut très bien poser ces contraintes dès la création de la table (dans le CREATE donc)
Les options "on update" et "on delete" permettent d'indiquer au SGBD ce qu'il doit faire concernant les tables filles en cas de MàJ dans les tables parentes.

Quelques remarques en supplément :
- les outils de modélisation génèrent par défaut un script avec tous les index correspondants aux identifiants PK ou FK. Libre à vous de supprimer ceux qui sont inutiles et d'en ajouter là où c'est nécessaire. Par exemple, créer un index sur le code SIREN est une chose assez fréquente (mais encore une fois surtout pas un index PK)
- externalisez les adresses et n° de téléphone : un même fournisseur peut avoir plusieurs adresses (de livraison, de facturation, de siège etc...) et plusieurs téléphone (fixe pro, fixe privé, portable pro, etc...)
- les poids et mesures doivent être accompagnés d'unités de mesure, rien n'interdit que votre fournisseur 1 vous livre l'article A par palette et le fournisseur 2 vous livre ce même article au kilo, article que vous livrerez ensuite à l'unité au client final, donc, pour calculer un stock, il convient d'être en mesure de convertir les unités.

16/11/2016, 10h23
drknow1

Bonjour ,

je vous remercier pour votre collaboration votre exemple est très claire , j'ai réussir à résoudre mon problème .

Je vous remercier pour vous aidez .

Cordialement