Requête par client avec ventilation par mois

Version imprimable

07/01/2013, 11h09
eclisse

Requête par client avec ventilation par mois

Bonjour,

merci de prendre le temps de me lire.
Je travaille sous sql 2000, en language asp.

Je dois faire un tableau de reporting par utilisateur et par mois.
J'ai mis en place une méthode dont je ne suis pas tout à fait satisfait, tant qu'il n'y a que peu d'enregistrement ça m'a l'air à peu près fluide, dès que ça augmentent, je trouve que je perd beaucoup de temps à l'affichage... et c'est peut être normal.
Du coup, j'aimerai voir s'il n'y a pas mieux pour faire la même chose avec un peu d'optimisation.

Méthode utilisée : Je créé un tableau [dim (x,12)]. J'ouvre une première requete de sélection des clients qui remplisse la condition (cad avoir des rapports pour l'année selectionné avec les conditions requises) et je boucle jusqu'à la fin du recordset.
A chaque item trouvé, je rempli mon tableau(x,0)avec l'id du client. J'exécute une seconde requete sur le nombre de rapport du clientid group by mois et order by mois.
Je boucle ma seconde requete et envoi les résultats dans le tableau, de 1 à 12 (par mois)
A la fin des 2 boucles, mon tableau de valeurs est rempli, je n'ai plus qu'à le cracher dans une boucle for next, une valeur par cellule.

J'ai essayé de passer par un procédure stockée, pensant que je pouvais gagner en temps. Sauf que je n'ai pas trouvé comment faire...

J'ai essayé également (avec procédure ou non) de faire des sous requetes, genre :
[select nom_client,
(select count(*) from raports where annee=x et idclient=y et mois=1) M1,
(select count(*) from raports where annee=x et idclient=y et mois=2) M2,
... (jusqu'à 12)
from client]

Mais du coup, je me demande si ce n'est pas plus lent ? (en tout cas c'est plus laborieux et paraît pas très optimisée...)

Quelqu'un a t-il déjà eu le cas et quelle solution serai la meilleure ?
Merci à tout ceux qui pourront me faire avancer.
07/01/2013, 17h00
Donpi

et une bête jointure sur tes utilisateur avec un pivote table pour avoir les utilisateurs sur des lignes et les mois sur des colonnes ?
07/01/2013, 17h11
eclisse

C'est à dire ?
Je ne vois pas comment une jointure seule ma résoudre mon problème mais si tu as une info et une requete qui m'échappe et me permet de sortir un recordset contenant :

Nom Client | Moi1 | Mois2 | ... | Mois12

... je suis preneur. A force de me triturer dans tous les sens, il n'est pas impossible que je ne vois plus quelque chose de simpe ??? ;)
Merci de ta réponse.
08/01/2013, 15h32
aieeeuuuuu
Bonjour,

Le plus simple, et le mieux à mon avis et de faire une seule requete qui renvoit le nombre de rapports par utilisateur et par mois, et de parser ensuite le resulat an ASP :
Code:

1 2 3 4 5 6 7 8 9 10 SELECT Nom_Client, Mois, COUNT(*) AS NbRapports FROM Rapport GROUP BY Nom_Client, Mois WHERE ... ORDER BY Nom_Client, Mois
Ensuite vous parcourez le résultat pour remplir vos tableaux ASP (attention aux "trous" si certains clients n'ont pas de rapport pour certains mois...)
09/01/2013, 11h51
iberserk

Bonjour,

Pour éviter ces trous vous pouvez passer par une table des mois...
09/01/2013, 11h57
eclisse

Oui, j'avais d'ailleurs une ancienne version dans ce type là.

J'arrive bien à remplir mon tableau en affectant directement les valeurs des mois dans la bonne colonne, plus les autres infos qui m'intéressent dans les colonne 0 ou supérieures à 12.

Par contre effectivement, au départ de ma requete, comme je dimensionne mon tableau en fonction du recordcount, il me renvoi des clients en double (avec des count pour les autres mois quant il y en a). Du coup, ça m'oblige à remplir un tableau temporaire où je récupère mes valeurs et reparser ce tableau pour supprimer les lignes qui sont vides.
Je ne sais pas si ce double remplissage de tableau peut avoir une incidence sur le système...

A moins de trouver mieux...
Merci en tout cas
10/01/2013, 15h12
SQLpro

Faites un CROSS JOIN des Mois/Années par utilisateur.
Associez dans une sous requête jointe sur le produit cartésien le calcul du count.

A +
10/01/2013, 15h26
eclisse

Ce qui ressemblerait à quoi ??
Un p'tit exemple, merci.

exemple :

Code:

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
CREATE TABLE VENTE
(VTE_ID           INT IDENTITY NOT NULL PRIMARY KEY,
 JOUR             DATE NOT NULL,
 PRODUIT          CHAR(16) NOT NULL,
 QUANTITE         FLOAT NOT NULL DEFAULT 1 CHECK(QUANTITE > 0), 
 PRIX             DECIMAL(16, 2) NOT NULL CHECK(PRIX >=0));
GO
 
INSERT INTO VENTE VALUES
('2012-01-06', 'COMPRESSEUR', 1, 1200.00),
('2012-12-20', 'COMPRESSEUR', 1, 1200.00),
('2012-08-15', 'VALVE', 1, 1200.00),
('2012-01-12', 'COMPRESSEUR', 2, 1100.00),
('2012-12-01', 'COMPRESSEUR', 1, 1200.00),
('2012-02-01', 'COMPRESSEUR', 5, 1000.00),
('2012-06-11', 'VALVE', 1, 1200.00),
('2012-05-16', 'COMPRESSEUR', 1, 1200.00),
('2012-05-11', 'COMPRESSEUR', 1, 1200.00),
('2012-02-21', 'COMPRESSEUR', 1, 1200.00),
('2012-03-11', 'COMPRESSEUR', 1, 1200.00),
('2012-04-18', 'COMPRESSEUR', 1, 1200.00),
('2012-07-17', 'COMPRESSEUR', 2, 1200.00),
('2012-04-21', 'VALVE', 1, 1200.00),
('2012-09-02', 'COMPRESSEUR', 1, 1200.00),
('2012-12-01', 'COMPRESSEUR', 1, 1200.00),
('2012-11-01', 'COMPRESSEUR', 1, 1200.00),
('2012-10-16', 'COMPRESSEUR', 1, 1200.00),
('2012-11-11', 'COMPRESSEUR', 3, 1050.00),
('2012-12-21', 'COMPRESSEUR', 1, 1200.00),
('2012-03-11', 'COMPRESSEUR', 1, 1200.00),
('2012-01-31', 'VALVE', 1, 1200.00);
GO

-- la requête naïve :

Code:

1
2
3
4
SELECT PRODUIT, MONTH(JOUR) AS MOIS, SUM(QUANTITE * PRIX) AS CA 
FROM   VENTE
WHERE  YEAR(JOUR) = 2012
GROUP  BY  PRODUIT, MONTH(JOUR);

-- la requête complète :

Code:

1
2
3
4
5
6
7
8
9
10
SELECT P.PRODUIT, D.MOIS, 
       COALESCE(SUM(QUANTITE * PRIX), 0) AS CA
FROM   (SELECT DISTINCT MONTH(JOUR) AS MOIS
        FROM   VENTE) AS D 
       CROSS JOIN (SELECT DISTINCT PRODUIT
	               FROM VENTE) AS P
       LEFT OUTER JOIN VENTE AS V
	         ON D.MOIS = MONTH(V.JOUR)
			 AND P.PRODUIT = V.PRODUIT
GROUP  BY P.PRODUIT, D.MOIS;

A +