Transact SQL - requête

Version imprimable

26/06/2009, 16h34
Therwy

Transact SQL - requête

Bonjour,

J'ai plusieurs colonnes A, B, C, D, E
La colonne A correspond au code produit et la colonne E au chiffre d'affaire.

Je souhaiterais connaître les produits qui génèrent à eux seuls 30% du chiffre d'affaire.

En d'autres termes je dois faire le total de la colonne E, la diviser par 3.
Mais ensuite ne doivent rester que les lignes produits dont le total ne dépasse pas le total de la colonne E divisé par 3.

Est-ce quelqu'un aurait une solution ?

J'ai déjà essayé avec la clause HAVING ou des requêtes multiples mais je ne tombe jamais sur le résultat voulu

Merci pour votre aide

Bonsoir,

Vous pouvez essayer ceci

Code:

1
2
3
4
5
6
7
8
9
SELECT A, SUM(E) FROM T 
GROUP BY A
HAVING SUM(E) > (SELECT SUM(E) * 30 / 100 FROM T)
 
--ou bien avec une cte
;WITH CT (A, E) AS 
(SELECT A, SUM(E) FROM T 
GROUP BY A) 
SELECT * FROM CT WHERE E > (SELECT SUM(E) * 30 / 100 FROM CT)

26/06/2009, 20h59
Therwy

Bonsoir,

merci pour votre réponse.
En fait j'avais essayé la 1ère solution mais il ne me donne en réponse aucune ligne.
j'ai essayé la 2ème que vous m'avez soumise mais idem, il ne me donne aucune ligne.
Si je vais un SELECT sans clauses sur la vue il me donne 11'000 lignes.
Je pense que le problème vient de la clause HAVING.

Je cherche encore...

Merci

Bonjour,

Voyons ce que donnent une de ces deux requêtes :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH
	CTE_CHIFFRE_AFFAIRE (TiersChiffreAffaire) AS
	(
		SELECT SUM(E) * 0.3 AS TiersChiffreAffaire
		FROM dbo.maTable
	)
SELECT A, B, C, D, E
FROM dbo.maTable AS T
JOIN CTE_CHIFFRE_AFFAIRE AS C
	ON T.E >= C.TiersChiffreAffaire
-------------------------------------------------------
SELECT A, B, C, D, E
FROM dbo.maTable AS T
JOIN (
		SELECT SUM(E) * 0.3 AS TiersChiffreAffaire
		FROM dbo.maTable
	) AS CHIFFRE_AFFAIRE (TiersChiffreAffaire)
ON T.E >= CHIFFRE_AFFAIRE.TiersChiffreAffaire

@++ ;)

26/06/2009, 22h02
Therwy
Bonsoir,

merci pour votre réponse.
Même résultat, dans les 2 cas il ne me donne aucunes lignes.

vous indiquez en clause

Code:

T.E >= C.TiersChiffreAffaire

Or avec cette clause il ne me donne aucune ligne si par contre je fais

Code:

T.E <= C.TiersChiffreAffaire

Là il me donne toutes les lignes à savoir les 11'000 lignes.

Dans votre cas on demande de faire la somme de E * 30 / 100 et ensuite de sortir chaque ligne ne dépassant pas ce résultat.
Or c'est normal qu'aucune ligne ne dépasse ce résultat car il est trop grand.

voici ce que je cherche
Code:

1 2 3 4 5 6 7 8 9 10 Colonne A Colonne E ProdA 100 ProdB 30 ProdC 50 ProdD 20 ProdE 20 ProdF 20 ProdG 20 ProdH 40
la somme de la colonne E est 300, pour faire simple si je veux connaitre les produits qui a eux seuls font la moitié du CA soit 150,
il ne devrait me retourner que les lignes ProdA et prodC qui a eux deux font 150. Soit les 2 produits qui rapportent le plus.

Merci pour votre aide
26/06/2009, 22h30
Waldar

Attention à l'énoncé c'est important.

Vous voulez connaître les produits qui font au moins 30% du CA
OU
les combinaisons de produits qui font au moins 30% du CA.

Le premier est très simple, le second très très complexe.
26/06/2009, 22h43
Therwy

Bonsoir,

je ne comprends pas bien votre question, je ne pense pas que ce soit des combinaisons.
Si je reprend le tableau que j'ai fait un peu plus haut, il faut faire

Code:

SELECT A, E FROM T ORDER BY E DESC

Là il va me sortir tous les articles du plus grand au plus petit soit le
prodA et le ProdC dans les 2 première position.

Le total de la colonne E fait 300. Si je veux les articles qui font 50% du CA.
Il devrait additionner chaque ligne et s'arrêter dès que le total des produits est égal à 150. Dans ce cas, ne sortir que les 2 premières lignes.

Je ne sais pas s'il existe une requête simple ou s'il faut passer par du multi-requête. Je continue de chercher.

Merci pour votre aide

Bonsoir,

Merci, c'est plus clair. Effectivement, l'énoncé est important :-)
Donc sans combinaison. En reprenant votre dernier exemple, est ce que ceci donne le résultat attendu ?
Code:

1 2 3 4 5 6 ;WITH T_RANG (A, E, RANG) AS (SELECT A, E, ROW_NUMBER() OVER (ORDER BY E DESC) FROM T) SELECT A, E FROM T_RANG WHERE (SELECT SUM(E) FROM T_RANG T1 WHERE T1.RANG <= T_RANG.Rang) <= (SELECT SUM(E) * 50 / 100 FROM T)
@+

27/06/2009, 10h35
Therwy

Bonjour,

merci pour votre réponse.
J'ai essayé mais j'ai laissé tombé car au bout de 8mn je n'ai toujours pas eu de résultat. La requête est trop lourde.

Merci
27/06/2009, 11h21
agemis31
Bonjour,

Que ce soit lent, ça c'est sûr, on doit difficilement faire plus lent :lol:
J'ai essayé d'écrire une seule requête, pour le principe:
- pas de clef primaire -> ROW_NUMBER()
- somme totale
- somme partielle
Il me semble que la problématique de "running totals" est plutôt du domaine du reporting. Voici deux liens qui peuvent vous intéresser:
running totals sur sqlteam
Using “Update to a local variable” to calculate running totals in SQL.

@+
27/06/2009, 17h57
Therwy

Je vais regarder tout ça de plus près ou alors je vais faire des extractions et programmer en VB dans excel pour faire ça.

merci encore pour votre aide.
Bon week-end
27/06/2009, 18h59
agemis31

Bonjour,

Tenez nous au courant du résultat :-)
Je viens de tomber sur une approche .net qui me semble intéressante tant que SQL serveur ne pourra pas effectuer des sommes sur une fenêtre de résultat (SUM() OVER...) . Le problème étant foncièrement procédural, l'auteur utilise .net pour conserver ou ajouter une valeur dans une colonne.
SQL. Running Totals.

@+