Discussion :

[cherche_encore]

Bonjour,

je me présente, je suis développeur asp.net avec une petite expérience en SQL avec Oracle et SqlServer.
Cela fait des années que je me débrouille en consultant les forums et blogs, mais là je peine vraiment.

Je viens donc vous demander votre aide.

J'ai une table stock :

Code :

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

1
2
3
4
5
6
7
8
 
create table stock (
produit nvarchar(30),
quantite int)
 
insert into stock values ('produit_a', 20)
insert into stock values ('produit_b', 30)
insert into stock values ('produit_c', 40)

et une table commande :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
create table commande (
id_commande int,
produit nvarchar(30),
quantite int)
 
insert into commande (1, 'produit_a', 10)
insert into commande (1, 'produit_b', 10)
insert into commande (1, 'produit_c', 10)
insert into commande (2, 'produit_a', 10)
insert into commande (2, 'produit_b', 30)
insert into commande (3, 'produit_a', 10)

je dois afficher un état de ces commandes, avec un indicateur permettant de savoir si pour chaque commande, il y a suffisamment de stock.

MAIS je dois tenir compte de l'ordre des commandes !

Ainsi ici ma commande 1 sera OK, il y a suffisamment de stock.
Par contre pour ma commande 2, le produit B sera en rupture, car aux 30 en stock, il faut soustraire les 10 dans la commande 1.

Je cherche à savoir si il est possible d'établir une requête me permettant d'obtenir ce résultat :

commande / produit / valide
1 / produit_a / ok
1 / produit_b / ok
1 / produit_c / ok
2 / produit_a / ok
2 / produit_b / nok
3 / produit_a / nok

le but étant d'avoir comme résultat final (mais cela je saurait l'obtenir si j'ai le résultat au dessus) :
commande / valide
1 / ok
2 / nok
3 / nok

Avez-vous une idée ? Quelque chose à base de 2 CTE dont l'une se réfère à l'autre peut-être ?

[Waldar]

La réponse va dépendre du SGBD concerné.
Ce sera plus simple chez Oracle et SQL-Server 2012 que SQL-Server 2005/2008.

[cherche_encore]

Ha oui pardon je n'ai pas précisé mon SGBDR.

Pour l'instant je teste avec mon exemple ci-dessous sur un SQL serveur 2008, mais à terme je dois utiliser une base DB2 sous AS400, en version v5r quelque chose. Donc relativement ancienne je crois.

[Waldar]

L'idée c'est de faire une autojointure pour réaliser le cumul des commandes :

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
  select cm1.id_commande
       , cm1.produit
       , cm1.quantite
       , SUM(cm2.quantite) as quantite_cumul
    from commande as cm1
    join commande as cm2
      on cm1.produit      = cm2.produit
     and cm1.id_commande >= cm2.id_commande
group by cm1.id_commande
       , cm1.produit
       , cm1.quantite;
 
id_commande produit                        quantite    quantite_cumul
----------- ------------------------------ ----------- --------------
1           produit_a                      10          10
1           produit_b                      10          10
1           produit_c                      10          10
2           produit_a                      10          20
2           produit_b                      30          40
3           produit_a                      10          30

À partir de là il suffit de comparer avec les stocks :

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
with commande_cumul as
(
  select cm1.id_commande
       , cm1.produit
       , cm1.quantite
       , SUM(cm2.quantite) as quantite_cumul
    from commande as cm1
    join commande as cm2
      on cm1.produit      = cm2.produit
     and cm1.id_commande >= cm2.id_commande
group by cm1.id_commande
       , cm1.produit
       , cm1.quantite
)
select cmd.id_commande
     , cmd.produit
     , cmd.quantite
     , cmd.quantite_cumul
     , case when cmd.quantite_cumul > stk.quantite then 'nok' else 'ok' end as valide
  from commande_cumul as cmd
  join stock          as stk
    on stk.produit = cmd.produit;
 
id_commande produit                        quantite    quantite_cumul valide
----------- ------------------------------ ----------- -------------- ------
1           produit_a                      10          10             ok
1           produit_b                      10          10             ok
1           produit_c                      10          10             ok
2           produit_a                      10          20             ok
2           produit_b                      30          40             nok
3           produit_a                      10          30             nok

Et pour remonter au niveau de la commande, une petite astuce :

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
with commande_cumul as
(
  select cm1.id_commande
       , cm1.produit
       , cm1.quantite
       , SUM(cm2.quantite) as quantite_cumul
    from commande as cm1
    join commande as cm2
      on cm1.produit      = cm2.produit
     and cm1.id_commande >= cm2.id_commande
group by cm1.id_commande
       , cm1.produit
       , cm1.quantite
)
  select cmd.id_commande
       , min(case when cmd.quantite_cumul > stk.quantite then 'nok' else 'ok' end) as valide
    from commande_cumul as cmd
    join stock          as stk
      on stk.produit = cmd.produit
group by cmd.id_commande;
 
id_commande valide
----------- ------
1           ok
2           nok
3           nok

[cherche_encore]

Alors là j'ai qu'un mot à dire : balèze !

Je tâtonnais dans ce sens mais je n'arrivais pas à formaliser, il me manquait la notion du cumul des commandes.

Vraiment merci beaucoup Waldar