Calcul d'un indicateur courant sur 12 mois: itération ? [Résolu]

[figarojuju]

Bonjour,
je suis confronté à un problème qui me pose la question de l'itération face au SQL (ensembliste) et je ne sais pas comment m'en dépatouiller.
Voici le tableau que je souhaite obtenir (Année, Mois, WrittenContribution sont faciles à extraire de la BDD existante) :

Annee|Mois|WrittenContribution|EarnedContribution
A1 |1 |101 |101/12
A1 |2 |102 |(101+102)/12
A1 |3 |103 |(101+102+103)/12
A1 |4 |104 |(101+102+103+104)/12
A1 |5 |105 |(101+102+103+104+105)/12
...
An |Mn |wc(n) |(wc(n-12)+wc(n-11)+...+wc(n))/12

EarnedContribution est la colonne prenant comme valeur à la ligne n, la somme des writtenContribution des 11 lignes précédente avec la ligne n, divisée par 12.

Sauriez vous comment calculer cet indicateur dans SQL server et aboutir à ce type de tableau ?
Merci, je vous souhaite une excellente journée

[mikedavem]

Bonjour,

Une solution :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DECLARE @T TABLE
(
 Annee INT,
 Mois INT,
 WrittenContribution INT
);
 
INSERT @T VALUES (2000, 1, 101), (2000, 2, 102), (2000, 3, 103), (2000, 4, 104), (2000, 5, 105);
 
SELECT * FROM @T;
 
SELECT  
 Annee, 
 Mois, 
 WrittenContribution,
 (SELECT SUM(WrittenContribution) FROM @T AS T2 
  WHERE T.Annee = T2.Annee AND T.Mois >= T2.Mois) / 12 AS EarnedContribution
FROM @T AS T;

++

[Waldar]

Avec une jointure plutôt qu'une requête scalaire :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
  SELECT T1.Annee
       , T1.Mois
       , T1.WrittenContribution
       , SUM(T2.WrittenContribution) / 12.0 AS EarnedContribution
    FROM @T AS T1
         INNER JOIN @T AS T2
           ON T2.Annee = T1.Annee
          AND T2.Mois <= T1.Mois
GROUP BY T1.Annee
       , T1.Mois
       , T1.WrittenContribution;
 
Annee       Mois        WrittenContribution EarnedContribution
----------- ----------- ------------------- ---------------------------------------
2000        1           101                 8.416666
2000        2           102                 16.916666
2000        3           103                 25.500000
2000        4           104                 34.166666
2000        5           105                 42.916666

[figarojuju]

Merci mikedavem,
malheureusement, votre solution ne fonctionne pas pour des valeurs à cheval sur deux années...
Auriez-vous d'autres idées ?
Bonne journée

[figarojuju]

Je pense que ma définition devait porter à confusion, donc je mets un petit exemple sous xls
ex.xlsx

PS: j'ai corrigé mon fichier xls qui était faux , désolé !

[Waldar]

En fait il faut convertir les Annee / Mois en une date qu'on pourra comparer plus simplement.

Soit on passe par une vue pour éviter les fonctions de conversions un peu lourdes, soit on le met directement dans la requête :

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
  SELECT T1.Annee
       , T1.Mois
       , T1.WrittenContribution
       , SUM(T2.WrittenContribution) / 12.0 AS EarnedContribution
    FROM @T AS T1
         INNER JOIN @T AS T2
           ON convert(datetime, cast(T2.Annee*10000 + T2.Mois*100 + 1 AS char(8)), 112)
              BETWEEN dateadd(year, -1, convert(datetime, cast(T1.Annee*10000 + T1.Mois*100 + 2 AS char(8)), 112))
                  AND convert(datetime, cast(T1.Annee*10000 + T1.Mois*100 + 1 AS char(8)), 112)
GROUP BY T1.Annee
       , T1.Mois
       , T1.WrittenContribution
 
Annee       Mois        WrittenContribution EarnedContribution
----------- ----------- ------------------- ---------------------------------------
2010        1           64042               5336.833333
2010        2           56426               10039.000000
2010        3           61812               15190.000000
2010        4           106814              24091.166666
2010        5           89998               31591.000000
2010        6           68871               37330.250000
2010        7           122443              47533.833333
2010        8           103414              56151.666666
2010        9           127549              66780.750000
2010        10          170035              80950.333333
2010        11          159626              94252.500000
2010        12          95099               102177.416666
2011        1           80780               103572.250000
2011        2           81041               105623.500000
2011        3           115236              110075.500000
2011        4           79190               107773.500000
2011        5           82836               107176.666666
2011        6           73042               107524.250000
2011        7           85720               104464.000000
2011        8           103849              104500.250000
2011        9           82602               100754.666666
2011        10          68818               92319.916666

Il y a une petite goodies dans la jointure

[figarojuju]

Merci beaucoup, c'est une super solution !
Bravo !