Discussion :

[toinou4100]

Bonjour ,

J'ai fait une requete SQL la voici :

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 Distinct Atbl_Prd_Stk_Dpt.Id_Dpt ,
 Atbl_Prd.Id_Prd,
 Atbl_Prd.Lbl_Prd,
 Atbl_Prd_Stk_Dpt.Stk_Qte,
 Atbl_Prd.Id_Unt_Stk, 
Atbl_Prd.Mnt_Rvt_F,
 Atbl_Prd.Mnt_Rvt_E As Prix,
 [Mnt_Rvt_F]*[Atbl_Prd_Stk_Dpt].[Stk_Qte] AS Val_F,
 Atbl_Prd.Mnt_Rvt_E *[Atbl_Prd_Stk_Dpt].[Stk_Qte] AS Val_E, 
Case when left(Atbl_Prd.Id_Fam,1)='E' then 'EOLIEN' when left(Atbl_Prd.Id_Fam,1)='H' then 'HYDRO' else 'AUTRES'end as Fam,
case when TypeData=0 then '' else ' (PF)'end as typ , cast( case when ISNUMERIC(Atbl_Prd.Tech_09)= 1 then Atbl_Prd.Tech_09 else 0 end as float ) as Tech,
Atbl_Prd.Id_Fam,
Atbl_Prd_Stk_Dpt.Id_Dpt,
Atbl_Prd.Id_Prd,
Ctbl_doc_stk_rch.Dtdoc
FROM (Atbl_Prd_Stk_Dpt 
INNER JOIN Atbl_Prd ON Atbl_Prd_Stk_Dpt.Id_Prd = Atbl_Prd.Id_Prd) 
INNER JOIN Atbl_Ste ON Atbl_Prd_Stk_Dpt.Id_Dpt = Atbl_Ste.Id_Ste
Left JOIN Ctbl_doc_stk_rch ON Atbl_Prd_Stk_Dpt.Id_Prd=Ctbl_doc_stk_rch.Id_prd
WHERE (((Atbl_Prd_Stk_Dpt.Stk_Qte)<>0)) 
AND Atbl_Prd.TypeData < 2
ORDER BY Atbl_Prd.Id_Fam DESC, Atbl_Prd_Stk_Dpt.Id_Dpt, Atbl_Prd.Id_Prd

Cependant le nombre de resultat retourné est enorme.Si je retire la partie en rouge le nombre de resultat redevient correct.
De plus je souhaiterais n'afficher que la date (Dtdoc) la plus recente pour un code produit (Id_prd)?

En piece jointe la copie d'ecran des resultats que j'obtient.

Merci beaucoup pour votre aide.

J'espere avoir été clair dans mon explication

[al1_24]

Un simple regroupement donc...

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
SELECT Atbl_Prd_Stk_Dpt.Id_Dpt ,
 Atbl_Prd.Id_Prd,
 Atbl_Prd.Lbl_Prd,
 Atbl_Prd_Stk_Dpt.Stk_Qte,
 Atbl_Prd.Id_Unt_Stk, 
Atbl_Prd.Mnt_Rvt_F,
 Atbl_Prd.Mnt_Rvt_E As Prix,
 [Mnt_Rvt_F]*[Atbl_Prd_Stk_Dpt].[Stk_Qte] AS Val_F,
 Atbl_Prd.Mnt_Rvt_E *[Atbl_Prd_Stk_Dpt].[Stk_Qte] AS Val_E, 
Case when left(Atbl_Prd.Id_Fam,1)='E' then 'EOLIEN' when left(Atbl_Prd.Id_Fam,1)='H' then 'HYDRO' else 'AUTRES'end as Fam,
case when TypeData=0 then '' else ' (PF)'end as typ , cast( case when ISNUMERIC(Atbl_Prd.Tech_09)= 1 then Atbl_Prd.Tech_09 else 0 end as float ) as Tech,
Atbl_Prd.Id_Fam,
Atbl_Prd_Stk_Dpt.Id_Dpt,
Atbl_Prd.Id_Prd,
MAX(Ctbl_doc_stk_rch.Dtdoc)
FROM (Atbl_Prd_Stk_Dpt 
INNER JOIN Atbl_Prd ON Atbl_Prd_Stk_Dpt.Id_Prd = Atbl_Prd.Id_Prd) 
INNER JOIN Atbl_Ste ON Atbl_Prd_Stk_Dpt.Id_Dpt = Atbl_Ste.Id_Ste
Left JOIN Ctbl_doc_stk_rch ON Atbl_Prd_Stk_Dpt.Id_Prd=Ctbl_doc_stk_rch.Id_prd
WHERE (((Atbl_Prd_Stk_Dpt.Stk_Qte)<>0)) 
AND Atbl_Prd.TypeData < 2
GROUP BY Atbl_Prd_Stk_Dpt.Id_Dpt ,
 Atbl_Prd.Id_Prd,
 Atbl_Prd.Lbl_Prd,
 Atbl_Prd_Stk_Dpt.Stk_Qte,
 Atbl_Prd.Id_Unt_Stk, 
Atbl_Prd.Mnt_Rvt_F,
 Atbl_Prd.Mnt_Rvt_E As Prix,
 [Mnt_Rvt_F]*[Atbl_Prd_Stk_Dpt].[Stk_Qte] AS Val_F,
 Atbl_Prd.Mnt_Rvt_E *[Atbl_Prd_Stk_Dpt].[Stk_Qte] AS Val_E, 
Case when left(Atbl_Prd.Id_Fam,1)='E' then 'EOLIEN' when left(Atbl_Prd.Id_Fam,1)='H' then 'HYDRO' else 'AUTRES'end as Fam,
case when TypeData=0 then '' else ' (PF)'end as typ , cast( case when ISNUMERIC(Atbl_Prd.Tech_09)= 1 then Atbl_Prd.Tech_09 else 0 end as float ) as Tech,
Atbl_Prd.Id_Fam,
Atbl_Prd_Stk_Dpt.Id_Dpt,
Atbl_Prd.Id_Prd
ORDER BY Atbl_Prd.Id_Fam DESC, Atbl_Prd_Stk_Dpt.Id_Dpt, Atbl_Prd.Id_Prd