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

SQL Firebird Discussion :

Requête plus ou moins complexe


Sujet :

SQL Firebird

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre Expert
    Homme Profil pro
    Directeur technique
    Inscrit en
    Mai 2008
    Messages
    2 400
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : Mai 2008
    Messages : 2 400
    Par défaut Requête plus ou moins complexe
    Salut !
    voilà j'ai deux tables Achat et règlement, je voudrais créer un journal de mouvement par fournisseur. Les deux tables ont comme jointure code_fournisseur. Donc, je voudrais avoir toutes les opérations ordonnées par date que se soit un achat ou un règlement et comme champ calculé le solde de l'opération. Mais comme les dates d'achat et de règlement ne sont pas en correspondance mon résultat est erroné. Voici le code SQL que j'ai réalisé pour cette requête, mais il semble bien qu'il y'a une erreur sans ma logique.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    select distinct a.code_fournisseur, a.date_achat,
    a.net, r.date_reglement, r.montant_regle,
    a.net - r.montant_regle solde
    from tb_achat a, tb_reglement_fournisseur rr
    En pièce jointe le résultat cette requête.


    Merci par avance pour tout aide.
    Fichiers attachés Fichiers attachés

  2. #2
    Expert confirmé
    Avatar de Ph. B.
    Homme Profil pro
    Freelance
    Inscrit en
    Avril 2002
    Messages
    1 786
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 59
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Freelance
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Avril 2002
    Messages : 1 786
    Par défaut
    Bonjour,

    La requête que vous avez proposée est un produit cartésien, pas une jointure dont il manque la condition sur le code fournisseur.
    La requête suivante est une jointure dont le résultat ne répond que partiellement à votre demande.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SELECT a.code_fournisseur, a.date_achat,
    a.net, r.date_reglement, r.montant_regle
    FROM tb_achat a
    INNER JOIN tb_reglement_fournisseur r ON r.code_fournisseur = a.code_fournisseur
    ORDER BY a.code_fournisseur, r.date_reglement
    Ensuite, de manière générale (et cela s'applique à d'autres) pourriez vous donner :
    1. la structure des tables (du moins des colonnes utiles à votre demande),
    2. un jeu d'essai pertinent,
    3. le résultat que vous souhaitez obtenir par rapport à ce jeu d'essai.

  3. #3
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique retraité
    Inscrit en
    Janvier 2007
    Messages
    15 638
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 69
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique retraité
    Secteur : Industrie

    Informations forums :
    Inscription : Janvier 2007
    Messages : 15 638
    Billets dans le blog
    65
    Par défaut
    Bonjour,

    Je 2 fois Philippe , une fois pour sa proposition SQL et une seconde pour ces recommandations , lorsque l'on demande ce genre de choses , il est bon d'appliquer ces trois points . Le mieux étant de fournir un script de création des tables et insertions des enregistrements , ainsi il est beaucoup plus facile de proposer une solution testée .

    je vois au moins une faille à la structure de la table : si je comprends bien un achat peut être réglé en plusieurs fois or il n'y a dans la table règlement aucune référence à l'achat ! AMHA il manque un numéro de pièce (facture/transaction comme il semble bon de l'appeler) sinon le calcul du Solde est totalement inutile

  4. #4
    Membre Expert
    Homme Profil pro
    Directeur technique
    Inscrit en
    Mai 2008
    Messages
    2 400
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : Mai 2008
    Messages : 2 400
    Par défaut
    Philippe - Serge merci pour les conseils et recommandations. c'est OK pour les propositions mais entre temps j'ai essayé avec une union et ça me donne un premier résultat à discuter. voici mon dernier code :
    SELECT a.code_fournisseur, f.nom_fournisseur, a.date_achat,
    a.net, null as montant_regle FROM tb_achat a
    inner join tb_fournisseur f on (f.code_fournisseur = a.code_fournisseur)

    UNION all

    SELECT r.code_fournisseur, f.nom_fournisseur, r.date_reglement,null as net,
    r.montant_regle FROM tb_reglement_fournisseur r
    inner join tb_fournisseur f on (f.code_fournisseur = r.code_fournisseur)

    order by 3
    Ce qui me manque c'est le solde, donc :
    1. comment inclure une colonne solde dans une union ?
    2. s'agissant du même fournisseur, le solde doit tenir compte du résultat de la ligne précédente, et là aucune idée sur comment faire ça ?

    @Serge : Effectivement, pour le référence sur un achat j'avais pas pensé. Selon la demande de mon client, il cherche le solde par fournisseur mais je pense qu'il est plus juste d'avoir une référence sur l'achat, comme vous l'avez proposer, afin de solder les factures d'achat. merci pour la remarque.

    @Philippe : voici la structure des trois tables (les plus importants point de vue requête demandée):
    table fournisseur {code_fournisseur, nom_fournisseur}
    table achat {code_achat, numero_achat, date_achat, type_piece_achat, numero_piece_achat, code_fournisseur, net}
    table reglement {code_reglement, date_reglement, mode_reglement, code_fournissuer, montant_regle}

  5. #5
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique retraité
    Inscrit en
    Janvier 2007
    Messages
    15 638
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 69
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique retraité
    Secteur : Industrie

    Informations forums :
    Inscription : Janvier 2007
    Messages : 15 638
    Billets dans le blog
    65
    Par défaut
    Selon la demande de mon client, il cherche le solde par fournisseur
    S'il s'agit uniquement des Soldes Fournisseurs alors je proposerais ceci

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SELECT a.code_fournisseur, f.nom_fournisseur, SUM(a.Net) as total_net , SUM(COALESCE(r.montant_regle,0) as total_regle 
    FROM tb_achat a 
    join tb_fournisseur f on a.code_fournisseur = f.code_fournisseur
    left join tb_reglement_fournisseur r on a.Code_fournisseur=r.code_fournisseur
    group by 1,2
    voire avec une CTE pour calculer le solde

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    WITH  T AS (SELECT a.code_fournisseur,SUM(a.Net) as total_net, SUM(COALESCE(r.montant_regle,0) as total_regle
    FROM tb_achat a 
    left join tb_reglement_fournisseur r on a.Code_fournisseur=r.code_fournisseur
    group by 1,2 )
     
    SELECT T.code_fournisseur,f.nom_fournisseur,T.total_Net,t.total_regle,T.net-T.regle as solde from T join tb_fournisseur f on t.code_fournisseur = f.code_fournisseur
    plutôt que
    table fournisseur {code_fournisseur, nom_fournisseur}
    table achat {code_achat, numero_achat, date_achat, type_piece_achat, numero_piece_achat, code_fournisseur, net}
    table reglement {code_reglement, date_reglement, mode_reglement, code_fournissuer, montant_regle}
    j'aurais préféré
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     
    CREATE TABLE FOURNISSEUR
    (code_fournisseur INTEGER,
     nom_fournisseur VARCHAR(60),
     CONTRAINT PK_FOURN PRIMARY KEY (code_fournisseur)
    ) ; 
     
    INSERT INTO FOURNISSEUR(code_fournisseur,nom_fournisseur) VALUES (1,'TOTO');
    INSERT INTO FOURNISSEUR(code_fournisseur,nom_fournisseur) VALUES (2,'TATOO');
     
    etc ....
    pour pouvoir faire des tests , ces deux propositions sont faites sans filet

  6. #6
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique retraité
    Inscrit en
    Janvier 2007
    Messages
    15 638
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 69
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique retraité
    Secteur : Industrie

    Informations forums :
    Inscription : Janvier 2007
    Messages : 15 638
    Billets dans le blog
    65
    Par défaut
    @Just-Soft regles-forum-langage sql à lire/
    cela s'applique aux rubriques SQL de chaque SGBD

    @Philippe
    Citation Envoyé par SergioMaster Voir le message
    ces deux propositions sont faites sans filet
    pour la CTE , effectivement j'ai groupé un peu trop , j'avais d'abord fait un copier collé de la première (avec le nom fournisseur) que j'ai ôté ensuite pour le mettre dans la requête 'principale' mais j'ai effectivement oublié d'enlever le critère de groupe

    quant à la première proposition , effectivement elle est fausse , faute à un jeu d'essai. Tombé du lit et n'ayant rien de mieux à faire sans bruit , je me suis fait un petit jeu d'essai moche (pas de PK , pas de CONTRAINTES !!!, manque des cas )

    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
     
    CREATE TABLE FOURNISSEUR
    ( CODE INTEGER,
      NOM VARCHAR(30)
    );
    COMMIT;
     
    INSERT INTO FOURNISSEUR VALUES (1,'TOTO');
    INSERT INTO FOURNISSEUR VALUES(2,'TATA');
    INSERT INTO FOURNISSEUR VALUES(3,'TUTU');
     
    CREATE TABLE ACHAT
    ( FOURNISSEUR INTEGER,
      MONTANT_NET NUMERIC(15,2)
    );
    COMMIT;
    INSERT INTO ACHAT VALUES(1,100);
    INSERT INTO ACHAT VALUES(2,200);
     
    CREATE TABLE REGLEMENT
    ( FOURNISSEUR INTEGER,
      MONTANT_VERSE NUMERIC(15,2)
    );
    COMMIT;
    INSERT INTO REGLEMENT VALUES(1,10);
    INSERT INTO REGLEMENT VALUES(1,10);
    INSERT INTO REGLEMENT VALUES(1,10);
    INSERT INTO REGLEMENT VALUES(1,10);
    INSERT INTO REGLEMENT VALUES(1,10);
    INSERT INTO REGLEMENT VALUES(1,10);
    INSERT INTO REGLEMENT VALUES(2,150);
    une première proposition (pas terrible)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    SELECT F.CODE,F.NOM, 
    (SELECT COALESCE(SUM(MONTANT_NET),0) FROM ACHAT WHERE FOURNISSEUR=f.CODE) AS total_net ,
    (SELECT COALESCE(SUM(montant_verse),0) FROM REGLEMENT WHERE FOURNISSEUR=f.CODE) AS total_regle,
    (SELECT COALESCE(SUM(MONTANT_NET),0) FROM ACHAT WHERE FOURNISSEUR=f.CODE)-
    (SELECT COALESCE(SUM(montant_verse),0) FROM REGLEMENT WHERE FOURNISSEUR=f.CODE) AS SOLDE
     
    FROM FOURNISSEUR F
    une plus jolie , grâce aux CTEs

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    WITH A AS (SELECT FOURNISSEUR,SUM(MONTANT_NET) AS TOTAL_NET FROM ACHAT GROUP BY 1),
         R AS (SELECT FOURNISSEUR,SUM(MONTANT_VERSE) AS TOTAL_REGLE FROM REGLEMENT GROUP BY 1) 
     
    SELECT F.CODE,F.NOM,COALESCE(A.TOTAL_NET,0) AS NET,COALESCE(R.TOTAL_REGLE,0) AS REGLE,COALESCE(A.TOTAL_NET,0)-COALESCE(R.TOTAL_REGLE,0) AS SOLDE 
    FROM FOURNISSEUR F
    LEFT JOIN A ON F.CODE=A.FOURNISSEUR
    LEFT JOIN R ON F.CODE=R.FOURNISSEUR
    résultats conformes au jeu d'essai
    1 TOTO 100.00 60.00 40.00
    2 TATA 200.00 150.00 50.00
    3 TUTU 0.00 0.00 0.00

  7. #7
    Expert confirmé
    Avatar de Ph. B.
    Homme Profil pro
    Freelance
    Inscrit en
    Avril 2002
    Messages
    1 786
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 59
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Freelance
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Avril 2002
    Messages : 1 786
    Par défaut
    Bonjour,
    Citation Envoyé par Just-Soft Voir le message
    @Serge : Effectivement, pour le référence sur un achat j'avais pas pensé. Selon la demande de mon client, il cherche le solde par fournisseur mais je pense qu'il est plus juste d'avoir une référence sur l'achat, comme vous l'avez proposer, afin de solder les factures d'achat. merci pour la remarque.

    @Philippe : voici la structure des trois tables (les plus importants point de vue requête demandée):
    table fournisseur {code_fournisseur, nom_fournisseur}
    table achat {code_achat, numero_achat, date_achat, type_piece_achat, numero_piece_achat, code_fournisseur, net}
    table reglement {code_reglement, date_reglement, mode_reglement, code_fournissuer, montant_regle}
    C'est un 1° pas d'avoir ces tables, mais je me permets d'insister, sans jeu d'essai et sans résultat souhaité par rapport à ce jeu d'essai, il est difficile de proposer une solution.
    Au préalable, il faut régler ce manque de lien entre règlement et achat.
    En effet, tout comptable voudra rattacher un règlement à un achat (de facto, il sera rattaché à un fournisseur car tout achat est fait auprès d'un fournisseur)...

    Au strict minimum, il faudrait les tables et les colonnes suivantes :
    TB_FOURNISSEUR (CODE_FOURNISSEUR, NOM_FOURNISSEUR)
    TB_ACHAT (CODE_ACHAT, DATE_ACHAT, CODE_FOURNISSEUR, NET)
    TB_REGLEMENT (CODE_REGLEMENT, DATE_REGLEMENT, CODE_ACHAT, MONTANT_REGLE)

    Un jeu d'essai possible serait :
    TB_FOURNISSEUR
    1, Fournisseur 1
    2, Fournisseur 2
    TB_ACHAT
    1, 01/07/2014, 1, 250
    2, 01/07/2014, 2, 500
    3, 10/07/2014, 1, 300
    4, 10/07/2014, 2, 400
    TB_REGLEMENT
    1, 05/07/2014, 1, 40
    2, 06/07/2014, 2, 100
    3, 07/07/2014, 1, 100
    4, 07/07/2014, 2, 200
    5, 08/07/2014, 1, 110
    6, 09/07/2014, 2, 200
    7, 11/07/2014, 3, 100

    Comme résultat par achat: (CODE_FOURNISSEUR, CODE_ACHAT, NET, TOTAL_REGLE, SOLDE)
    1, 1, 250, 250, 0
    1, 3, 300, 100, 200
    2, 2, 500, 500, 0
    2, 4, 400, 0, 400

    Comme résultat par fournisseur: (CODE_FOURNISSEUR, TOTAL_NET, TOTAL_REGLE, SOLDE)
    1, 550, 350, 200
    2, 900, 500, 400

    A partir de ces données, on peut proposer une réponse au minimum consistante, au mieux vraie...

    Serge, la 1° requête est AMHA erronée : la somme sur le net se fait sur le résultat d'une jointure (pour 2 règlements d'un achat, on va cumuler 2 fois le même net).
    La clause WITH de la CTE est syntaxiquement incorrecte, une colonne avec une fonction d'agrégation ne peut pas faire partie de la clause de regroupement.

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

Discussions similaires

  1. Requete plus ou moin complexe avec une condition
    Par tidou95220 dans le forum Requêtes
    Réponses: 1
    Dernier message: 07/05/2012, 22h46
  2. Requête plus ou moins compliquée pour moi
    Par Just-Soft dans le forum SQL
    Réponses: 17
    Dernier message: 31/07/2011, 01h04
  3. [C#] 3 chiffres ni plus ni moins
    Par niPrM dans le forum Windows Forms
    Réponses: 1
    Dernier message: 02/06/2004, 10h51

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