comment recuperer un champ pour un calcul

[gogo85]

bonjour

j'ai fais une requete avec de sous requetes

Code :

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

cela ne fonctionne pas j'aimerai savoir si il est possible de récupérer des valeurs du select pour faire des calculs dans une nouvelle colonne
merci de votre aide

[SQLpro]

Au lieu de cet horrible pâté de SELECT SUM, faites un groupage par mois et ne prenez en compte que les mois considérés.
Un serveur SQL n'est pas fait pour faire de la cosmétique !
A lire : http://sqlpro.developpez.com/cours/sqlaz/erreurs/#L9
A +