Requete de jointure

[Dali_Piero]

Bonjour à tous,

Je suis novice dans ce forum et je voudrais une solution pour mon problème suivant:

Je suis entrain de créer une requête (sous SQL 2008) qui me permet d'avoir mon chiffre d'affaire (CA) par Année/Mois et par article
Pour faire cette requête j'ai fait une jointure (gauche) entre ma table article et ma table Facture:

Code :

1
2
3
4
5
6
7
8
SELECT Year(Date) AS Annee , 
 Month(Date) AS Mois,
 Reference AS Code article,
 Designation AS Designation article, 
 SUM (MontantTTC) AS CA 
FROM F_ARTICLE 
LEFT JOIN F_FACTURE 
 ON F_Article.Reference=F_Facture.Reference

Le résultat:
Année Mois Code article Designation Article CA
2011 04 99999 Article Test 1000
2011 04 99999 Article Test 2000

Le problème est que lorsque l'article n'a pas de CA par mois, la requête n'affiche rien alors je souhaite avoir ce résultat
Année Mois Code article Designation Article CA
2011 03 99999 Article Test 0
2011 04 99999 Article Test 1000
2011 04 99999 Article Test 2000


Merci à l'avance pour votre collaboration

[Blacknounou]

Envoyé par Dali_Piero

Select Year(Date) as Annee , Month(Date) as Mois,Reference as Code article,Designation as Designation article, SUM (MontantTTC) as CA From F_ARTICLE LEFT JOIN F_FACTURE ON F_Article.Reference=F_Facture.Reference

Le problème est que lorsque l'article n'a pas de CA par mois, la requête n'affiche rien alors je souhaite avoir ce résultat
Année Mois Code article Designation Article CA
2011 03 99999 Article Test 0
2011 04 99999 Article Test 1000
2011 04 99999 Article Test 2000

Bonjour,

Si vous remplacez

Code :

SUM (MontantTTC) AS CA

par :

Code :

1
2
 
ISNULL(SUM (MontantTTC),0) AS CA

Ca pourrait fonctionner.

[aieeeuuuuu]

Bonjour


Et pour le GROUP BY... c'était trop cher ou ils étaient en rupture de stock ?

Donnez nous votre requête complète !

Avec une jointure externe telle que vous l'avez faite(mais avec le group by ), vous devriez obtenir le résultat voulu !

[Dali_Piero]

Bonsoir à tous,

Merci pour votre réponse mais je pense que je suis obligé de vous transmettre ma requête complète

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
  SELECT TOP (100) PERCENT YEAR(dbo.F_FACTURE.DO_Date) AS Year
       , MONTH(dbo.F_FACTURE.DO_Date) AS Month
       , dbo.F_ARTICLE.AR_Ref AS [Article Code]
       , dbo.F_ARTICLE.AR_Design AS [Article Descriptions]
       , ISNULL(SUM(dbo.F_FACTURE.DL_MontantTTC), 0) AS [Net Sales TND]
       , ISNULL(SUM(dbo.F_FACTURE.DL_Qte),0) AS [Qte Vendu]
       , dbo.F_ARTICLE.AR_PoidsNet AS [Poids Kg]
    FROM dbo.F_ARTICLE
         LEFT OUTER JOIN dbo.F_FACTURE
           ON dbo.F_ARTICLE.AR_Ref = dbo.F_FACTURE.AR_Ref
GROUP BY dbo.F_ARTICLE.AR_Ref
       , dbo.F_ARTICLE.AR_Design
       , MONTH(dbo.F_FACTURE.DO_Date)
       , dbo.F_ARTICLE.AR_PoidsNet
       , YEAR(dbo.F_FACTURE.DO_Date)

[aieeeuuuuu]

Je ne comprend pas trop votre besoin

Dans votre exemple d'origine, je ne comprend pas pourquoi les deux dernières lignes ne sont pas groupées, puisqu'il s'agit du même produit, le même mois, même année...

Si votre problème est de faire ressortir tous les produits tous les mois (meme les ou il n'y a pas eu de vente pour tel ou tel produit), alors le mieux est de passer par des tables de calendrier, et faire un produit cartesien avec la table des produits, puis votre jointure externe avec la table des factures.

sinon, en alternative, vous pouvez générer une pseudo-table des mois avec une CTE recursive.

[Dali_Piero]

Bonsoir,
Mon besoin est simple, je n'arrive pas à avoir le résultat souhaité à partir de ma requête qui me parait logique et correcte! (avoir toutes les CA des articles mensuellement même ceux avec des valeurs Null)

@aieeeuuuuu: je ne vois pas pourquoi passer par une table intermédiaire alors que normalement la logique de la requete est correcte?

Cordialement

[Waldar]

Envoyé par Dali_Piero

@aieeeuuuuu: je ne vois pas pourquoi passer par une table intermédiaire alors que normalement la logique de la requete est correcte ?

La logique de la requête est correcte... parmi les données existantes.
Là vous devez créer une ligne qui n'existe pas, et la solution que vous propose aieeeuuuuu prend alors tout son sens.

[elsuket]

Bonjour,

J'aurai plutôt écrit :

Code :

1
2
       , SUM(ISNULL(dbo.F_FACTURE.DL_MontantTTC, 0) AS [Net Sales TND]
       , SUM(ISNULL(dbo.F_FACTURE.DL_Qte,0) AS [Qte Vendu]

@++

[Dali_Piero]

Bonjour à tous,

Je voudrais tous d'abord vous remerciez pour l’intérêt accordé à mon problème:-)

@elsuket: j'ai essayé d'inverser la fonction ISNULL comme vous me l'avez proposé et je n'ai rien obtenu

@Waldar & aieeeuuuuu: Merci de bien vouloir éclaircir un peu votre idée et me proposer un exemple de code pour mieux comprendre.

Toutes autres idées ou proposition(plus simple) seront les bienvenues.

Cordialement

[aieeeuuuuu]

Ok !

Vous faites une jointure externe entre la table produit et la table commande.
Grâce à cette jointure externe, un produit qui n'a jamais été vendu apparaitra quand même dans le résultat. Vous aurez donc toutes les colonnes issues de la table produit renseignées, et toutes les colonnes issues de la table facturation à NULL. mais vous aurez une ligne par produit, et vous voulez une ligne par produit et par mois. Il faut donc ajouter les mois dans votre condition de jointure. Il vous faut donc bien cette "liste des mois".

Je vois donc trois solutions :
1/Un modèle de calendrier (comme celui de SQLPro par exemple)
2/ générer cette liste à la volée avec une CTE récursive
3/ récupérer cette liste depuis la table des factures

La troisième solution, tentante présente cependant quelques défauts :
1/ si pour un mois donné, il n'y a eu aucune commande d'aucun produit, ce mois n'apparaitra pas.
2/ vous allez traiter une énorme quantité de données inutilement.

La solution avec table de calendrier et sans doute la meilleure, surtout si vous étés amené a effectuer régulièrement ce genre de requêtes : c'est sans doute celle qui offrira les meilleures performances, les données étant déjà dans la base, avec les indexes (et statistiques) nécessaires pour l'optimisation.
Cette solution est cependant plus lourde à mettre en place.

Si vous n'avez as la possibilité d'implémenter cette solution, il vous reste donc la deuxième solution, en générant vous même à la volée la précieuse liste des mois.

Ci dessous, un exemple, que je vous laisse adapter à votre besoin (vous ne nous avez pas donné la structure de vos tables...)

Code SQL :

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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
DECLARE @produit TABLE(
	IDProduit INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	Nom VARCHAR(50)
)
DECLARE @cmd TABLE (
	IDCmd INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	IDProduit INT NOT NULL,
	DateHeure DATETIME NOT NULL,
	Montant INT NOT NULL
)
 
--insertion du jeu de données
INSERT INTO @produit(Nom)
VALUES 
	('Produit A'),
	('Produit B')
 
INSERT INTO @cmd(IDProduit, DateHeure, Montant)
VALUES
	(1,'2011-02-12', 1),
	(1,'2011-02-17', 2),
	(2,'2011-02-13', 4),
	(2,'2011-03-18', 8),
	(1,'2011-04-03', 16),
	(1,'2011-04-05', 32),
	(2,'2011-04-21', 64)
 
-- requete telle que vous la faites : le produit A ne ressort pas pour le mois de mars	
SELECT 
	YEAR(DateHeure),
	MONTH(DateHeure),
	Nom,
	SUM(COALESCE(Montant, 0)) AS MontanTotal
FROM @Produit P
LEFT OUTER JOIN @Cmd C
	ON P.IDProduit = C.IDproduit
GROUP BY YEAR(DateHeure),
	MONTH(DateHeure),
	Nom
ORDER BY 1,2,3
 
 
--requete avec une CTE recursive fournissant la liste des 12 derniers mois (borneInf = début du mois, BornSup = début du mois suivant)	
;WITH Mois (BorneInf, BorneSup) AS(
	SELECT DATEADD(
		MONTH,
		DATEDIFF(
			MONTH,
			0,
			GETDATE()
		),
		0
	), --debut du mois en cours
	DATEADD(
		MONTH,
		DATEDIFF(
			MONTH,
			0,
			GETDATE()
		) + 1,
		0
	) --debut du mois prochain
	UNION ALL
	SELECT 
		DATEADD(MONTH, -1, BorneInf),
		DATEADD(MONTH, -1, BorneSup)
	FROM Mois 
	WHERE BorneInf > DATEADD(YEAR, -1, GETDATE()) --on remonte mois par mois jusqu'a il y a un an
)
SELECT 
	M.BorneInf,
	Nom,
	SUM(COALESCE(Montant, 0)) AS MontantTotal
FROM @Produit P
CROSS JOIN Mois M --produit cartesien : chaque produit, chaque mois
LEFT OUTER JOIN @Cmd C --on cherche les commandes qui correspondent
	ON P.IDProduit = C.IDproduit
	AND C.DateHeure >= M.BorneInf
	AND C.DateHeure < M.BorneSup
GROUP BY M.BorneInf, Nom

n'hésitez pas si quelque chose n'est toujours pas clair

[SQLpro]

Envoyé par aieeeuuuuu

Je vois donc trois solutions :
1/Un modèle de calendrier
...
La solution avec table de calendrier et sans doute la meilleure
...
Si vous n'avez as la possibilité d'implémenter cette solution

Cite moi un cas ou il n'aurait pas la possibilité de faire cette solution ???

A +

[aieeeuuuuu]

Envoyé par SQLpro

Cite moi un cas ou il n'aurait pas la possibilité de faire cette solution ???

A +

heu... ce cas là par exemple :

CREATE TABLE permission denied in database 'xxx'.

[SQLpro]

Je m'y attendais, d'où le piège tendu !!!

NON, car il peut implémenter son calendrier dans une autre base et faire des requêtes interbase....

C'est même la meilleure solution, car la base calendrier peut être mise en READ ONLY !!!!

A +

[aieeeuuuuu]

Envoyé par SQLpro

Je m'y attendais, d'où le piège tendu !!!

NON, car il peut implémenter son calendrier dans une autre base et faire des requêtes interbase....

Arfff...
heu.. ce cas là alors :

CREATE DATABASE permission denied in database 'xxx'

C'est même la meilleure solution, car la base calendrier peut être mise en READ ONLY !!!!
A +

En effet !

[Zabriskir]

NON, car il peut implémenter son calendrier dans une autre base et faire des requêtes interbase....

Bonjour,

Je rebondis là-dessus pour demander comment vous gérer les accès pour autoriser les requêtes interbases.

Zabriskir

[SQLpro]

par le chainage des propriétaires
sp_configure 'cross db ownership chaining'
ou
alter database DB_CHAINING { ON | OFF }

A +

[Zabriskir]

par le chainage des propriétaires
sp_configure 'cross db ownership chaining'
ou
alter database DB_CHAINING { ON | OFF }

Merci pour votre réponse.
Quelle est la bonne pratique :
- donner accès à la base cible/commune à tous les utilisateurs qui ont besoin d'accéder à la vue calendrier ?
- se servir de l'utilisateur guest (comme pour tempdb ou master) ?

Zabriskir