Discussion :

[joomy78]

Bonjour à tous,

je cherche à valoriser un article en fonction de ses achats successifs.
voici la table:




il y a actuellement 394 articles en stock (champ Calc_Stock répété sur chaque ligne)
le dernier achat est de 300 pieces (Db_QteHA) à 12,74 euros (Db_PrixHA) (num=1)
le précédent achat est de 200 pièces à 14.14 (num=2)

on peut déduire approximativement un prix d'achat en utilisant chaque achat: =((300*12.74)+(200*14.14)+(500*13.23))/1000=13.265
mais je souhaiterais pondérer en fonction des achats
300 pièces sur la dernière commande et 94 sur celle juste avant.
((300 *12.74) + (94*14.14))/394=13.074


il faudrait que la requête fasse un traitement itératif:
je prends la 1ère ligne: 300.
je soustrais à ma valeur de stock la quantité achetée: 394-300=94
si le resultat est negatif, j'arrête la =>faux
je prends la ligne 2 avec le resultat de la précédente operation 94-200=-106
C'est négatif: j'arrête la

Je pensais que ce serait facile à traiter en utilisant les CTE, mais j'ai l'impression que ce n'est que pour gérer des arborescences...

est-ce que quelqu'un aurait une idée pour effectuer un traitement itératif?

merci

[Waldar]

Quel est votre SGBD ?

[joomy78]

Désolé, je pensais à une réponse d'ordre général
je suis sous SQL Server 2012 SP3.

[Waldar]

Une CTE récursive fait bien l'affaire.

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
create table achats (Db_Ref char(3), num smallint, Calc_Stock integer, Db_QteHA integer, Db_PrixHA  decimal(8,3))
go
 
insert into achats values 
('rf1', 1, 394, 300, 12.74),
('rf1', 2, 394, 200, 14.14),
('rf1', 3, 394, 500, 13.23),
('rf2', 1, 150, 300, 12.12),
('rf3', 1, 900, 300, 12.74),
('rf3', 2, 900, 200, 14.14),
('rf3', 3, 900, 500, 13.23)
go
 
with cte_recurs (Db_Ref, num, Calc_Stock, Db_QteHA_remain, Db_QteHA, Db_PrixHA, Prix) as
(
select Db_Ref
     , num
     , Calc_Stock
     , case
         when Calc_Stock >= Db_QteHA
         then Calc_Stock  - Db_QteHA
         else 0
       end
     , Db_QteHA
     , Db_PrixHA
     , cast(case
              when Calc_Stock >= Db_QteHA
              then Db_QteHA
              else Calc_Stock
            end * Db_PrixHA as decimal(8,3))
  from achats
 where num = 1
 union all
select act.Db_Ref
     , act.num
     , act.Calc_Stock
     , case
         when rec.Db_QteHA_remain >= act.Db_QteHA
         then rec.Db_QteHA_remain  - act.Db_QteHA
         else 0
       end
     , act.Db_QteHA
     , act.Db_PrixHA
     , cast(rec.Prix
     + case
         when rec.Db_QteHA_remain >= act.Db_QteHA
         then act.Db_QteHA
         else rec.Db_QteHA_remain
       end * act.Db_PrixHA as decimal(8,3))
  from achats     as act
  join cte_recurs as rec  on rec.Db_Ref = act.Db_Ref
                         and rec.num    = act.num - 1
 where rec.Db_QteHA_remain > 0
)
  select Db_Ref, Calc_Stock, Prix / Calc_Stock as Prix_Pondere
    from cte_recurs
   where Db_QteHA_remain = 0
order by Db_Ref asc
go
 
Db_Ref  Calc_Stock  Prix_Pondere
------  ----------  -----------------
rf1            394  13,07401015228426
rf2            150  12,12000000000000
rf3            900  13,26888888888888

[joomy78]

Parfait!
je vais analyser comment tu as fait pour comprendre enfin les CTE appliqués à cet exemple.

Un grand merci de m'avoir consacré tout ce temps.

est-ce que tu connais un tuto sur les CTE?
tous ceux que j'ai trouvé, y compris sur developpez.net traitent d'arborescence...

A bientôt

[aieeeuuuuu]

Bonjour,

Voici également une version sans récursion :

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
 
WITH tmp1 AS (
    SELECT  *,  SUM(Db_QteHA) OVER(PARTITION BY Db_ref ORDER BY num) SC
    FROM	   achats
)
,
tmp2 AS (
    SELECT *, CASE WHEN SC < Calc_Stock THEN DB_QteHA ELSE Calc_stock - SC + Db_QteHA END AS Qte_reelle
    FROM tmp1
)
SELECT Db_Ref, Calc_Stock, SUM(Db_prixHA * Qte_reelle) / SUM(Qte_reelle)
FROM tmp2
WHERE Qte_reelle > 0
GROUP BY Db_Ref, Calc_Stock
ORDER BY Db_ref

[joomy78]

Merci à toi également.

les 2 requêtes fonctionnent dans tous les cas: je n'ai pas trouvé de faille en changeant les quantités, le nombre d'achats...

je peux aller réviser mes CTE

Super forum!