Mini-challenge SQL n°1

Version imprimable

18/11/2009, 14h55
FMJ

Mini-challenge SQL n°1

:D

En fait je suis en train de me gratter la tête (et des cheveux, j'en ai plus guère) sur la requête suivante (sans curseur)

Soit une table qui regroupe les différentes lignes correspondant aux différentes réceptions. Je poste pas la description de la table, trop de champs (260.....).
Chaque ligne correspond à un article réception avec toutes les caractéristiques possibles et inimaginables prévues par l'éditeur.
J'en retiendrais deux !
> Code de l'article (ARTICLE)
> Date de la pièce réception (DATEPIECE)

Je souhaite calculer le nombre de réception d'un article donné par année.

Position du problème :
1. Un article peut avoir plusieurs livraisons durant l'année.
2. La réception d'un article peut se faire sur plusieurs jours, voire semaines (réception de palettes) et donc générer plusieurs pièces de réceptions (une par jour généralement), et donc plusieurs lignes de réception. Mais dans les faits, il s'agit bien de la même réception ! Il me font donc trouver un critère de regroupement pour ne comptabiliser qu'une seule réception pour ces différentes lignes.

3. Les utilisateurs ne sont pas rigoureux dans la saisie de la référence du bon de livraison du fournisseur. Je ne peux donc pas faire de regroupement sur ce champ.

Je pense que la seule méthode pour faire ce regroupement consiste à se baser uniquement sur la date de réception et se donner une règle de gestion : deux lignes de réception d'un même article qui sont espacées de plus de 15j sont considérées comme deux réceptions différentes. Dans le cas contraire, il s'agit d'une seule et même réception.

Exemple pour un même article :
Date Qté
Ligne 1 : 03/02/09 x2
Ligne 2 : 05/02/09 x3
Ligne 3 : 10/02/09 x10
Ligne 4 : 18/04/09 x7
Ligne 5 : 23/09/09 x4

Résultat --> 3 réceptions
Réception n°1 : Lignes 1, 2 et 3
Réception n°2 : Ligne 4
Réception n°3 : Ligne 5

Le blème, c'est que je n'arrive pas à trouver un critère de regroupement puisque la fourchette de date que je me fixe est variable d'une ligne à l'autre.
Si je fais des jointures pour éliminer les lignes "adjacentes" (celles qui sont dans la plage des 15j), j'élimine toutes ces lignes. Or il me faudrait en garder une par réception !

Je suis sous SQL server 2000 et ne bénéficie donc pas des CTE.

Merci d'avance pour vos idées.
18/11/2009, 19h15
elsuket

Bonjour,

Citation:

Soit une table qui regroupe les différentes lignes correspondant aux différentes réceptions. Je poste pas la description de la table, trop de champs (260.....)

Quelle horreur ... Je vous plains !

Citation:

Les utilisateurs ne sont pas rigoureux dans la saisie de la référence du bon de livraison du fournisseur. Je ne peux donc pas faire de regroupement sur ce champ

Serait-ce un canular ? :mouarf:

Citation:

je n'arrive pas à trouver un critère de regroupement puisque la fourchette de date que je me fixe est variable d'une ligne à l'autre.

Là je ne comprend plus ... Vous cherchez à calculer le nombre de réception d'un article donné par année, donc il vous suffirait d'extraire l'année de livraison ...
Le problème se pose lorsque l'article est livré en plusieurs livraisons à cheval sur deux années.

@++ ;)
18/11/2009, 20h08
FMJ

Bonjour Elsuket

En fait, j'ai simplifié les termes de mon explication. En toute rigueur, il faut distinguer livraison et réception.

Le fournisseur peut livrer 18 palettes en une seule fois = une livraison

Mais le client livré va mettre des jours à réceptionner cette livraison. Comme il créé une réception par jour, si ça lui prend 10 jours, il y aura 10 réceptions pour une seule livraison.

Calculer le nombre de réceptions, c'est enfantin.

