IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Mini-challenge SQL n°1


Sujet :

Développement SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    FMJ
    FMJ est déconnecté
    Membre éclairé
    Profil pro
    tutu
    Inscrit en
    Octobre 2003
    Messages
    417
    Détails du profil
    Informations personnelles :
    Localisation : France, Aveyron (Midi Pyrénées)

    Informations professionnelles :
    Activité : tutu

    Informations forums :
    Inscription : Octobre 2003
    Messages : 417
    Par défaut Mini-challenge SQL n°1


    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.

  2. #2
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 44
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    Bonjour,

    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 !

    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 ?

    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.

    @++

  3. #3
    FMJ
    FMJ est déconnecté
    Membre éclairé
    Profil pro
    tutu
    Inscrit en
    Octobre 2003
    Messages
    417
    Détails du profil
    Informations personnelles :
    Localisation : France, Aveyron (Midi Pyrénées)

    Informations professionnelles :
    Activité : tutu

    Informations forums :
    Inscription : Octobre 2003
    Messages : 417
    Par défaut
    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.

  4. #4
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 44
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    Cela n'est pas la solution directe, mais vous pourrez probablement vous en inspirer :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    @++

  5. #5
    FMJ
    FMJ est déconnecté
    Membre éclairé
    Profil pro
    tutu
    Inscrit en
    Octobre 2003
    Messages
    417
    Détails du profil
    Informations personnelles :
    Localisation : France, Aveyron (Midi Pyrénées)

    Informations professionnelles :
    Activité : tutu

    Informations forums :
    Inscription : Octobre 2003
    Messages : 417
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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

  6. #6
    FMJ
    FMJ est déconnecté
    Membre éclairé
    Profil pro
    tutu
    Inscrit en
    Octobre 2003
    Messages
    417
    Détails du profil
    Informations personnelles :
    Localisation : France, Aveyron (Midi Pyrénées)

    Informations professionnelles :
    Activité : tutu

    Informations forums :
    Inscription : Octobre 2003
    Messages : 417
    Par défaut
    Et merci à Elsuket !

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Requête sql challenge
    Par voxov dans le forum Langage SQL
    Réponses: 14
    Dernier message: 22/10/2013, 02h25
  2. Requête sql challenge 2
    Par voxov dans le forum Langage SQL
    Réponses: 9
    Dernier message: 09/10/2013, 14h41
  3. Challenge SQL Oracle
    Par alex_972 dans le forum SQL
    Réponses: 9
    Dernier message: 05/10/2011, 13h26
  4. Recherche codeur PL/SQL pour mini-projet
    Par Jeanfoya dans le forum Autres
    Réponses: 0
    Dernier message: 14/03/2010, 18h50
  5. Un challenge SQL!
    Par Ismaël(l) dans le forum Langage SQL
    Réponses: 2
    Dernier message: 03/05/2007, 11h23

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo