Discussion :

[ritchi46]

Bonjour à tous

J'ai une table qui contient des prévisions.Ces prévisions doivent être rangées par année, mois, article, client, et doivent totaliser une quantité en commande.
Cette quantité varie selon le besoin du client, et est associée à une date de calcul. Le tout est borné sur des dates passées en paramètres.
Il peut y avoir plusieurs lignes (normalement max 2), pour une même date de calcul.
Il faut donc lire uniquement les dernière dates pour chaque prévision année/mois/article/client et si deux lignes pour la même date il faut additionner les quantités pour un seule ligne.

Je suis parti sur une sur une requête :

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
24
25
26
27
28
29
30
31
SELECT 
        annee,         
        mois, 
        no_art, 
        no_cli, 
        la_somme_qte_pic, 
        MAX(date_calcul) 
FROM 
        (SELECT 
                LEFT(ficPrevisions.date_besoin , 4 ) AS annee,   
                SUBSTRING(ficPrevisions.date_besoin , 5, 2 ) AS mois,   
                ficPrevisions.no_art AS no_art,   
                ficPrevisions.no_cli AS no_cli,   
                SUM(ficPrevisions.qte_pic) AS la_somme_qte_pic,   
                ficPrevisions.date_calcul AS date_calcul 
        FROM 
                ficPrevisions 
        WHERE 
                LEFT(ficPrevisions.date_besoin , 4 ) BETWEEN {pAnneeDebut} AND {pAnneeFin}
        GROUP BY 
                LEFT(ficPrevisions.date_besoin , 4 ),   
                SUBSTRING(ficPrevisions.date_besoin , 5, 2 ),   
                ficPrevisions.no_art,   
                ficPrevisions.no_cli,   
                ficPrevisions.date_calcul)
GROUP BY 
        annee,         
        mois, 
        no_art, 
        no_cli, 
        la_somme_qte_pic

Mais le résultat n'est pas correct, la quantité n'étant jamais correctement additionnée ou empêchant le regroupement par date.
J'ai testé avec la somme dans la sous requête et l'extraction date dans la requête principale mais le problème reste le même.
J'ai essayé sans sous requêtes mais même résultat.
J'ai essayé avec EXISTS mais beaucoup trop long, je n'ai pas pu consulter le résultat.

Qu'en pensez-vous ?

Merci pour vos retours.

[elsuket]

Bonjour,

Sur le principe, vous n'étiez pas loin de la solution, à ce point près qu'il faut extraire la dernière date de calcul dans une requête distincte.
On intègre ensuite cette requête dans une expression de table commune (CTE dans la littérature), ou dans une sous requête.
Ceci permet de spécifier un prédicat de jointure sur la même table basé sur cette dernière date de calcul.

Voici donc l'expression de la requête avec une CTE :

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
24
25
26
27
28
29
WITH
	CTE AS
	(
		-- 1. Extrait la date de dernier calcul pour chaque tuple {année, mois, article, client}
		SELECT	LEFT(ficPrevisions.date_besoin , 4) AS annee
			, SUBSTRING(ficPrevisions.date_besoin, 5, 2 ) AS mois
			, no_art
			, no_cli
			, MAX(date_calcul) AS date_dernier_calcul
		FROM	dbo.ficPrevisions
		WHERE	LEFT(ficPrevisions.date_besoin , 4 ) BETWEEN @pAnneeDebut AND @pAnneeFin
		GROUP	BY LEFT(ficPrevisions.date_besoin , 4) AS annee
			, SUBSTRING(ficPrevisions.date_besoin, 5, 2 ) AS mois
			, no_art
			, no_cli
	)
SELECT		C.annee
		, C.mois
		, C.no_art
		, C.no_cli
		, SUM(P.qte_pic) AS la_somme_qte_pic -- 3. Ce qui nous permet ensuite de calculer la quantité cumulée à ladite date
		, C.date_dernier_calcul
FROM		dbo.ficPrevisions AS P
INNER JOIN	CTE AS C
			ON LEFT(P.date_besoin , 4) = C.annee
			AND SUBSTRING(P.date_besoin, 5, 2 ) = C.mois
			AND P.no_art = C.no_art
			AND P.no_cli = C.no_cli
			AND P.date_calcul = C.date_dernier_calcul -- 2. On utilise la date de dernier calcul pour la jointure

Et l'expression de la même requête avec une sous-requête :

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
24
25
26
SELECT		C.annee
		, C.mois
		, C.no_art
		, C.no_cli
		, SUM(P.qte_pic) AS la_somme_qte_pic -- 3. Ce qui nous permet ensuite de calculer la quantité cumulée à ladite date
		, C.date_dernier_calcul
FROM		dbo.ficPrevisions AS P
INNER JOIN	(
			-- 1. Extrait la date de dernier calcul pour chaque tuple {année, mois, article, client}
			SELECT	LEFT(ficPrevisions.date_besoin , 4) AS annee
				, SUBSTRING(ficPrevisions.date_besoin, 5, 2 ) AS mois
				, no_art
				, no_cli
				, MAX(date_calcul) AS date_dernier_calcul
			FROM	dbo.ficPrevisions
			WHERE	LEFT(ficPrevisions.date_besoin , 4 ) BETWEEN @pAnneeDebut AND @pAnneeFin
			GROUP	BY LEFT(ficPrevisions.date_besoin , 4) AS annee
				, SUBSTRING(ficPrevisions.date_besoin, 5, 2 ) AS mois
				, no_art
				, no_cli
		) AS C
			ON LEFT(P.date_besoin , 4) = C.annee
			AND SUBSTRING(P.date_besoin, 5, 2 ) = C.mois
			AND P.no_art = C.no_art
			AND P.no_cli = C.no_cli
			AND P.date_calcul = C.date_dernier_calcul -- 2. On utilise la date de dernier calcul pour la jointure

Dites-nous si cela répond à votre problématique.

Notons par ailleurs que :

- Effectuer des groupements, filtres, ou jointures basés sur l'application d'une fonction sur une colonne les rend non-cherchables et donc contre-performants. L'utilisation des fonctions LEFT et SUBSTRING ici est due au fait que le modèle de données sous-jacent à cette table est incorrect puisqu'il viole la première forme normale. Selon celle-ci, toute valeur stockée doit être atomique (au sens du système dans laquelle elle vit). On peut alors décider de re-modéliser, ce qui est très coûteux à court terme, ou bien ajouter une colonne calculée indexée, éventuellement persistée (il s'agit alors d'une nouvelle dénormalisation; c'est peu coûteux à long terme mais cela ne résout pas le problème de modélisation).

- La résolution de votre problématique et les éléments exposés ci-dessus (et bien d'autres choses encore !) sont exposés dans le livre sur SQL Server 2014 que j'ai co-écrit avec Frédéric Brouard, David Barbarin et Christian Soutou. Je ne peux que vous recommander de le lire pour parfaire votre connaissance de SQL Server et de T-SQL ! (cf.lien dans ma signature)

@++

[ritchi46]

Bonjour,
Un grand merci, j'ai opté pour la sous requête et cela fonctionne nickel, je testerai lundi en situation réelle, mais le test que je viens de faire à distance est tout à fait correct, donc il ne devrait pas y avoir de surprise.
J'y ai passé une grande partie de la journée de vendredi sans y parvenir.
Clairement c'est un grand merci.
Si vous êtes pro, je suis prêt à travailler avec vous. On a toujours des requêtes un peu tordues à faire, dans les grands SI (je fais du projet informatique) et on a pas toujours les compétences en interne.
N'hésitez pas à me faire passer vos coordonnées si vous êtes intéressé.

Merci.

Cordialement,

Richard Bellot

PS : je ne sais pas clôturer, si vous pouviez le faire.