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 |
