none
Requête complexe avec incrémentation RRS feed

  • 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é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

    Mes 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

    lundi 9 février 2015 17:48

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
    
    Cordialement

    Charlie Dancoisne - Independent Consultant & Trainer

    mardi 10 février 2015 21:34
  • 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

    lundi 16 février 2015 12:37