inner join qui me pose probleme

Version imprimable

Bonjour,
J'ai un tableau comme celui ci :

Citation:

-------------------------------------------------------------
| Code | Date | Qtes | Affaire | Personne | Equipe |
-------------------------------------------------------------
PR | 23/01/2010 | 1 | n1000 | M.XX | 1
PR | 23/01/2010 | 1 | n1000 | M.ZZ | 2
PR | 23/01/2010 | 1 | n1000 | M.YY | 1
PR | 23/01/2010 | 1 | n1000 | M.TT | 3
PR | 24/01/2010 | 1 | n1000 | M.WW | 1

je voudrais savoir qui a travaillé avec M.XX sur la même affaire, le même jours, la même equipe .
voici ma requête :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 select distinct T1.[No_] as Code, convert(char(10),T1.[Forecast Date],103) as Date, cast(T1.[Forecast Quantity (Base)] as float) as Qtés, T1.[Document No_] as Affaire, T1.[Ressource] as Personne, T1.[Ressource]+' '+T2.[Ressource] as PersoEquip, T1.[Type] as CodeEvenement, T1.[Type Article] as TypeArticle, T1.[Equipe] as Equipe from dbo.[Societe$Production] T1 inner join dbo.[Societe$Production] T2 on (T1.[Document No_] = T2.[Document No_] and T1.[Forecast Quantity (Base)] = T2.[Forecast Quantity (Base)] and T1.[Document No_] = T2.[Document No_] and T1.[Equipe] = T2.[Equipe]) where T1.[Ressource] = 'M.XX' and T1.[Forecast Date] = '23/01/2010'
hors je ne sais pour quelle raison dans le resultat de ma requete apparait M.WW alors qu'il n'a pas travaillé ce jours la ! :oops:
MERCI pour votre aide

24/08/2010, 22h13
zinzineti

Peux-tu montrer le script de création de la table [Societe$Production] ?
Il me semble que tu as un problème de date.
dans le WHERE tu as

Code:

T1.[Forecast Date] = '23/01/2010'

et en affichage du résultat tu as une valeur du 24/01/2010 !???

Citation:

PR | 24/01/2010 | 1 | n1000 | M.WW | 1

Essaye ceci pour voir

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT DISTINCT
T1.[No_] AS Code,
convert(char(10),T1.[Forecast Date],103) AS Date,
cast(T1.[Forecast Quantity (Base)] AS float) AS Qtés,
T1.[Document No_] AS Affaire,
T1.[Ressource] AS Personne,
T1.[Ressource]+'  '+T2.[Ressource] AS PersoEquip,
T1.[Type] AS CodeEvenement,
T1.[Type Article] AS TypeArticle,
T1.[Equipe] AS Equipe
 
FROM dbo.[Societe$Production] T1
	INNER JOIN dbo.[Societe$Production] T2 
		ON (T1.[Document No_] = T2.[Document No_]
		AND T1.[Forecast Quantity (Base)] = T2.[Forecast Quantity (Base)]
		AND T1.[Document No_] = T2.[Document No_]
		AND T1.[Equipe] = T2.[Equipe])
WHERE T1.[Ressource] = 'M.XX'
AND convert(char(10),T1.[Forecast Date],103) = '23/01/2010'

Merci zinzineti de ta reponse , mais le resultat est le meme. Voici le script :

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
37
38
39
40
41
42
43
CREATE TABLE [dbo].[Societe$Production Chantier](
	[timestamp] [timestamp] NOT NULL,
	[Entry No_] [int] NOT NULL,
	[Document Type] [int] NOT NULL,
	[No_] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
	[Forecast Date] [datetime] NOT NULL,
	[Forecast Quantity] [decimal](38, 20) NOT NULL,
	[Unit of Measure Code] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
	[Qty_ per Unit of Measure] [decimal](38, 20) NOT NULL,
	[Forecast Quantity (Base)] [decimal](38, 20) NOT NULL,
	[Location Code] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
	[Component Forecast] [tinyint] NOT NULL,
	[Description] [varchar](70) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
	[Document No_] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
	[Type] [int] NOT NULL,
	[Ressource] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
	[Equipe] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
	[Machine] [tinyint] NOT NULL,
	[Presence] [tinyint] NOT NULL,
	[Auto] [tinyint] NOT NULL,
	[Valider] [tinyint] NOT NULL,
	[Saisie Terminée] [tinyint] NOT NULL,
	[Ligne Chantier] [int] NOT NULL,
	[Phase] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
	[Tache] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
	[Hors Heure Affectée] [tinyint] NOT NULL,
	[Agence] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
	[Région] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
	[Type Article] [int] NOT NULL,
	[Présence Réel Agence] [decimal](38, 20) NOT NULL,
	[Présence Réel Région] [decimal](38, 20) NOT NULL,
	[Présence Réel Equipe] [decimal](38, 20) NOT NULL,
	[Type DEPOT] [tinyint] NOT NULL,
	[Tranfert ressource] [int] NOT NULL,
	[Tranfert équipe] [int] NOT NULL,
	[N° séq lien compta] [int] NOT NULL,
	[Présence Réel Affaire] [decimal](38, 20) NOT NULL,
	[Passage Controle transfert] [tinyint] NOT NULL,
 CONSTRAINT [Societe$Production Chantier$0] PRIMARY KEY CLUSTERED 
(
	[Entry No_] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [Data Filegroup 1]
) ON [Data Filegroup 1]

26/08/2010, 10h18
backdraf

:kill::arf::arf:
l'erreur vient de cette codition je me suis trompé de colonne :
"T1.[Forecast Quantity (Base)]" au lieu de "T1.[Forecast Date]"
:oops: désolé merci pour votre aide :oops: