Discussion :

[Dachetache]

Bonjour,

je viens vers car j'ai un problème concernant plusieurs tables. Je vous mets les idées: table Personne (id_Pers, nom_Pers, prénom, adresse,cp,ville), table Préparation(Id_Prép,Nom_Pers,date_Prép,Total_heure_Prép),table Plantation(Id_Plan,Nom_Pers,date_Plan,Total_heure_Plan), Table récolte(Id_Réc,Nom_Pers,date_Réc,Total_heure_Réc).
1. Je veux afficher cette table employé(Id,Nom_Pers,date_Prép,date_Plan,Total_heure_Réc).
2. Je veux calculer les heures de travail somme de (date_Prép,date_Plan,Total_heure_Réc). Donc table employé(Id,Nom_Pers,date_Prép,date_Plan,Total_heure_Réc,Somme_Heure).

J'ai créé toutes les tables, l'insertion,update,suppression finis mais je n'arrive pas la jointure entre les différentes table puis faire la somme des heures de travail.

Quelqu'un peut m'aider? Merci pour votre aide

[al1_24]

Quelles requêtes as-tu déjà essayées ?
Quels problèmes rencontres-tu avec celles-ci : erreurs de syntaxe, résultats erronés ?

[Dachetache]

J'ai essayé de mettre des clés secondaires pour faire le lien entre chaque table

voila le select

Code :

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

1
2
3
4
5
6
7
 
SELECT        Nom_Famille,DESHERBAGE.Total_Heure_Desh, ENTRETIEN.Total_Heure_Ent, PLANTATION.Total_Heure_Plan, PREPARATION_SOL.Total_Heure_Pre_Sol, RECOLTES.Total_Heure
FROM            DESHERBAGE INNER JOIN
                         PLANTATION ON DESHERBAGE.Id_Plan = PLANTATION.Id_Plan INNER JOIN
                         PREPARATION_SOL ON PLANTATION.Id_Pre_Sol = PREPARATION_SOL.Id_Pre_Sol CROSS JOIN
                         ENTRETIEN CROSS JOIN
                         RECOLTES where Nom_Famille = 'DUPONT'

la requête me donne


C'est la somme des heures que je n'arrive pas à le faire (1+0.5+1.5+2.5+2.5). Ensuite je souhaiterais avoir une alerte si l'heure est supérieur à 7 heures "heure supplémentaire" si heure est inférieur à 7 heures "vous devez des heures".

[escartefigue]

Pour votre somme, vous pouvez simplement faire quelque chose comme :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT  Nom_Famille,
        DES.Total_Heure_Desh, 
        ENT.Total_Heure_Ent, 
        PLN.Total_Heure_Plan, 
        PRE.Total_Heure_Pre_Sol, 
        REC.Total_Heure,
        DES.Total_Heure_Desh+ENT.Total_Heure_Ent+PLN.Total_Heure_Plan+PRE.Total_Heure_Pre_Sol+REC.Total_Heure as TOTAL
FROM    DESHERBAGE    as DES
INNER JOIN PLANTATION as PLN 
   ON PLN.Id_Plan = DES.Id_Plan
INNER JOIN PREPARATION_SOL as PRE
   ON PRE.Id_Pre_Sol = PLN.Id_Pre_Sol

J'ai remplacé les noms in extenso des tables par des alias (AS), c'est quand même plus pratique
Et aussi, attention, vos premières tables utilisent des jointures INNER, vous n'aurez donc des résultats que pour les lignes présentes dans les chacune des 3 premières tables
Pour les autres tables, pourquoi faites vous des jointures CROSS JOIN c'est à dire le produit cartésien

[Dachetache]

Merci de votre message, j'ai constate que dans mes tables il y a quelque chose qui ne marche. Je m'explique, j'ai mis des clés secondaires pour faire la jointure entre les tables. par exemple: j'ai ajouter un employé Durant qui a effectué la tâche récolte et désherbage.
slect * from RECOLTES


select* DESHERBAGE


Lorsque nous faisons la requête DURANT n'est affiché

Code :

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

