SQL server Sum en fonction de certaines colonnes, mais pas toute..

Messages postés
39
Date d'inscription
mardi 25 février 2014
Statut
Membre
Dernière intervention
27 octobre 2019
- - Dernière réponse : yulione
Messages postés
39
Date d'inscription
mardi 25 février 2014
Statut
Membre
Dernière intervention
27 octobre 2019
- 27 oct. 2019 à 16:57
Bonjour à tous.

Tout d'abord, merci pour celles et ceux qui accepterons au moins de lire ceci ^^ Je ne suis pas un fin connaisseur du langage SQL, mais pour les besoins de mon entreprise, j'ai du m'y mettre depuis peu.

Le contexte :
Nous gérons du prêt de matériel. Pour chaque prêt, nous indiquons une date de retour théorique. Seulement, notre ERP (SAGE pour ceux qui connaîtrons, qui fonctionne sous SQL server). Nous souhaitons en interrogeant la base de données, obtenir la liste de tout le matériel prêté, ainsi que lieu où à été envoyé le matériel, et la date théorique de retour (soit la fin du prêt).

Sous l'ERP, le prêt est matérialiser par un mouvement de stock, vers le "dépôt" du client (+1 dans le dépôt). Lors du retour, nous réalisons le mouvement inverse (du dépot client vers notre stock = -1 dans le dépôt). Afin de différencier les mouvements de pret des autres type de mouvement (nombreux et très variable), nous avons fait le choix de typer le mouvement, dans un champs à part.

L'organisation :
Dans une table (dbo.DP_MVT_STOCK), j'ai les informations suivante :
Numéro du mouvement (Char) = MVTST_DOPIECE
quantité (INT) = MVTST_QTE
Référence du produit (Char) = MVTST_ARREF
Numéro de série du matériel (Char) = MVTST_NUMEROSERIELOT
Nom du dépôt cible du mouvement (Char) = MVTST_DENO
Date du mouvement (Date) =MVTST_DLDATEBL


Dans une seconde table (dbo.F_DOCENTETE , c'est la table de champs libres de l'ERP), j'ai :
Numéro du mouvement (Char) = DO_piece
le type de dépôt ("prêt" ou "retour de prêt") = [Type de dépôt]
La date de fin du prêt (Date) = [Date de fin]


Afin de ne voir que le matériel effectivement encore présent dans le dépôt, nous réalisons un HAVING sur la somme de la quantité du mouvement. Ainsi les mouvements de prêt et de retour de prêt s'annule..... En théorie :

Pour vous donner une petite idée, voici les mouvements d'entré et de sorti réalisés pour tester ma requête :


Comme vous pouvez le voir il y a quelques petites contrainte à gérer :
Plusieurs articles avec le même numéro de lot
Différent dépôt de prêt (correspondant à différent client) à gérer)

Et donc voici la requête que j'ai commencer à appliquer pour répondre à me besoin (la selection du dépôt P-TEST" dans ce code est uniquement présent pour les besoins de l'essais. A terme, tout les dépôts doivent ressortir)
SELECT
dbo.F_DOCENTETE.DO_piece,
dbo.F_DOCENTETE.[Type de dépôt],
dbo.F_DOCENTETE.[Date de fin],
dbo.DP_MVT_STOCK.MVTST_ARREF,
dbo.DP_MVT_STOCK.MVTST_NUMEROSERIELOT,
dbo.DP_MVT_STOCK.MVTST_DENO,
dbo.DP_MVT_STOCK.MVTST_DLDATEBL,
dbo.DP_MVT_STOCK.MVTST_DOPIECE,
sum(dbo.DP_MVT_STOCK.MVTST_QTE)
FROM
dbo.F_DOCENTETE INNER JOIN dbo.DP_MVT_STOCK ON (dbo.F_DOCENTETE.DO_piece = dbo.DP_MVT_STOCK.MVTST_DOPIECE)
WHERE
(dbo.DP_MVT_STOCK.MVTST_DENO = 'P-TEST' AND dbo.F_DOCENTETE.[Type de dépôt] = 'Prêt' OR dbo.F_DOCENTETE.[Type de dépôt] = 'Retour Prêt' )
GROUP BY
dbo.F_DOCENTETE.DO_piece,
dbo.F_DOCENTETE.[Type de dépôt],
dbo.F_DOCENTETE.[Date de fin],
dbo.DP_MVT_STOCK.MVTST_ARREF,
dbo.DP_MVT_STOCK.MVTST_NUMEROSERIELOT,
dbo.DP_MVT_STOCK.MVTST_DENO,
dbo.DP_MVT_STOCK.MVTST_DLDATEBL,
dbo.DP_MVT_STOCK.MVTST_DOPIECE
HAVING
(sum(dbo.DP_MVT_STOCK.MVTST_QTE) > 0)


