Auteur de questions
Requête complexe avec incrémentation

Question
-
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émenterMes 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, 2Mes tables avec le resultat 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
- Modifié laurent_sitbon lundi 9 février 2015 21:14
Toutes les réponses
-
Bonjour,
Voici comme base une requête SELECT qui pourrait vous aider à réaliser votre requête UPDATE
SELECT IDSouscription, Annee, (1 + Augmentation / 100. - Reduction / 100.) as Augmentation, (SELECT EXP(SUM(LOG(1 + (Augmentation / 100. - Reduction / 100.)))) * (SELECT TOP 1 CotisationAnnuelle FROM Souscription as S WHERE S.IDSouscription = A1.IDSouscription) AS PrixFinal FROM Ajustement_Prix as A2 WHERE A1.IDSouscription = A2.IDSouscription and A1.Annee >= A2.Annee) FROM Ajustement_Prix as A1
CordialementCharlie Dancoisne - Independent Consultant & Trainer
-
Bonjour,
La requête finale en utilisant un CTE (Common Table Expression) pour simplifier la lecture :
Use BDDTest Create Table Souscription ( IDSouscription int, CotisationAnnuelle int, dt_Signature int ) Go insert into Souscription values (1, 1200, 2012), (2, 978, 2013) Go Create Table Ajustement_Prix ( IDSouscription int, Annee int, Augmentation int, Reduction int, PrixFinal decimal(8,2) ) Go insert into Ajustement_Prix values (1, 2012, 10, 10, null), (1, 2013, 8, 0, null), (1, 2014, 9, 0, null), (1, 2015, 7, 0, null), (2, 2013, 8, 8, null), (2, 2014, 9, 0, null), (2, 2015, 7, 2, null) Go WITH CTE AS ( SELECT IDSouscription, Annee, (1 + Augmentation / 100. - Reduction / 100.) AS Augmentation, (SELECT EXP(SUM(LOG(1 + (Augmentation / 100. - Reduction / 100.)))) * (SELECT TOP 1 CotisationAnnuelle FROM Souscription AS S WHERE S.IDSouscription = A1.IDSouscription) FROM Ajustement_Prix as A2 WHERE A1.IDSouscription = A2.IDSouscription and A1.Annee >= A2.Annee ) AS PrixFinal FROM Ajustement_Prix AS A1 ) UPDATE Ajustement_Prix SET PrixFinal = AP2.PrixFinal FROM Ajustement_Prix AS AP1 INNER JOIN CTE AS AP2 ON AP1.IDSouscription = AP2.IDSouscription AND AP1.Annee = AP2.Annee
Cordialement,
Charlie
Charlie Dancoisne - Independent Consultant & Trainer