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
| SELECT V_LST_PIECEVENTELIGNES.ARTCODE, ARTICLES.ARTDESIGNATION,
(select sum(VLP1.PLVQTE) from V_LST_PIECEVENTELIGNES VLP1 where pitcode ='F' and VLP1.AFMCODE = 'CORDIAL'
AND VLP1.pcvdateeffet >= dateadd(m,-12,getdate()) and VLP1.pcvdateeffet < dateadd(m,-11,getdate()) and VLP1.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ),
(select sum(VLP2.PLVQTE) from V_LST_PIECEVENTELIGNES VLP2 where pitcode ='F' and VLP2.AFMCODE = 'CORDIAL'
AND VLP2.pcvdateeffet >= dateadd(m,-11,getdate()) and VLP2.pcvdateeffet < dateadd(m,-10,getdate()) and VLP2.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ),
(select sum(VLP3.PLVQTE) from V_LST_PIECEVENTELIGNES VLP3 where pitcode ='F' and VLP3.AFMCODE = 'CORDIAL'
AND VLP3.pcvdateeffet >= dateadd(m,-10,getdate()) and VLP3.pcvdateeffet < dateadd(m,-9,getdate()) and VLP3.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ),
(select sum(VLP4.PLVQTE) from V_LST_PIECEVENTELIGNES VLP4 where pitcode ='F' and VLP4.AFMCODE = 'CORDIAL'
AND VLP4.pcvdateeffet >= dateadd(m,-9,getdate()) and VLP4.pcvdateeffet < dateadd(m,-8,getdate()) and VLP4.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ),
(select sum(VLP5.PLVQTE) from V_LST_PIECEVENTELIGNES VLP5 where pitcode ='F' and VLP5.AFMCODE = 'CORDIAL'
AND VLP5.pcvdateeffet >= dateadd(m,-8,getdate()) and VLP5.pcvdateeffet < dateadd(m,-7,getdate()) and VLP5.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ),
(select sum(VLP6.PLVQTE) from V_LST_PIECEVENTELIGNES VLP6 where pitcode ='F' and VLP6.AFMCODE = 'CORDIAL'
AND VLP6.pcvdateeffet >= dateadd(m,-7,getdate()) and VLP6.pcvdateeffet < dateadd(m,-6,getdate()) and VLP6.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ),
(select sum(VLP7.PLVQTE) from V_LST_PIECEVENTELIGNES VLP7 where pitcode ='F' and VLP7.AFMCODE = 'CORDIAL'
AND VLP7.pcvdateeffet >= dateadd(m,-6,getdate()) and VLP7.pcvdateeffet < dateadd(m,-5,getdate()) and VLP7.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ),
(select sum(VLP8.PLVQTE) from V_LST_PIECEVENTELIGNES VLP8 where pitcode ='F' and VLP8.AFMCODE = 'CORDIAL'
AND VLP8.pcvdateeffet >= dateadd(m,-5,getdate()) and VLP8.pcvdateeffet < dateadd(m,-4,getdate()) and VLP8.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ),
(select sum(VLP9.PLVQTE) from V_LST_PIECEVENTELIGNES VLP9 where pitcode ='F' and VLP9.AFMCODE = 'CORDIAL'
AND VLP9.pcvdateeffet >= dateadd(m,-4,getdate()) and VLP9.pcvdateeffet < dateadd(m,-3,getdate()) and VLP9.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ),
(select sum(VLP10.PLVQTE) from V_LST_PIECEVENTELIGNES VLP10 where pitcode ='F' and VLP10.AFMCODE = 'CORDIAL'
AND VLP10.pcvdateeffet >= dateadd(m,-3,getdate()) and VLP10.pcvdateeffet < dateadd(m,-2,getdate()) and VLP10.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ),
(select sum(VLP11.PLVQTE) from V_LST_PIECEVENTELIGNES VLP11 where pitcode ='F' and VLP11.AFMCODE = 'CORDIAL'
AND VLP11.pcvdateeffet >= dateadd(m,-2,getdate()) and VLP11.pcvdateeffet < dateadd(m,-1,getdate()) and VLP11.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ),
(select sum(VLP12.PLVQTE) from V_LST_PIECEVENTELIGNES VLP12 where pitcode ='F' and VLP12.AFMCODE = 'CORDIAL'
AND VLP12.pcvdateeffet >= dateadd(m,-1,getdate()) and VLP12.pcvdateeffet < dateadd(m,-0,getdate()) and VLP12.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ),
(select sum(VLP13.PLVQTE) from V_LST_PIECEVENTELIGNES VLP13 where pitcode ='F' and VLP13.AFMCODE = 'CORDIAL'
AND VLP13.pcvdateeffet >= dateadd(m,-12,getdate()) and VLP13.pcvdateeffet < dateadd(m,-0,getdate()) and VLP13.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ) as ToTal,
(select ROUND((sum(VLP14.PLVQTE) / 12),2) from V_LST_PIECEVENTELIGNES VLP14 where pitcode ='F' and VLP14.AFMCODE = 'CORDIAL'
AND VLP14.pcvdateeffet >= dateadd(m,-12,getdate()) and VLP14.pcvdateeffet < dateadd(m,-0,getdate()) and VLP14.ARTCODE = V_LST_PIECEVENTELIGNES.ARTCODE ) as Moyenne,
V_STOCK_ARTICLES.STOCKREEL, V_STOCK_ARTICLES.STOCKCDE, V_STOCK_ARTICLES.STOCKRSV,V_STOCK_ARTICLES.STOCKDISPO, V_STOCK_ARTICLES.STOCKTERME as stock,
case when ((Moyenne - stock)>'0') then (Moyenne-stock) else '0' end as achat
FROM V_LST_PIECEVENTELIGNES LEFT OUTER JOIN ARTICLES ON V_LST_PIECEVENTELIGNES.ARTID = ARTICLES.ARTID LEFT OUTER JOIN V_STOCK_ARTICLES ON ARTICLES.ARTID = V_STOCK_ARTICLES.ARTID
where pitcode ='F'
and ARTICLES.ARTISACTIF = 'O'
and V_LST_PIECEVENTELIGNES.AFMCODE = 'CORDIAL'
and V_LST_PIECEVENTELIGNES.ARTCODE not like '%/S2'
and V_LST_PIECEVENTELIGNES.ARTCODE not like '%REM'
and V_LST_PIECEVENTELIGNES.ARTCODE not like '%SAV%'
group by V_LST_PIECEVENTELIGNES.ARTCODE, ARTICLES.ARTDESIGNATION, V_STOCK_ARTICLES.STOCKREEL, V_STOCK_ARTICLES.STOCKCDE, V_STOCK_ARTICLES.STOCKRSV,V_STOCK_ARTICLES.STOCKDISPO, V_STOCK_ARTICLES.STOCKTERME |