Seulement voila, comme vous l'imaginer, la clause Having, dépendante du GROUP BY, ne fonctionne pas comme je l’espérai, car elle ne somme que les lignes ayant en des valeurs équivalentes pour les colonnes dans le GROUP BY. or, j'aimerai moi que la somme (et le Having qui en dépend) ne se fasse que sur la correspondance entre le dépôt, la référence, le numéro de série...et pas le autres. J'ai absolument besoin d'afficher ces autres données. Mais je ne sais absolument pas comment faire cela.
Résultat de ma requête :


En réalité, les produits effectivement en cours de prêt sont les suivants (les autres ayant déjà été récupéré = mouvement avec quantité à -1) :


ça fait des jours que je cherche une solution. j'ai recherche du coté des sous requête, mais la requête dure des heures. j'ai également cherché du coté de la close OVER sur le sum, avec des order by ou partition by, mais sans succès non plus. J'ai commencé à regarder du coté des curseurs.... et là je me suis dit que j'allais demander de l'aide avant de me lancer la dedans....

Donc, si quelqu'un comprend mon problème et a une piste de solution pour moi, ce serait vraiment génial.

merci à tous.
Afficher la suite 

5 réponses

Messages postés
8952
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
17 novembre 2019
459
0
Merci
bonjour,
ton explication est très compliquée à lire parce que tu n'utilises pas toujours les noms des champs et des tables. tu parles de concept (matériel, par exemple), sans faire le lien avec les noms de tes champs.
par ailleurs, les images que tu partages sont illisibles et inutilisables. ce serait mieux de partager du texte, ou même un tableur avec ces données.

l'exemple que tu donnes n'est pas utilisable: tu écris que tu as deux tables, nous devons voir les données de chaque table.

cependant, si j'ai compris (deviné) ce que tu voulais faire, je suggère:
1) de faire une requête supplémentaire, avec le HAVING sur la somme, utilisant uniquement les champs que tu veux utiliser pour la somme. je crois que cette requête se fera uniquement sur une table.
2) réfléchissons ensuite à ajouter les informations supplémentaires.

