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 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
| select distinct
V.sous_type_taux,V.call_put,A.categorie,A.INST_CD as code_pivot,V.devise,
V.echeance_dt,V.emetteur_id,V.emission_dt,V.val_id,V.sous_jacent_id,
A.lib_c,A.lib_l,V.mt_emission,V.nb_titre,V.nominal,V.px_exercice,
V.quotite,A.sous_type,A.type,V.taux_fixe,V.type_interet,V.type_taux,
V.ratio_conversion,V.nom_taux_variable,V.marche,V.VAL_ID as INST_ID,
valo_invent.sensib,getdate() as dat_jour,V.flag_produit_epargne,
V.parite_conversion,V.cpe,V.nominal_uni_futur,v.periodicite_paiement_id,
V.valeur_tick,V.variation_min_cours,V.flag_emtn,V.MTG_ORIG_WAL,nxt_call_dt,nxt_put_dt
FROM portail.dbo.REF_VAL V, (select * from VALO where VALO.STATUT_VL=100 AND VALO.FLAG_INVENT='O'
AND VALO.VALO_TYPE='O'
AND VALO.VALO_DT >= dateadd(mm,-3,getdate())
AND VALO.UPD_DT >= dateadd(dd,-35,getdate())
AND UPD_DT <= "09/08/2009 06:03:42 "
) as VALO,
(SELECT VALO_ID
FROM VALO,(select max(VALIDATION_DT) as date_valo, PTF_ID from VALO
WHERE VALO.STATUT_VL=100 AND VALO.FLAG_INVENT='O' and VALO.VALO_TYPE='O'
AND VALO.VALO_DT >= dateadd(mm,-3,getdate())
AND VALO.UPD_DT >= dateadd(dd,-35,getdate())
AND UPD_DT <= "09/08/2009 06:03:42 "
group by PTF_ID) max_date
WHERE max_date.date_valo = VALO.VALIDATION_DT
AND max_date.PTF_ID = VALO.PTF_ID
AND VALO.STATUT_VL=100 AND VALO.FLAG_INVENT='O'
AND VALO.VALO_TYPE='O'
AND VALO.VALO_DT >= dateadd(mm,-3,getdate())
AND VALO.UPD_DT >= dateadd(dd,-35,getdate())
AND VALO.UPD_DT <= "09/08/2009 06:03:42 "
) as VIEW_DERNIERE_DATE_VALO,
portail.dbo.VALO_INVENT VALO_INVENT, REF_INST A
WHERE VALO_INVENT.VALO_ID = VALO.VALO_ID
AND VIEW_DERNIERE_DATE_VALO.VALO_ID = VALO.VALO_ID
AND V.VAL_ID = VALO_INVENT.INST_ID
AND V.VAL_ID=A.INST_ID
AND ((VALO.VALO_ID IN (17752179,17753509,17753513,17753515,17753502,17753514,17753344,17753316,17753359,17753365,17753321,17753522,17753366,17753317,17753479,17753324,17751775,17752040,17750552,17753325,17753419,17753320,17753376,17753377,17753339,17753337,17753338,17753336,17753335,17753343,17753482,17753367,17753346,17753348,17753318,17753347,17753332,17753351,17753329,17753362,17753490,17753330,17753481,17753488,17753477,17753480,17753353,17753519,17753354,17753474,17753523,17753524,17753342,17753476,17753379,17753375,17753380,17753322,17753360,17753357,17753319,17753326,17753340,17753368,17753374,17753349,17753370,17753369,17753361,17753358,17753372,17753373,17753363,17753364,17753526,17753323,17753496,17753493,17753501,17753498,17753499,17753497,17753500,17753495,17753494,17753378,17753371,17753487,17753333,17753491,17753341,17753402,17753398,17753478,17753429,17753447,17753440,17753470,17753417,17753465,17753469,17753421,17753466,17753425,17753431,17753443,17753442,17753464,17753452,17753414,17753423,17753428,17753422,17753416,17753409,17753427,17753399,17753446,17753424,17753457,17753456,17753407,17753458,17753459,17753453,17753461,17753400,17753430,17753401,17753460,17753403,17753455,17753434,17753435,17753436,17753437,17753438,17753410,17753454,17753450,17753420,17753441,17753433,17753444,17753445,17753467,17753468,17753418,17753432,17753408,17753426,17753406,17753413,17753439,17753448,17753405,17753411,17753451,17753449,17753471,17753462,17753517,17753512,17753511,17753510,17753507,17753504,17753505,17753345,17753506,17753508,17753503,17753516,17753485,17753483,17753327,17753489,17753518,17753463,17753397,17753393,17753395,17753392,17753394,17753328) ))
UNION
select distinct
V.sous_type_taux,V.call_put,A.categorie,A.INST_CD as code_pivot,
V.devise,V.echeance_dt,V.emetteur_id,V.emission_dt,
V.val_id,V.sous_jacent_id,A.lib_c,A.lib_l,V.mt_emission,
V.nb_titre,V.nominal,V.px_exercice,V.quotite,A.sous_type,
A.type,V.taux_fixe,V.type_interet,V.type_taux,V.ratio_conversion,
V.nom_taux_variable,V.marche,V.VAL_ID as INST_ID,valo_invent.sensib,
getdate() as dat_jour,V.flag_produit_epargne,V.parite_conversion,V.cpe, V.nominal_uni_futur,v.periodicite_paiement_id,
null,null,V.flag_emtn,V.MTG_ORIG_WAL,nxt_call_dt,nxt_put_dt
from REF_CONTRAT PARENT, REF_CONTRAT JAMBE, VALO_INVENT VALO_INVENT, REF_VAL V, REF_INST A, REF_INST P,
(select * from VALO where VALO.STATUT_VL=100 AND VALO.FLAG_INVENT='O'
AND VALO.VALO_TYPE='O'
AND VALO.VALO_DT >= dateadd(mm,-3,getdate())
AND VALO.UPD_DT >= dateadd(dd,-14,getdate())
AND UPD_DT <= "09/08/2009 06:03:42 "
) as VALO,
(SELECT VALO_ID
FROM VALO,(select max(VALIDATION_DT) as date_valo, PTF_ID from VALO
WHERE VALO.STATUT_VL=100 AND VALO.FLAG_INVENT='O' and VALO.VALO_TYPE='O'
AND VALO.VALO_DT >= dateadd(mm,-3,getdate())
AND VALO.UPD_DT >= dateadd(dd,-14,getdate())
AND UPD_DT <= "09/08/2009 06:03:42 "
group by PTF_ID) max_date
WHERE max_date.date_valo = VALO.VALIDATION_DT
AND max_date.PTF_ID = VALO.PTF_ID
AND VALO.STATUT_VL=100 AND VALO.FLAG_INVENT='O'
AND VALO.VALO_TYPE='O'
AND VALO.VALO_DT >= dateadd(mm,-3,getdate())
AND VALO.UPD_DT >= dateadd(dd,-14,getdate())
AND VALO.UPD_DT <= "09/08/2009 06:03:42 "
) as VIEW_DERNIERE_DATE_VALO
WHERE JAMBE.PARENT_ID = PARENT.CONTRAT_ID
AND VIEW_DERNIERE_DATE_VALO.VALO_ID = VALO.VALO_ID
AND VALO.VALO_ID = VALO_INVENT.VALO_ID
AND parent.CONTRAT_ID = VALO_INVENT.inst_ID
AND PARENT.CONTRAT_ID = P.INST_ID
AND P.CATEGORIE = 'CESSTMP'
AND JAMBE.SOUS_JACENT_ID = V.VAL_ID
AND V.VAL_ID=A.INST_ID
AND ((VALO.VALO_ID IN (17752179,17753509,17753513,17753515,17753502,17753514,17753344,17753316,17753359,17753365,17753321,17753522,17753366,17753317,17753479,17753324,17751775,17752040,17750552,17753325,17753419,17753320,17753376,17753377,17753339,17753337,17753338,17753336,17753335,17753343,17753482,17753367,17753346,17753348,17753318,17753347,17753332,17753351,17753329,17753362,17753490,17753330,17753481,17753488,17753477,17753480,17753353,17753519,17753354,17753474,17753523,17753524,17753342,17753476,17753379,17753375,17753380,17753322,17753360,17753357,17753319,17753326,17753340,17753368,17753374,17753349,17753370,17753369,17753361,17753358,17753372,17753373,17753363,17753364,17753526,17753323,17753496,17753493,17753501,17753498,17753499,17753497,17753500,17753495,17753494,17753378,17753371,17753487,17753333,17753491,17753341,17753402,17753398,17753478,17753429,17753447,17753440,17753470,17753417,17753465,17753469,17753421,17753466,17753425,17753431,17753443,17753442,17753464,17753452,17753414,17753423,17753428,17753422,17753416,17753409,17753427,17753399,17753446,17753424,17753457,17753456,17753407,17753458,17753459,17753453,17753461,17753400,17753430,17753401,17753460,17753403,17753455,17753434,17753435,17753436,17753437,17753438,17753410,17753454,17753450,17753420,17753441,17753433,17753444,17753445,17753467,17753468,17753418,17753432,17753408,17753426,17753406,17753413,17753439,17753448,17753405,17753411,17753451,17753449,17753471,17753462,17753517,17753512,17753511,17753510,17753507,17753504,17753505,17753345,17753506,17753508,17753503,17753516,17753485,17753483,17753327,17753489,17753518,17753463,17753397,17753393,17753395,17753392,17753394,17753328) )) |
Partager