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
|
SELECT
CA.[Sell-to Customer No_] AS Client,
CA.[Item Disc_ Group] AS Famille,
Volume.volume_commande AS Volume1,
CA.chiffre_affaire AS CA1
FROM ( SELECT [BDD$Sales Invoice Line].[Sell-to Customer No_],
[BDD$Item].[Item Disc_ Group],
[BDD$Sales Invoice Line].[Shipment Date],
SUM([BDD$Sales Invoice Line].[Amount]) AS chiffre_affaire
FROM [BDD$Sales Invoice Line]
JOIN [BDD$Item]
ON [BDD$Item].[No_]=[BDD$Sales Invoice Line].[No_]
GROUP BY [BDD$Sales Invoice Line].[Sell-to Customer No_],
[BDD$Sales Invoice Line].[Shipment Date],
[BDD$Item].[Item Disc_ Group]
) AS CA
INNER JOIN
( SELECT [BDD$Sales Line].[Sell-to Customer No_],
[BDD$Item].[Item Disc_ Group],
[BDD$Sales Line].[Shipment Date],
SUM([BDD$Sales Line].[Quantity]) AS volume_commande
FROM [BDD$Sales Line]
JOIN [BDD$Item]
ON [BDD$Item].[No_]=[BDD$Sales Line].[No_]
GROUP BY [BDD$Sales Line].[Sell-to Customer No_],
[BDD$Sales Line].[Shipment Date],
[BDD$Item].[Item Disc_ Group]
) AS Volume
ON Volume.[Item Disc_ Group] = CA.[Item Disc_ Group]
AND Volume.[Sell-to Customer No_] = CA.[Sell-to Customer No_]
AND YEAR(Volume.[Shipment Date])='2009'
AND MONTH(Volume.[Shipment Date])='12'
AND YEAR(CA.[Shipment Date])='2009'
AND MONTH(CA.[Shipment Date])='12'
INNER JOIN [BDD$Customer]
ON Volume.[Sell-to Customer No_]=[BDD$Customer].[No_]
AND CA.[Sell-to Customer No_]=[BDD$Customer].[No_]
AND [BDD$Customer].[Customer Disc_ Group]='DPT1' |
Partager