Calculer le nombre de livraisons à partir du nombre de réceptions, c'est beaucoup plus chaud lorsque l'on ne peut se reposer sur la référence des bons de livraison saisis dans les réceptions.

Donc, mon seul salut est de passer par l'analyse des dates de réception.
> Moins de 15j entre 2 réceptions = 1 seule livraison
> Plus de 15j entre 2 réceptions = 2 livraisons

Cf. l'exemple donné dans le 1er post :

Réception 1 : 03/02/09
Réception 2 : 09/02/09
Réception 3 : 21/02/09
Réception 4 : 18/04/09
Réception 5 : 23/09/09

Réception 1, 2 et 3 correspondent à une seule livraison (même s'il y a plus de 15j entre la première et la dernière réception)
Réception 4 correspond à une seule livraison
Réception 5 correspond à une seule livraison

Au final, il y a eu 3 livraisons pour cet article durant l'année.

La difficulté vient du fait que les 15j sont "glissants" avec une date de réception à l'une des extrémités de la plage.
Je ne peux pas compter 1 livraison pour les premiers 15j du mois s'il y a eu au moins une réception. Il peut y avoir une seconde livraison pour cette livraison en début de seconde la quinzaine de ce mois.

Cela n'est pas la solution directe, mais vous pourrez probablement vous en inspirer :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT *
FROM dbo.maTableLivraisons AS L
JOIN (
		SELECT A.IDArticle,
				MIN(L.dateReception),
				MIN(L.dateReception) + 15
		FROM dbo.maTableArticles AS A
		JOIN dbo.maTableLivraisons AS L
			ON A.IDArticle = L.IDArticle
		GROUP BY A.IDArticle
	) AS TMP (IDArticle, dateDebReception, dateFinReception
	ON L.IDArticle = TMP.IDArticle
	AND L.dateReception BETWEEN TMP.dateDebReception AND TMP.dateFinReception

@++ ;)

Bah je me fais des noeuds pour rien. La solution est toute simple : il suffit d'isoler la dernière réception d'une même livraison (i.e. on vérifie qu'il n'y ait pas d'autres réception dans les 15j qui suivent : on compte 0 s'il y en a (c'est pas la dernière réception de la série) ou on compte 1 s'il n'y en a pas (c'est bien la dernière)).

Soit la table LIGNE contenant les réceptions
Une ligne = la réception d'un article (ARTICLE) à une date donnée (DATEPIECE)

L'idée serait donc la suivante :
Code:

1 2 3 4 5 6 SELECT YEAR(L1.DATEPIECE), L1.ARTICLE, SUM(ISNULL((SELECT TOP 1 0 FROM LIGNE AS L2 WHERE L2.ARTICLE=L1.ARTICLE AND L2.DATEPIECE > L1.DATEPIECE AND L2.DATEPIECE <= DATEADD(day, 15, L1.DATEPIECE),1)) FROM LIGNE AS L1 GROUP BY YEAR(L1.DATEPIECE), L1.ARTICLE
Malheureusement, cette requête entraîne l'erreur Cannot perform an aggregate function on an expression containing an aggregate or a subquery à cause du SUM( .. TOP 1 ...)

Qu'à cela ne tienne, il suffit de faire le regroupement dans un second temps.
Code:

1 2 3 4 5 6 7 8 9 SELECT (SAISON), ARTICLE, SUM(LivraisonNb) FROM (SELECT YEAR(L1.DATEPIECE) AS SAISON, L1.ARTICLE AS ART, ISNULL((SELECT TOP 1 0 FROM LIGNE AS L2 WHERE L2.ARTICLE=L1.ARTICLE AND L2.DATEPIECE > L1.DATEPIECE AND L2.DATEPIECE <= DATEADD(day, 15, L1.DATEPIECE),1) AS LivraisonNb FROM LIGNE AS L1) AS TABLE1 GROUP BY SAISON, ARTICLE

19/11/2009, 11h46
FMJ

Et merci à Elsuket !:ccool: