Requête complexe avec incrémentation

Version imprimable

09/02/2015, 18h37
laurent_sitbon

Requête complexe avec incrémentation

Bonjour,

Je tente de faire une requête update mais c'est complexe

voici un résumé rapide
un client signe un contrat pour l'année 2013 pour un prix de 1200€
chaque année, ce client subi une augmentation variable exemple pour 2014 --> 8% de plus donc 8*1200=96 d'augmentation donc prix Final = 1296
la ou ça se complique c'est que ça doit s’incrémenter

Mes 2 tables souscription et ajustement_prix sont comme dans mon schéma (ci-dessous)

Il me manque que les valeurs à mettre dans le champ PrixFinal

Merci de votre aide.

Mes tables à l'origine

table souscription
idsouscription, cotisationAnnuelle, dt_signature
1, 1200, 2012,
2, 978, 2013,

table ajustement_prix
idsouscription, Annee, %Augmentaion, %Reduction, PrixFinal
1, 2012, 10, 10
1, 2013, 8, 0
1, 2014, 9, 0
1, 2015, 7, 0

2, 2013, 8, 8
2, 2014, 9, 0
2, 2015, 7, 2

Ma table avec le résultat souhaité

table ajustement_prix
idsouscription, Annee, %Augmentaion, %Reduction, PrixFinal
1, 2012, 10, 10, 1200 --> donc 10%(Aug) - 10%(Red) = ((0 * 1200(CotisAnnuel)) /100) = 0 + 1200 = 1200
1, 2013, 8, 0, 1296 --> donc 8%(Aug) - 0%(Red) = ((8 * 1200(CotisAnnuel)) /100) = 96 + 1200 = 1296
1, 2014, 9, 0, 1412.64 --> donc 9%(Aug) - 0%(Red) = ((9 * 1296(CotisAnnuel)) /100) = 116.64 + 1296 = 1412.64
1, 2015, 7, 0, 1511.52 --> donc 7%(Aug) - 0%(Red) = ((7 * 1412.64(CotisAnnuel)) /100) = 98.88 + 1412.64 = 1511.52

2, 2013, 8, 8, 978 --> donc 8%(Aug) - 8%(Red) = ((0 * 978(CotisAnnuel)) /100) = 0 + 978 = 978
2, 2014, 9, 0, 1066.02 --> donc 9%(Aug) - 0%(Red) = ((9 * 978(CotisAnnuel)) /100) = 88.02 + 978 = 1066.02
2, 2015, 7, 2, 1119.32 --> donc 7%(Aug) - 2%(Red) = ((5 * 1066.02(CotisAnnuel)) /100) = 53.30 + 1066.02 = 1119.32
09/02/2015, 21h07
Alexandre le Grand

Citation:

Envoyé par laurent_sitbon

Bonjour,

Je tente de faire une requête mais c'est complexe

Bonjour,

Quel genre de requête tentes tu de faire, un Select, update, delete où Insert?

Cordialement.
09/02/2015, 21h48
laurent_sitbon

Type de requête Update

Merci pour votre réponse .

J'ai reformulé le message d'origine.

Merci
10/02/2015, 09h41
SQLpro

Merci de respecter la charte de postage en postant le DDL de vos table ainsi que des INSERT : http://www.developpez.net/forums/d96...vement-poster/

A +
10/02/2015, 09h42
Alexandre le Grand
Bonjour,

A voir ton écrit, on dénombre pas mal d'erreurs de débutant. Pour une bonne gestion, il faudrait stocker seulement les variables et non les calculs.

D'abord pour commencer, je te dirai que ta colonne "PrixFinal", n'a pas sa place dans la table. C'est une colonne calculée, de surcroît évolutive. On peut la générer à tout moment par requête simple, dans une vue ou une procédure stockée. Je te conseillerai pour mieux faire d'insérer une colonne AnnéeEnCours pour estimer les années de variation des différents taux, et aussi de renommer la colonne Année en AnneSouscription.

Les taux n'étant pas fixes, les calculs deviendront fastidieux lors des SELECTs.

En SQL "%" est un mot clé de programmation, donc %Augmentation génère une donnée, alors que tu l'utilise comme nom de colonne. Il serait bon de le renommer Augmentation tout simplemnt, de même pour %Reduction.
Donc pour bien faire, il ne restera que les colonnes idsouscription, Annee, %Augmentaion, %Reduction,.

Donc l'Update de ta requête sans la colonne PrixFinal sera:
Code:

1 2 Declare @Anneé float, @Augmentation float, @Reduction float, @id int UPDATE MaTable SET Année = @Annee, [Augmentation] = @Augmentation, [Reduction] = @Reduction WHERE id = @id
Songe à renommer tes variables, surtout à suivre la méthodologie.

Bien cordialement.

Bonjour,

vous pouvez passer par une requête recursive pour obtenir le résultat souhaité :

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
 
WITH CTE AS(
  SELECT
        ss.idsouscription
        ,aj.Annee
        ,aj.augmentation
        ,aj.Reduction
        ,((aj.augmentation - aj.reduction) / CAST(100 AS DECIMAL(x,y)) * ss.cotisationAnnuelle) + ss.cotisationAnnuelle As PrixFinal
  FROM ajustement_prix aj
  INNER JOIN souscription ss
      ON ss.idsouscription = aj.idsouscription    
  WHERE aj.annee = ss.dt_signature
 
  UNION ALL
 
  SELECT 
        CTE.idsouscription
        ,aj.Annee
        ,aj.augmentation
        ,aj.Reduction
        ,((aj.augmentation - aj.reduction) / CAST(100 AS DECIMAL(x,y)) * CTE.PrixFinal) + CTE.PrixFinal As PrixFinal 
  FROM CTE 
  INNER JOIN ajustement_prix aj
      ON CTE.idsouscription = aj.idsouscription    
  WHERE aj.annee = CTE.annee + 1
 )
SELECT *
FROM CTE
ORDER BY idsouscription, annee

En adaptant le transtypage en fonction du type de vos colonnes cotisation et reduction/augmentation

Un grand merci

Bonjour,
Un grand merci à tous et surtout à aieeeuuuuu qui m'a trouvé la solution.

J'ai modifier un peu la requete

voici le résultat final qui fonctionne parfaitement (dans mon schema d'origine j'ai modifié quelque nom de champs afin de le rendre facile à expliquer)

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
 WITH CTE AS(
  SELECT
        ss.idsouscription
        ,aj.Annee
        ,aj.PourcentAugmentation
        ,aj.PourcentReduction
        ,(((aj.PourcentAugmentation - aj.PourcentReduction) / 100) * ss.MontantCotisationAnnuelle) + ss.MontantCotisationAnnuelle As PrixFinal
  FROM souscriptions ss
  INNER JOIN  ReductionAnnuelle aj
      ON ss.idsouscription = aj.idsouscription    
  WHERE aj.annee = year(ss.dtsignature)
 
  UNION ALL
 
  SELECT 
        CTE.idsouscription
        ,aj.Annee
        ,aj.PourcentAugmentation
        ,aj.PourcentReduction
        ,(((aj.PourcentAugmentation - aj.PourcentReduction) / 100 ) * CTE.PrixFinal) + CTE.PrixFinal As PrixFinal 
  FROM CTE 
  INNER JOIN ReductionAnnuelle aj
      ON CTE.idsouscription = aj.idsouscription    
  WHERE aj.annee = CTE.annee +1
  )
 
UPDATE r
   SET r.PrixFinal = c.PrixFinal
  FROM ReductionAnnuelle AS r
         INNER JOIN 
       CTE AS c 
         ON r.IdSouscription = c.IdSouscription and r.Annee=c.Annee

Génial......