la requête en question serait peut-être ceci, est-ce que cela te donne la bonne réponse (partielle, parce qu'il manque des colonnes)? si oui, peux-tu nous montrer le résultat obtenu?
SELECT
dbo.DP_MVT_STOCK.MVTST_ARREF,
dbo.DP_MVT_STOCK.MVTST_NUMEROSERIELOT,
dbo.DP_MVT_STOCK.MVTST_DENO,
sum(dbo.DP_MVT_STOCK.MVTST_QTE)
FROM
dbo.DP_MVT_STOCK
GROUP BY

dbo.DP_MVT_STOCK.MVTST_ARREF,
dbo.DP_MVT_STOCK.MVTST_NUMEROSERIELOT,
dbo.DP_MVT_STOCK.MVTST_DENO,
HAVING
(sum(dbo.DP_MVT_STOCK.MVTST_QTE) > 0)
Commenter la réponse de yg_be
0
Merci
Bonjour yg_be.

tout d'abord, merci d'avoir répondu, malgré les explications difficiles à comprendre.
Il me semble que tu as très bien compris ce que j'essais de faire.

Malheureusement, ta requête en l'état traite tout les mouvements de la base de données.
hors, je ne m'intéresse qu'au mouvement de type "prêt" et "retour de prêt" (information qui se trouve sur la seconde table).

je ne peux malheureusement pas te partager le résultat de cette requête, car il faut plusieurs milliers de lignes.

Cependant, avec un peu d'adaptation, je peux avoir ceci :

SELECT
dbo.DP_MVT_STOCK.MVTST_ARREF,
dbo.DP_MVT_STOCK.MVTST_NUMEROSERIELOT,
dbo.DP_MVT_STOCK.MVTST_DENO,
sum(dbo.DP_MVT_STOCK.MVTST_QTE)
FROM
dbo.F_DOCENTETE INNER JOIN dbo.DP_MVT_STOCK ON (dbo.F_DOCENTETE.DO_piece = dbo.DP_MVT_STOCK.MVTST_DOPIECE)
WHERE
(dbo.F_DOCENTETE.[Type de dépôt] = 'Prêt' OR dbo.F_DOCENTETE.[Type de dépôt] = 'Retour Prêt' )
GROUP BY
dbo.DP_MVT_STOCK.MVTST_ARREF,
dbo.DP_MVT_STOCK.MVTST_NUMEROSERIELOT,
dbo.DP_MVT_STOCK.MVTST_DENO
HAVING
(sum(dbo.DP_MVT_STOCK.MVTST_QTE) > 0)

Cette requete me renvoi le résultat suivant (voir l'onglet requete yg_be) : https://www.dropbox.com/s/adptqud42n0lbpt/tableau%20recherche.xlsx?dl=0

Ce tableau reprendre également les images de mon précédent post. L'onglet mouvement, montre l'organisation des deux tables :

Le résultat est plutôt encourageant. Maintenant, reste à savoir comment récuperer le reste des informations dont j'ai besoin.....

Merci !
Commenter la réponse de yulione
Messages postés
8952
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
17 novembre 2019
459
0
Merci
effectivement, j'avais négligé le type de mouvement, je suis d'accord avec ta correction.
avant d'aller plus loin, je pense que tes exemples sont trop simples. compliquons un peu:
pour Article 2 10101 P-TEST-2,
ajoute deux mouvements:
un prêt de 3
un retour de -1
ensuite, explique quel résultat tu attends dans ce cas-là (de préférence, en mettant le dropbox à jour, c'est très clair ainsi, merci).
Commenter la réponse de yg_be
Messages postés
39
Date d'inscription
mardi 25 février 2014
Statut
Membre
Dernière intervention
27 octobre 2019
1
0
Merci
Wow.... en répondant à ta question je viens de comprendre un cas de figure qui effectivement est plus complexe... J'ai mis à jour comme demandé :
https://www.dropbox.com/s/adptqud42n0lbpt/tableau%20recherche.xlsx?dl=0

Merci.
yg_be
Messages postés
8952
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
17 novembre 2019
459 -
je pense que les dates de fin ne sont pas correctes dans les tableaux "résultat souhaité".
je ne vois pas comment tu arrives au premier tableau souhaité: pourquoi as-tu déduit le dernier retour du dernier prêt, et pas du premier prêt?
dans le second tableau souhaité, sur quelle base as-tu choisi les détails? sur base des dates de mouvement, ou sur base des dates de fin de prêt?
ajoute encore deux mouvements pour Article 2 10101 P-TEST-2:
un prêt de 1, mouvement du 10/27/2019
un retour de -3, mouvement du 10/28/2019
Commenter la réponse de yulione
Messages postés
39
Date d'inscription
mardi 25 février 2014
Statut
Membre
Dernière intervention
27 octobre 2019
1
0
Merci
Et bien justement , c'est ça mon cas plus complexe. Le système ne sera pas capable de différencier deux prêts, pour un même matériel et même lots, même client, mais qui commencent et finissent à des dates différentes.
Mais ce sera complètement anecdotique et je pourrais contourner le problème.

Sur le premier tableau donc, j'ai choisi arbitrairement de déduire du premier prêt, j'aurai tout aussi bien pu le déduire du second.

Pour le second, j'ai fait comme si d'autres instruments ont été ajouté au premier prêt, et que la durée de ce prêt avait été prolongé, en conséquence (la date de fin est donc devenu 20/01/2020 (et non pas 2019 comme dans le tableau... sorry ^^).

Le détail est donc plus choisi en fonction de la date de fin, que de la date de mouvement. J'ai mis des commentaire dans le tableau directement, c''est peut être plus claire avec les chiffres à coté.
https://www.dropbox.com/s/adptqud42n0lbpt/tableau%20recherche.xlsx?dl=0
yg_be
Messages postés
8952
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
17 novembre 2019
459 -
tu écris "notre ERP ne permet pas de dire, ce mouvement de retour est lié à ce mouvement de prêt".
à tout hasard:
est-il possible, pour un retour, d'obtenir dans F_DOCENTETE.[Date de fin] la même date de fin que celle du prêt, ou peut-être un nouveau champ avec une référence du prêt?
ou bien d'utiliser pour un retour, la même valeur de DP_MVT_STOCK.MVTST_DOPIECE que pour le prêt correspondant?
sinon, je pense qu'il faut abandonner le résultat que tu préfères obtenir.

pour obtenir le résultat non préféré, je pense qu'il suffit d'ajouter max([Date de fin]) et max(MVTST_DLDATEBL) dans le SELECT en #2.
yulione
Messages postés
39
Date d'inscription
mardi 25 février 2014
Statut
Membre
Dernière intervention
27 octobre 2019
1 -
Ce serait effectivement possible. ajouter une référence de prêt à indiquer sur tout les mouvements, aller comme retour, réglerai surement le problème. Je vais étudier la questions, car rajouter une saisie d'information de ce type augmenterai le risque d'erreur pour les opérateurs qui réalisent cette activité.

Quand à la seconde solution, je pense que je vais m'orienter sur cela. Je test tout cela demain quand j'aurai accès à la bdd et donnerai la soultion final.

merci
Commenter la réponse de yulione