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

Version imprimable

3 pièce(s) jointe(s)

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 :
Pièce jointe 513073

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)
Code:

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 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 :

Pièce jointe 513077

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) :

Pièce jointe 513080

ç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.

EDIT le 25-10-2019, suite à la proposition d'escartefigue :
Rajout de cas plus complexe :

Plusieurs articles avec le même numéro de lot
Différent dépôt de prêt (correspondant à différent client) à gérer)

24/10/2019, 13h34
escartefigue

Bonjour,

Dans la mesure où un même matériel ne peut être prêté qu'une seule fois à un instant "t", il n'est pas nécessaire de faire la somme des mouvements, il suffit de vérifier si le dernier mouvement est de type "dépôt"
Si vous avez des difficultés pour extraire le dernier mouvement en date, consultez mon blog, il y a un article sur ce sujet ;)
25/10/2019, 09h56
yulione

Citation:

Envoyé par escartefigue

Bonjour,

Dans la mesure où un même matériel ne peut être prêté qu'une seule fois à un instant "t", il n'est pas nécessaire de faire la somme des mouvements, il suffit de vérifier si le dernier mouvement est de type "dépôt"
Si vous avez des difficultés pour extraire le dernier mouvement en date, consultez mon blog, il y a un article sur ce sujet ;)

Bonjour Escartefigue.

Merci pour ta réponse, et pour le blog, j'y ai appris quelques petites choses qui me seront bien utile.

Cependant, je ne pense pas que cette solution fonctionne pour tous les cas (ou alors je ne comprend pas comment elle fonctionne), mais c'est mon exemple qui n'est pas assez complet. Désolé c'est ma faute. je m'explique :

Cette solution fonctionnerai si effectivement chaque article étaient bien unique (identifiable par leur N° de série par exemple). hors, ce n'est pas mon cas. j'ai des produits qui sont gérer en lot (il peut y avoir 15 article1 portant le numéro 10101 par exemple).
Mais je peux également avoir plusieurs référence avec le même numéro de série (article1 : 10101 et article2 : 10101). Et oui, les fournisseurs ne s'arrange pas encore entre eux pour attribuer des numéros de série/lot différents à chaque produit.....
De plus, histoire de complexifier encore un peu, ces 15 articles peuvent être prêtés à différent client, en même temps. Je dois donc être capable de différencier chaque article et leur état en fonction de : référence, numéro de série(ou lot) et dépôt du client concerné....

Je vais éditer mon message initiale pour rajouter ces éléments.

Si ta solution fonctionne malgrès ce que je rajoute, alors je suis preneur, mais je n'ai malheureusement pas une idée bien claire de comment adapter le code ^^

merci !

Bonjour,

Citation:

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.

Il faut donc faire le group by sur les colonnes utiles au regroupement. :mrgreen:

Mais que faire des colonnes qu'on a ajouté dans le SELECT "juste pour info, mais qui sont importantes quand même" ?
Ben de 2 choses l'une :
* soit, elles sont potentiellement différentes et alors là il nous faut plus d'explications sur quelle est la valeur à retentir
* soit, elles sont les mêmes sur l'ensemble du regroupement et alors on peut prendre indifféremment la valeur max ou min

ce qui donnerait un truc dans le genre :
Code:

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 /*regroupement sur la correspondance entre le dépôt, la référence et le numéro de série */ SELECT max(dbo.F_DOCENTETE.DO_piece) as DO_piece, dbo.F_DOCENTETE.[Type de dépôt], max(dbo.F_DOCENTETE.[Date de fin]) as [Date de fin], dbo.DP_MVT_STOCK.MVTST_ARREF, dbo.DP_MVT_STOCK.MVTST_NUMEROSERIELOT, max(dbo.DP_MVT_STOCK.MVTST_DENO) as MVTST_DENO, max(dbo.DP_MVT_STOCK.MVTST_DLDATEBL) as MVTST_DLDATEBL, max(dbo.DP_MVT_STOCK.MVTST_DOPIECE) as 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)