1
2
3
4
5
6
SELECT        Nom_Famille,DESHERBAGE.Total_Heure_Desh, ENTRETIEN.Total_Heure_Ent, PREPARATION_SOL.Total_Heure_Pre_Sol, PLANTATION.Total_Heure_Plan, RECOLTES.Total_Heure
FROM            DESHERBAGE INNER JOIN
                         PLANTATION ON DESHERBAGE.Id_Plan = PLANTATION.Id_Plan INNER JOIN
                         PREPARATION_SOL ON PLANTATION.Id_Pre_Sol = PREPARATION_SOL.Id_Pre_Sol INNER JOIN
                         RECOLTES ON DESHERBAGE.Id_Desh = RECOLTES.Id_Desh CROSS JOIN
                         ENTRETIEN

Qu'est ce que je peux faire? si je veux que les autres Total_Heures sont zéro, le total de la journée est égal aux différentes tâches effectuées?

[escartefigue]

Communiquez le DDL complet de vos tables index, c'est à dire le script qui contient les ordres CREATE et ALTER , sans quoi il est très difficile de vous aider

[Dachetache]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE [dbo].[DESHERBAGE](
	[Id_Desh] [varchar](10) NOT NULL,
	[Id_Plan] [varchar](10) NULL,
	[Type_Chapelle] [varchar](30) NULL,
	[Total_Heure_Desh] [decimal](3, 2) NULL,
	[Commentaire] [varchar](250) NULL,
 CONSTRAINT [PK_DESHERBAGE] PRIMARY KEY CLUSTERED 
(
	[Id_Desh] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE [dbo].[ENTRETIEN](
	[Id_Ent] [varchar](10) NOT NULL,
	[Id_Plan] [varchar](10) NULL,
	[Nom_famille_Ent] [varchar](50) NULL,
	[Type_Chapelle] [varchar](30) NULL,
	[Total_Heure_Ent] [decimal](3, 2) NULL,
	[commentaire] [varchar](250) NULL,
	[Date_Cré] [datetime2](0) NULL,
 CONSTRAINT [PK_ENTRETIEN] PRIMARY KEY CLUSTERED 
(
	[Id_Ent] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

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
CREATE TABLE [dbo].[PLANTATION](
	[Id_Plan] [varchar](10) NOT NULL,
	[Id_Pre_Sol] [varchar](20) NULL,
	[Nom_FamillePlan] [varchar](50) NULL,
	[Date_Plan] [datetime2](0) NULL,
	[Produit] [varchar](50) NULL,
	[Type_Chapelle] [varchar](30) NULL,
	[Statut_Plan] [varchar](10) NULL,
	[Total_Heure_Plan] [decimal](3, 2) NULL,
	[Commentaire] [varchar](250) NULL,
	[Date_Cré] [datetime2](0) NULL,
 CONSTRAINT [PK_PLANTATION] PRIMARY KEY CLUSTERED 
(
	[Id_Plan] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE [dbo].[PREPARATION_SOL](
	[Id_Pre_Sol] [varchar](10) NOT NULL,
	[Matricule] [varchar](10) NULL,
	[Nom_Famille_Pré_Sol] [varchar](50) NULL,
	[Type_Chapelle] [varchar](30) NULL,
	[Total_Heure_Pre_Sol] [decimal](3, 2) NULL,
	[Commentaire] [varchar](250) NULL,
	[Date_Cré] [datetime2](0) NULL,
 CONSTRAINT [PK_PREPARATION_SOL] PRIMARY KEY CLUSTERED 
(
	[Id_Pre_Sol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE [dbo].[RECOLTES](
	[id_Recol] [varchar](10) NOT NULL,
	[Id_Desh] [varchar](10) NOT NULL,
	[Nom_Famille] [varchar](50) NULL,
	[Type_Serre] [varchar](20) NULL,
	[Total_Heure] [decimal](2, 1) NULL,
	[Status] [varchar](10) NULL,
	[Commentaire] [varchar](150) NULL,
	[Date_Cré] [datetime2](0) NULL,
 CONSTRAINT [PK_RECOLTES] PRIMARY KEY CLUSTERED 
(
	[id_Recol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

[sqllm]

Bonjour,

Je pense qu'il faut revoir votre modele de donnees. Le fait d'avoir une table par activite complexifie inutilement les requetes et finira par vous bloquer par la suite. Attention aux types de donnees egalement.

Dans les grandes lignes, il faudrait avoir une seule table ACTIVITE et rajouter un TYPE ACTIVITE. Par exemple :

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
 
CREATE TABLE ACTIVITE(
ID INT NOT NULL IDENTITY(1,1),
TYPEACT_ID INT NOT NULL,
EMP_ID SMALLINT NOT NULL,
DUREE DECIMAL(3,2),
DATECREATION DATETIME2 DEFAULT GETDATE(),
COMMENTAIRE VARCHAR(250)
 CONSTRAINT [PK_ACTIVITE] PRIMARY KEY CLUSTERED (ID))
 
CREATE TABLE TYPEACTIVITE(
ID TINYINT NOT NULL IDENTITY(1,1),
LIBELLE VARCHAR(50)
CONSTRAINT [PK_TYPEACTIVITE] PRIMARY KEY CLUSTERED (ID))
 
CREATE TABLE EMPLOYE(
ID SMALLINT IDENTITY(1,1),
NOM VARCHAR(20)
CONSTRAINT [PK_EMPLOYE] PRIMARY KEY CLUSTERED (ID))

on peut renseigner ensuite les valeurs. D'abord les types d'activites, les employes et enfin les activites.

Code :

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

1
2
3
4
5
6
7
8
 
INSERT TYPEACTIVITE(LIBELLE) 
VALUES ('DESHERBAGE'), ('ENTRETIEN'),('PLANTATION'),('PREPARATION SOL'),('RECOLTES')
 
INSERT EMPLOYE (NOM) VALUES ('DUPONT'),('DURAND')
 
INSERT ACTIVITE(TYPEACT_ID,EMP_ID,DUREE)
VALUES (1,1,1),(1,2,3),(5,1,2.5),(5,2,3)

Avec ce modele la requete est beaucoup plus simple a ecrire :

Code :

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

1
2
3
4
5
6
7
 
select E.NOM, SUM(A.DUREE) FROM ACTIVITE A 
  JOIN EMPLOYE E on E.ID = A.EMP_ID
  JOIN TYPEACTIVITE T on T.ID = A.TYPEACT_ID
WHERE E.NOM = 'DUPONT'
AND T.LIBELLE IN ('DESHERBAGE','RECOLTES')
GROUP BY E.NOM

Code :

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

1
2
3
 
NOM          TOTALDUREE
DUPONT     3.50

et si vous preferez le nombre d'heures en colonne

Code :

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

1
2
3
4
5
6
 
SELECT * FROM (Select E.NOM, T.LIBELLE, A.DUREE FROM ACTIVITE A
  JOIN EMPLOYE E on E.ID = A.EMP_ID
  JOIN TYPEACTIVITE T on T.ID = A.TYPEACT_ID
  ) C
  PIVOT (SUM(DUREE) FOR LIBELLE IN ([DESHERBAGE], [ENTRETIEN],[PLANTATION],[PREPARATION SOL],[RECOLTES])) as PIV

[Dachetache]

Bonjour,

je vous remercie de votre conseil concernant les table.
J'ai ajouté une table chapelle

Code :

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

1
2
3
4
CREATE TABLE TYPECHAPELLE(
CHAP_ID TINYINT NOT NULL IDENTITY(1,1),
LIBELLECHAP VARCHAR(50)
CONSTRAINT [PK_TYPECHAPELLE] PRIMARY KEY CLUSTERED (CHAP_ID))

et la table ACTIVITE est modifiée

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE ACTIVITE(
ID INT NOT NULL IDENTITY(1,1),
TYPEACT_ID INT NOT NULL,
EMP_ID SMALLINT NOT NULL,
CHAP_ID int NOT NULL,
DUREE DECIMAL(3,2),
DATECREATION DATETIME2 DEFAULT GETDATE(),
COMMENTAIRE VARCHAR(250)
 CONSTRAINT [PK_ACTIVITE] PRIMARY KEY CLUSTERED (ID))

j'ai ajouté 5 chapelles voici l'insertion

Code :

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

1
2
3
4
5
6
7
INSERT TYPECHAPELLE(LIBELLECHAP) 
VALUES ('Droite1'), ('Droite2'),('Droite3'),('Droite4'),('Droite5')
 
INSERT EMPLOYE (NOM) VALUES ('DUPONT'),('DURAND'),('DACHE')
 
INSERT ACTIVITE(TYPEACT_ID,EMP_ID,CHAP_ID,DUREE)
VALUES (1,1,2,1),(1,2,1,3),(5,1,2,5),(5,2,2,3), (2,3,2,3),(3,3,1,2),(4,3,3,2),(5,3,2,3)

je souhaiterais avoir que les chapelle que l'employé a travaillé pas les autres chapelles. J'ai fait cette requête mais elle me donne toutes les chapelles.

Code :

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

1
2
3
4
5
6
SELECT * FROM (Select E.NOM, T.LIBELLE, A.DUREE, CH.LIBELLECHAP,DATECREATION FROM ACTIVITE A
  JOIN EMPLOYE E on E.EMP_ID = A.EMP_ID
  JOIN TYPEACTIVITE T on T.TYPEACT_ID = A.TYPEACT_ID
  JOIN TYPECHAPELLE CH on CH.CHAP_ID = CH.CHAP_ID
  ) C
  PIVOT (SUM(DUREE) FOR LIBELLE IN ([DESHERBAGE], [ENTRETIEN],[PLANTATION],[PREPARATION SOL],[RECOLTES])) as PIV

la requête ajoute la durée à toutes les chapelles.

[sqllm]

Bonjour,

Votre requete est parfaitement valide hormis l'erreur d'inattention

Code :

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

1
2
3
4
5
6
7
SELECT * FROM (Select E.NOM, T.LIBELLE, A.DUREE, CH.LIBELLECHAP,DATECREATION FROM ACTIVITE A
  JOIN EMPLOYE E on E.EMP_ID = A.EMP_ID
  JOIN TYPEACTIVITE T on T.TYPEACT_ID = A.TYPEACT_ID
  JOIN TYPECHAPELLE CH on CH.CHAP_ID = A.CHAP_ID
  ) C
  PIVOT (SUM(DUREE) FOR LIBELLE IN ([DESHERBAGE], [ENTRETIEN],[PLANTATION],[PREPARATION SOL],[RECOLTES])) as PIV

[Dachetache]

Comment peut-on ajouter à la fin de la colonne la colonne Somme_Heure_Journée?
cette somme calculera les différente tâche.

Code :

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

1
2
3
4
5
6
SELECT * FROM (Select E.NOM, T.LIBELLE, A.DUREE FROM ACTIVITE A
  JOIN EMPLOYE E on E.EMP_ID = A.EMP_ID
  JOIN TYPEACTIVITE T on T.TYPEACT_ID = A.TYPEACT_ID
  JOIN TYPECHAPELLE CH on CH.CHAP_ID = A.CHAP_ID
  ) C
  PIVOT (SUM(DUREE) FOR LIBELLE IN ([DESHERBAGE], [ENTRETIEN],[PLANTATION],[PREPARATION SOL],[RECOLTES],[SOMME])) as PIV

J'ai pense à cette requête SOMME = DESHERBAGE.DUREE + ENTRETIEN.DUREE + PLANTATION.DUREE + PREPARATION_SOL.DUREE + RECOLTE.DUREE, je ne sais pas qu'est ce que vous pensez?

[sqllm]

Bonjour,

Il y plusieurs facons de faire. Dans le 1er cas il faut juste penser a traiter les valeurs NULL car NULL + un chifffre = NULL ! En utilisant COALESCE, SQL "remplace" la valeur NULL par le deuxieme arguement de la fonction : ici 0.

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
 
 SELECT *,
       COALESCE([DESHERBAGE], 0)
       + COALESCE([ENTRETIEN], 0)
       + COALESCE([PLANTATION], 0)
       + COALESCE([PREPARATION SOL], 0)
       + COALESCE([RECOLTES], 0) as SOMME
FROM   (Select E.NOM,
               T.LIBELLE,
               A.DUREE
        FROM   ACTIVITE A
               JOIN EMPLOYE E
                 on E.EMP_ID = A.EMP_ID
               JOIN TYPEACTIVITE T
                 on T.TYPEACT_ID = A.TYPEACT_ID
               JOIN TYPECHAPELLE CH
                 on CH.CHAP_ID = A.CHAP_ID) C
       PIVOT (Sum(DUREE)
             FOR LIBELLE IN ([DESHERBAGE],
                             [ENTRETIEN],
                             [PLANTATION],
                             [PREPARATION SOL],
                             [RECOLTES])) as PIV
ORDER  BY NOM

Une autre facon de faire est de calculer la somme directement dans la 1ere requete

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
 
 SELECT *
FROM   (Select E.NOM,
               T.LIBELLE,
               A.DUREE,
               Sum(A.DUREE)
                 OVER (
                   PARTITION BY E.NOM) as SOMME
        FROM   ACTIVITE A
               JOIN EMPLOYE E
                 on E.EMP_ID = A.EMP_ID
               JOIN TYPEACTIVITE T
                 on T.TYPEACT_ID = A.TYPEACT_ID
               JOIN TYPECHAPELLE CH
                 on CH.CHAP_ID = A.CHAP_ID) C
       PIVOT (Sum(DUREE)
             FOR LIBELLE IN ([DESHERBAGE],
                             [ENTRETIEN],
                             [PLANTATION],
                             [PREPARATION SOL],
                             [RECOLTES])) as PIV
ORDER  BY NOM

et en bonus un exemple de requete pour avoir le total general

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
 
 SELECT COALESCE(NOM, 'TOTAL'),
       Sum(DESHERBAGE)        as DESHERBAGE,
       Sum([ENTRETIEN])       as [ENTRETIEN],
       Sum([PLANTATION])      as [PLANTATION],
       Sum([PREPARATION SOL]) as [PREPARATION SOL],
       Sum([RECOLTES])        as [RECOLTES],
       Sum(SOMME)             as SOMME
FROM   (Select E.NOM,
               T.LIBELLE,
               A.DUREE,
               Sum(A.DUREE)
                 OVER (
                   PARTITION BY E.NOM) as SOMME
        FROM   ACTIVITE A
               JOIN EMPLOYE E
                 on E.EMP_ID = A.EMP_ID
               JOIN TYPEACTIVITE T
                 on T.TYPEACT_ID = A.TYPEACT_ID
               JOIN TYPECHAPELLE CH
                 on CH.CHAP_ID = A.CHAP_ID) C
       PIVOT (Sum(DUREE)
             FOR LIBELLE IN ([DESHERBAGE],
                             [ENTRETIEN],
                             [PLANTATION],
                             [PREPARATION SOL],
                             [RECOLTES])) as PIV
GROUP  BY NOM WITH ROLLUP
ORDER  BY COALESCE(NOM, 'z'),
          NOM

Le ROLLUP Permet de rajouter le total general et Le Order COALESCE(NOM, 'z) permet de positionner le total a la fin !

[Dachetache]

Bonjour,

Merci pour vos messages.

Je reviens encore sur le forum car j'ai encore besoin de vous.
Je veux avoir que la somme des activité par ligne tel que: DUPONT a fait une heure de désherbage + 5 heures de récolte la somme est 6 heures dans la serre "droite 2" ensuite DURANT n'a fait que 3 heures dans la serre droite 1 je veux la somme total est de 3 heures.

voila ce que je fais

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
SELECT *
FROM   (Select E.NOM,
               T.LIBELLE,
               A.DUREE,
			   LIBELLECHAP,
			   COMMENTAIRE,
               Sum(A.DUREE)
                 OVER (
                   PARTITION BY E.NOM) as SOMME
        FROM   ACTIVITE A
               JOIN EMPLOYE E
                 on E.EMP_ID = A.EMP_ID
               JOIN TYPEACTIVITE T
                 on T.TYPEACT_ID = A.TYPEACT_ID
               JOIN TYPECHAPELLE CH
                 on CH.CHAP_ID = A.CHAP_ID) C
       PIVOT (Sum(DUREE)
             FOR LIBELLE IN ([DESHERBAGE],
                             [ENTRETIEN],
                             [PLANTATION],
                             [PREPARATION SOL],
                             [RECOLTES])) as PIV
ORDER  BY NOM