Discussion :

[droliprane]

Bonjour,

dans le cadre de la réécriture d'un projet de gpao, je voudrais transformer un calcul, actuellement réalisé côté ihm (windev), en une procédure stockée.

Le cas concret est le suivant : je parcours des mouvements d'entrée/sortie de stock selon la date croissante, et je souhaite arrêter mon parcours quand la somme des mouvements passe en dessous d'un certain seuil, souvent 0 mais potentiellement un stock sécu.

Est-ce que je peux obtenir cette info (date de passage sous le seuil) via une requête simple, ou bien je dois utiliser un curseur pour avancer ligne par ligne ?

Cordialement,

droliprane

[al1_24]

Quelque chose dans ce gout là :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
with    liste
    as  (   select  ma_date
                ,   sum(mouvement)  over    (ordered by ma_date)    as solde
            from    ma_table
        )
select  min(ma_date)
from    liste
where   solde   < seuil
;

[SQLpro]

Pour cette dernière requête il y a toujours discussion sur le dernier élément. Si l'on veut l"intégrer alors il faut utiliser conjointement un ROW_NUMBER combiné au seuil et rajouter une seconde CTE qui prendre l'élément N+1.

A +

[droliprane]

Merci, je ne connaissais pas cette instruction WITH, je sens que ça ouvre plein de possibilités

Par contre si je demande de récupérer en même temps que la date, également le solde au moment de la rupture de stock, j'ai une erreur selon laquelle le solde n'est pas dans la fonction d'agrégation.

Et je n'arrive pas à corriger

La requête fonctionne comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
WITH liste AS (SELECT xes_date, sum(xes_qte) OVER (ORDER BY xes_date) AS solde FROM xes) 
SELECT MIN(xes_date) AS date_besoin FROM liste WHERE solde < 1000 AND xes_date > cast('20170924' AS datetime) AND xes_date <= cast('20171231' AS datetime)

Mais pas si j'ajoute solde dans le dernier SELECT

[al1_24]

Application de la requête classique dite "de la dernière facture"

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
WITH liste
    AS  (   SELECT  xes_date
                ,   sum(xes_qte) OVER (ORDER BY xes_date) AS solde 
            FROM    xes
            WHERE   xes.xes_date > cast('20170924' AS datetime) 
                AND xes.xes_date <= cast('20171231' AS datetime)
        )
SELECT  lst.xes_date    AS date_besoin
    ,   lst.solde
FROM    liste   AS  lst
WHERE   EXISTS
        (   SELECT  NULL
            FROM    liste   AS  sel
            WHERE   sel.solde   < 1000 
            HAVING  MIN(sel.xes_date)   = lst.xes_date
        )
;

Il y a d'autres manières de faire, mais c'est celle que je préfère...

[droliprane]

Bonjour et merci pour cet exemple quasi sur mesure !

Je suis parvenu à mes fins, mais je rencontre un autre soucis

Cette fois pour écrire la requête consistant à obtenir sur une période donnée la date où le stock cumulé descend le plus dans le négatif.

J'ai écrit ceci (sans m'occuper de la plage de dates pour l'instant) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT TOP 1 xes_date AS date_rupture_maxi, stock_projete FROM
(SELECT DISTINCT xes_date, sum(xes_qte) OVER (ORDER BY xes_date) AS stock_projete
 FROM xes WHERE xes.xes_aid = 3605 AND xes.xes_uid = 75) AS liste
ORDER BY stock_projete

Ca fonctionne mais je me demande si c'est performant... En fait je n'ai pas bien compris l'interet du WHERE EXISTS (SELECT NULL ...

Bonne journée

[al1_24]

En fait je n'ai pas bien compris l'interet du WHERE EXISTS (SELECT NULL ...

Enlève le et tu verras

[droliprane]

Bonjour à tous,

petit retour de performance sur une procédure de calcul de besoin net qui était réalisée auparavant à l'aide d'une table (widget windev mais invisible) dans laquelle étaient rapatriés tous les mouvements d'entrées/sorties pour un article donné, et je cherchais les ruptures de stock par parcours progressif de cette table. Je trouvais que les traitements étaient coûteux en temps du fait de nombreux appels à l'instruction tableActiveFiltre. J'appellerai cette version la v1.

J'ai donc repensé cette procédure en alimentant une table dans la BD (table xes qui stocke les mouvements d'entrées sorties) et en recherchant les points de rupture de stock via des requêtes sql plutôt complexes. Ce sera la version v2.

Est-ce que la v2 est plus efficace que la v1 ? Pas si simple de répondre à la question.

En fait, quand je suis en local sur ma base de test, les nombreuses requêtes sql ne sont pas coûteuses en temps et dans ce cas de figure c'est la version 2 qui est plus performante (environ 15s de gagnées sur un traitement qui prend 55s avec la version TableActiveFiltre). J'ai testé sur un périmètre qui nécessite d'avantages de calculs, le calcul prend 2 minutes au lieu de 3. Intéressant.

Malheureusement, quand je suis en config utilisateur sur un poste client avec la base sur un serveur distant (mais dans le réseau interne quand même) les choses s'inversent et c'est la v1 qui reste plus performante. Un traitement de près de 2 minutes en version 2 ne prend finalement que 1 minutes 20 en v1, soit 40 secondes de moins

J'avais vraiment espéré que de fonctionner par requêtes m'amènerait de gros gains de temps par rapport à l'utilisation du TableActiveFiltre qui fait plutôt ruse de sioux mais programmation pas très élégante... Après, est-ce que mes requêtes sont optimisées au max ?

Si vous avez un avis sur le sujet ou des conseils pour que mon CBN gagne en temps de traitement, je suis preneur. A noter que dans mon ihm j'ai donné la possibilité de restreindre le périmètre de calcul d'un CBN pour ne s'intéresser qu'à un produit en particulier, et dans ce cas on patiente généralement entre 20s et 2 minutes, selon la complexité de la nomenclature de fabrication du produits ciblé. Maintenant, je souhaite lancer un CBN complet à intervalles réguliers, côté serveur, et actuellement cela prends 3 bonnes heures pour traiter pas loin de 6000 références... Donc le CBN global a toujours 3 heures de retard par rapport aux mouvements de stocks...

Merci d'avance si ce sujet vous intéresse et vous amène à me répondre.

droliprane

[SQLpro]

Balancez DDL de vos tables, jeu d'essais et résultat attendu et on vous aideras à optimiser tout cela...

A +

[droliprane]

Bonjour,
j'ai pris note de votre proposition d'aide, dès que j'ai un moment pour faire bien les choses je vous recontacte.
Merci à vous
droliprane