Discussion :

[Pasiphae]

Bonjour tout le monde.

Voila je travaille avec une base de données sql serveur.
Je dois faire des requetes pour des statistiques (sur les ventes) et je me heurte a un probleme.

J ai une table ligne_facture avec la qté d articles vendus par ligne, ainsi que l identifiant du produit en question sur chaque ligne. Dans ma table facture, j ai plein de choses et une date de facturation.

Je dois remonter le nombre d articles vendus sur une periode entre 2 dates, groupés par articles, jusqu ici tout va bien. Mais je dois rajouter exactement la meme requete sur une periode identique sur l annee precedente (n-1) et la je vois pas du tout comment avoir tout ca sur la meme ligne.

Article1 4 vendus en 2005, 8 en 2006 ...

Si vous pourriez me donner une piste, cela m aiderait beaucoup.
Par avance merci.

[Nadine]

est-ce qu'on peut avoir une idée de la structure de tes tables?

[Pasiphae]

Oui, bien sur. Merci.
Mais je n utilise pas tous les champs pour ma requete.

CREATE TABLE [dbo].[Facture] (
[FactureID] [bigint] IDENTITY (1, 1) NOT NULL ,
[TiersID] [int] NOT NULL ,
[DateFact] [datetime] NULL ,
[DateLivraison] [datetime] NULL ,
[Devise] [nvarchar] (5) COLLATE French_CI_AS NULL ,
[NomTiers] [nvarchar] (100) COLLATE French_CI_AS NULL ,
[AdresseTiers] [nvarchar] (100) COLLATE French_CI_AS NULL ,
[CPTiers] [nvarchar] (30) COLLATE French_CI_AS NULL ,
[VilleTiers] [nvarchar] (100) COLLATE French_CI_AS NULL ,
[PaysTiers] [nvarchar] (100) COLLATE French_CI_AS NULL ,
[NomPayeur] [nvarchar] (100) COLLATE French_CI_AS NULL ,
[AdressePayeur] [nvarchar] (100) COLLATE French_CI_AS NULL ,
[CPPayeur] [nvarchar] (20) COLLATE French_CI_AS NULL ,
[VillePayeur] [nvarchar] (100) COLLATE French_CI_AS NULL ,
[PaysPayeur] [nvarchar] (100) COLLATE French_CI_AS NULL ,
[RemiseGlobale] [int] NULL ,
[MontantTTC] [money] NULL ,
[MontantTVA] [money] NULL ,
[MontantHT] [money] NULL ,
[NetAPayer] [money] NULL ,
[Escompte] [money] NULL ,
[Accompte] [money] NULL ,
[FraisPort] [money] NULL ,
[DateEcheance] [datetime] NULL ,
[ConditionRegl] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[EstReglee] [tinyint] NULL ,
[Notes] [nvarchar] (150) COLLATE French_CI_AS NULL ,
[RefFact] [nvarchar] (20) COLLATE French_CI_AS NULL ,
[RefCmde] [nvarchar] (20) COLLATE French_CI_AS NULL ,
[RefLivr] [nvarchar] (20) COLLATE French_CI_AS NULL ,
[Enlevement] [bit] NULL ,
[Lu] [bit] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Ligne_Facture] (
[LigneFactureID] [bigint] IDENTITY (1, 1) NOT NULL ,
[FactureID] [bigint] NULL ,
[ProduitID] [bigint] NULL ,
[PrixUnitaire] [money] NULL ,
[QteTotaleLivree] [int] NULL ,
[LibelleProd] [nvarchar] (100) COLLATE French_CI_AS NULL ,
[GencodeProd] [nvarchar] (15) COLLATE French_CI_AS NULL ,
[TauxTVA] [float] NULL ,
[TotalHT] [money] NULL ,
[TotalTTC] [money] NULL ,
[Remise] [int] NULL ,
[Commentaire] [nvarchar] (300) COLLATE French_CI_AS NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[Facture] WITH NOCHECK ADD
CONSTRAINT [PK_Facture] PRIMARY KEY CLUSTERED
(
[FactureID]
) ON [PRIMARY]

ALTER TABLE [dbo].[Ligne_Facture] WITH NOCHECK ADD
CONSTRAINT [PK_Ligne_Facture] PRIMARY KEY CLUSTERED
(
[LigneFactureID]
) ON [PRIMARY]


Actuellement j ai essayé avec une procedure stockee :

CREATE PROCEDURE GetStatsProd
(
@DateFirst datetime,
@DateLast datetime
)
AS

SELECT sum(QteTotaleLivree) as QteTotale, sum(TotalHT) as TotalHT, ProduitID
FROM Ligne_Facture

INNER JOIN
Facture ON Ligne_Facture.FactureID = Facture.FactureID

WHERE Facture.DateLivraison >= @DateFirst AND Facture.DateLivraison <= @DateLast

Group by ProduitID




Mon souci c est qu il me faudrait la meme chose sur la meme ligne (meme jeu de resultats) pour DateFirst - 1annee et DateLast -1annee.

Je vois pas comment faire en une requete. C est peut etre pas possible aussi...

[SQLpro]

Quelquechose comme :

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
SELECT LF.ProduitID,
       sum(LF.QteTotaleLivree) as QteTotale, 
       sum(LF.TotalHT) as TotalHT, 
       sum(LFOLD.QteTotaleLivree) as QteTotale_LAST_YEAR, 
       sum(LFOLD.TotalHT) as TotalHT_LAST_YEAR, 
 
FROM   Ligne_Facture LF
       INNER JOIN Facture F
             ON LF.FactureID = F.FactureID
       LEFT OUTER JOIN Ligne_Facture LFOLD
             ON LF.ProduitID = LFOLF.ProduitID
       LEFT OUTER JOIN Facture FOLD
             ON LFOLD.FactureID = FOLD.FactureID
 
WHERE  F.DateLivraison BETWEEN @DateFirst AND @DateLast 
  AND  FOLD.DateLivraison BETWEEN DATEADD(year, -1, @DateFirst) AND DATEADD(year, -1, @DateLast)
 
Group  by ProduitID

Pas testé !

A +

[Pasiphae]

Merci beaucoup pour votre aide.

Votre requete fonctionne beaucoup mieux que la meme c est certain. Par contre, lorsque je n ai pas de ventes l annee precedente ou si j en ai l annee precedente et pas l annee en cours, ca ne me renvoie rien.

Ca devrait me renvoyer qqch du genre
PRODUIT , Qte 2006, HT2006 , Qte 2005 (n-1), HT 2005 (n-1)
1, 3, 12, 0, 0
2, 5, 15, 2, 15

Votre requete ne me retourne que la derniere ligne.
Si vous pouvriez m aider encore un petit peu, s il vous plait... Je ne voudrais cependant pas abuser...

[edité pour correction des donnees attendues]

[Médiat]

Essaye :

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
SELECT LF.ProduitID,
       sum(LF.QteTotaleLivree) as QteTotale, 
       sum(LF.TotalHT) as TotalHT, 
       sum(LFOLD.QteTotaleLivree) as QteTotale_LAST_YEAR, 
       sum(LFOLD.TotalHT) as TotalHT_LAST_YEAR, 
 
FROM   Ligne_Facture LF
       INNER JOIN Facture F
             ON LF.FactureID = F.FactureID
       LEFT OUTER JOIN Ligne_Facture LFOLD
             ON LF.ProduitID = LFOLF.ProduitID
       LEFT OUTER JOIN Facture FOLD
             ON LFOLD.FactureID = FOLD.FactureID
             AND FOLD.DateLivraison BETWEEN DATEADD(year, -1, @DateFirst) AND DATEADD(year, -1, @DateLast)
WHERE  F.DateLivraison BETWEEN @DateFirst AND @DateLast 
  AND  
 
Group  by ProduitID

[Pasiphae]

J ai tenté ça, mais ca me retourne des resultats bizarres (j ai mis des valeurs de date pour les tests, je modifierais en procedure +tard ^^).

SELECT LF.ProduitID,
sum(LF.QteTotaleLivree) as QteTotale,
sum(LF.TotalHT) as TotalHT,
sum(LFOLD.QteTotaleLivree) as QteTotale_LAST_YEAR,
sum(LFOLD.TotalHT) as TotalHT_LAST_YEAR

FROM Ligne_Facture LF
INNER JOIN Facture F
ON LF.FactureID = F.FactureID

LEFT OUTER JOIN Ligne_Facture LFOLD
ON LF.ProduitID = LFOLD.ProduitID and F.DateLivraison BETWEEN '01/01/2006' AND '05/05/2006'

LEFT OUTER JOIN Facture FOLD
ON LFOLD.FactureID = FOLD.FactureID and FOLD.DateLivraison BETWEEN DATEADD(year, -1, '01/01/2006') AND DATEADD(year, -1, '05/05/2006')


Et la j ai ca comme resultat :
ProduitId, qte annee,ht annee, qte annee passee, ht annee passee
2,2, 15, null, null
1, 6, 24, 6, 24
2, 10, 30, 7, 30

Lorsque j utilise votre requete, je n ai pas la premiere ligne (ce qui semble mieux)...

les valeurs de calcul (sum) semblent multipliées par 2 ^^
je devrais avoir :
1, 3, 12, 0, 0
2, 5, 15, 2, 15


je viens d aller voir cette page qui m a aiguillee un peu http://sql.developpez.com/sqlaz/jointures/
mais je vois toujours pas, je craque.

[Médiat]

Publie les inserts qui permettront de faire des tests...

[Pasiphae]

Merci beaucoup pour votre aide, j avoue desesperer un peu.
Je pense que ca doit etre cela qui est attendu (je n ai mis que les champs necessaires) :

INSERT INTO [dbo].[Facture]([FactureID], [TiersID], [DateFact])
VALUES(9, 1, '2006-04-05')
INSERT INTO [dbo].[Facture]([FactureID], [TiersID], [DateFact])
VALUES(10, 1, '2006-04-01')
INSERT INTO [dbo].[Facture]([FactureID], [TiersID], [DateFact])
VALUES(11, 2, '2005-04-01')

INSERT INTO .[dbo].[Ligne_Facture]([LigneFactureID], [FactureID], [ProduitID],[QteTotaleLivree], [TotalHT])
VALUES(13, 9, 1, 1, 10.00)
INSERT INTO .[dbo].[Ligne_Facture]([LigneFactureID], [FactureID], [ProduitID],[QteTotaleLivree], [TotalHT])
VALUES(14, 9, 2, 5, 15.00)
INSERT INTO .[dbo].[Ligne_Facture]([LigneFactureID], [FactureID], [ProduitID],[QteTotaleLivree], [TotalHT])
VALUES(15, 10, 1, 2, 2.00)
INSERT INTO .[dbo].[Ligne_Facture]([LigneFactureID], [FactureID], [ProduitID],[QteTotaleLivree], [TotalHT])
VALUES(16, 11, 2, 2, 15.00)

[Médiat]

Essaye :

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
SELECT NEW.ProduitID,
       QteTotale,
       TotalHT,
       QteTotale_LAST_YEAR,
       TotalHT_LAST_YEAR
FROM   (SELECT ProduitID,
        sum(LF.QteTotaleLivree) as QteTotale,
        sum(LF.TotalHT) as TotalHT
        FROM Facture F INNER JOIN Ligne_FACTURE LF
                              ON LF.FactureID = F.FactureID
        WHERE F.DateFact BETWEEN '2006-04-01' AND '2006-04-30'
        GROUP BY ProduitID) NEW
     LEFT OUTER JOIN
       (SELECT ProduitID,
               sum(LFOLD.QteTotaleLivree) as QteTotale_LAST_YEAR,
               sum(LFOLD.TotalHT) as TotalHT_LAST_YEAR
        FROM Facture FOLD INNER JOIN Ligne_FACTURE LFOLD
                                ON LFOLD.FactureID = FOLD.FactureID
        WHERE FOLD.DateFact BETWEEN DATEADD(year, -1, '2006-04-01') AND DATEADD(year, -1, '2006-04-30')
        GROUP BY ProduitID) OLD
     ON NEW.ProduitID = OLD.ProduitID

[Pasiphae]

Top! Genial ! Merci !
Ca fonctionne deja correctement dans un sens.

Par contre quand le produit a ete commandé en 2005 et pas en 2006, la ligne n apparait pas dans le jeu de resultat de la requete. J ai fais le test en rajoutant cette ligne dans Ligne_Facture :

INSERT INTO .[dbo].[Ligne_Facture]([LigneFactureID], [FactureID], [ProduitID],[QteTotaleLivree], [TotalHT])
VALUES(17, 11, 5, 1, 1.05)

Je devrais avoir une ligne de plus :
5 ; 1 ; Null ; Null; 1; 1.05

Je vais donc me pencher sur la question, je ne voudrais pas abuser de votre gentillesse a tous...

Par contre j avoue que je n ai pas bien compris la requete : pourquoi rajouter un "ON NEW.ProduitID = OLD.ProduitID" ?
Si vous avez le temps de m expliquer et de me donner une autre piste, je vous en serais tres reconnaissante.

[Pasiphae]

J ai tenté ceci (chez moi les dates ne sont pas au meme au format ) :

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 NEW.ProduitID,
QteTotale,
TotalHT,
QteTotale_LAST_YEAR,
TotalHT_LAST_YEAR
 
FROM (SELECT ProduitID,
sum(LF.QteTotaleLivree) as QteTotale,
sum(LF.TotalHT) as TotalHT
FROM Facture F right outer JOIN Ligne_FACTURE LF
ON LF.FactureID = F.FactureID
and F.DateFact BETWEEN '01/01/2006' AND '05/05/2006' 
GROUP BY ProduitID) NEW
LEFT OUTER JOIN
(SELECT ProduitID,
sum(LFOLD.QteTotaleLivree) as QteTotale_LAST_YEAR,
sum(LFOLD.TotalHT) as TotalHT_LAST_YEAR
FROM Facture FOLD INNER JOIN Ligne_FACTURE LFOLD
ON LFOLD.FactureID = FOLD.FactureID
WHERE FOLD.DateFact BETWEEN DATEADD(year, -1, '01/01/2006') AND DATEADD(year, -1, '05/05/2006')
GROUP BY ProduitID) OLD
ON NEW.ProduitID = OLD.ProduitID

J obtiens :

produitid; qte totale; totalht; qtetotale_last_year; totalht_last_year
1 ; 3; 12; null ; null
2 ; 7; 30; 2; 15
5; 1 ; 1.00; 1 ; 1.00

je devrais avoir :
produitid; qte totale; totalht; qtetotale_last_year; totalht_last_year
1, 3, 12, null; null
2, 5, 15, 2, 15
5;null; null; 1; 1.00

Yah vraiment des jours ou je hais les stats...

[Médiat]

Bonjour Pasiphae,

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
SELECT coalesce(NEW.ProduitID,OLD.ProduitID) as ProduitID,
       QteTotale,
       TotalHT,
       QteTotale_LAST_YEAR,
       TotalHT_LAST_YEAR
FROM   (SELECT ProduitID,
        sum(LF.QteTotaleLivree) as QteTotale,
        sum(LF.TotalHT) as TotalHT
        FROM Facture F INNER JOIN Ligne_FACTURE LF
                              ON LF.FactureID = F.FactureID
        WHERE F.DateFact BETWEEN '2006-04-01' AND '2006-04-30'
        GROUP BY ProduitID) NEW
     FULL OUTER JOIN
       (SELECT ProduitID,
               sum(LFOLD.QteTotaleLivree) as QteTotale_LAST_YEAR,
               sum(LFOLD.TotalHT) as TotalHT_LAST_YEAR
        FROM Facture FOLD INNER JOIN Ligne_FACTURE LFOLD
                                ON LFOLD.FactureID = FOLD.FactureID
        WHERE FOLD.DateFact BETWEEN DATEADD(year, -1, '2006-04-01') AND DATEADD(year, -1, '2006-04-30')
        GROUP BY ProduitID) OLD
     ON NEW.ProduitID = OLD.ProduitID

[Pasiphae]

Je crois que merci ne sera jamais assez fort comme mot, mais quand meme MERCI du fond du coeur !