October 14, 2019 at 4:18 pm
Good morning all
I would like to add the result of a multiple query in a table
the goal is to have this formathere is my request
;with cte as
(
blablablab
)
INSERT INTO newtable
select * cte
here is my request
with echantillontemp as (
select
Ref_Contrat = v.Contract_ID + '-' + format('0'+ rank() over (partition by v.Contract_ID order by v.sor_ident),'0#')
,num_piece= convert(int,format('0'+ rank() over (partition by v.Contract_ID order by v.sor_ident),'0#'))
,MaxNumeroVersion = max(v."Numéro Version")
,MinNumeroVersion = min(v."Numéro Version")
,NumAliment = v.Contract_ID
,v.sor_ident
,v.Client_ID
from Base_versions v
inner join Base_Risques r on r.[pol_numpol contract_id]=v.Contract_ID
inner join Base_mouvements m on m.Contract_ID=v.Contract_ID
inner join RemonteePerimetreContrats rp on rp.num_aliment=v.Contract_ID
--where v.Contract_ID = 'COYP02617'--'FORP07402'
group by v.sor_ident ,v.Contract_ID
,v.Client_ID
)
, echantillontemp2 as(
select e.*,v.Situation
from echantillontemp e
join Base_versions v
on v.Contract_ID = e.NumAliment
and v."Numéro Version" = e.MinNumeroVersion
)
,echantillon as (
select e.*,d_echeance = dateadd(day,-1,dateadd(year
,convert(int,
datediff(month,r.[pol_datdeb initial_policy_effect_date],r.[Date debut periode risque]/*r.[Date fin de periode risque]*/)/12
)+1
,r.[pol_datdeb initial_policy_effect_date]))
from echantillontemp2 e
join Base_versions v
on v.Contract_ID = e.NumAliment
and v.sor_ident = e.sor_ident
and v."Numéro Version" = e.MaxNumeroVersion
left join Base_Risques r
on r.[pol_numpol contract_id] = e.NumAliment
and r.[sor_ident contract_update_id] = e.sor_ident
and r.[Type conducteur] = 'PRINCIPAL'
and v.[Numéro Version] = e.MaxNumeroVersion
)
select
Ref_Contrat
,ref_produit =r.[pas_libelle_produit affinity]
,t_hta = r.[PM totale HT]
,tt_an = r.[PM totale TTC] - r.[PM totale HT]
,t3_ttc = r.[PM totale TTC]
,fca = '0'
,p_ciale = r.[PM totale TTC] + 0
,formule = r.formula
,formule_code = 'voir referentiel formule'
,formule_ass = 'Non Obligatoire'
,etat_c = case when v.cancellation_reason = 'Pour non paiement de prime' and v.Status_ID = '2' then 3
else
case when v.cancellation_reason is null and v.Status_ID = '2' then 2
Else case v.Status_ID when '1' then 1 when '3' then 5 when '4' then 3 when '6' then 7 when '7' then 7
else -1
end
end
end
,d_fin_etat = r.[Date fin de periode risque]
,nature = case when v.Situation='SUS' then v.Situation else echantillon.Situation end
,d_effet = r.[Date debut periode risque]
,d_echeance = echantillon.d_echeance
,m_paiement = v.Type_of_payment
,m_frac =r.Fractionnement
,d_sous = dateadd(hour,num_piece-1+8,convert(smalldatetime,r.[Date debut periode risque]))
,origine = 'Reprise_SA2020'
,mode_conn = 'Reprise_SA2020' --r.[Canal d entree]
,auteur = 'CORTEX'
,d_effet_c =r.[pol_datdeb initial_policy_effect_date]
,t_hta_p = convert(decimal(12,2),
datediff(day,r.[Date debut periode risque] , isnull(r.[Date fin de periode risque],echantillon.d_echeance))
*
r.[PM totale HT]
/ datediff(day,r.[Date debut periode risque],echantillon.d_echeance)
)
,tt_an_p = convert(decimal(12,2),
datediff(day,r.[Date debut periode risque] , isnull(r.[Date fin de periode risque],echantillon.d_echeance))
*
r.[PM totale TTC]
/ datediff(day,r.[Date debut periode risque],echantillon.d_echeance)
)
-
convert(decimal(12,2),
datediff(day,r.[Date debut periode risque] , isnull(r.[Date fin de periode risque],echantillon.d_echeance))
*
r.[PM totale HT]
/ datediff(day,r.[Date debut periode risque],echantillon.d_echeance)
)
,t3_ttc_p = convert(decimal(12,2),
datediff(day,r.[Date debut periode risque] , isnull(r.[Date fin de periode risque],echantillon.d_echeance))
*
r.[PM totale TTC]
/ datediff(day,r.[Date debut periode risque],echantillon.d_echeance)
)
,fca_p = 'Non Obligatoire'
,p_ciale_p = 'Non Obligatoire'
,id_apporteur = 'A renseigner liaison code courtier'
,id_vendeur = 'A renseigner liaison vendeur par defaut de la concession'
,code_cour_1 = UPPER(left(replace(r.[pas_libelle_produit affinity],' ',''),4)) + convert(varchar(20),r.[Code concession automobile])
,tx_cour_1 = 'Non Obligatoire'
,com_apporteur = 'Non Obligatoire'
,frais_courtage = 'Non Obligatoire'
,coeff_courtage = 'Non Obligatoire'
,code_cour_2 = 'Non Obligatoire'
,tx_cour_2 = 'Non Obligatoire'
,copef = 'Non Obligatoire'
,is_precompte = 'Non Obligatoire'
,tx_precompte_1 = 'Non Obligatoire'
,tx_precompte_2 = 'Non Obligatoire'
,n_ctg = r.[pol_ptrpasid product_id]
,formule_ass2 = Case When r.[pol_ptrpasid product_id] = '3361' and r.[PM_Ass. 0Km Mondial Ass] > 0 then '921439'
When r.[pol_ptrpasid product_id] = '3601' and r.[PM_Ass. 0Km Mondial Ass] > 0 then '921586'
When r.[pol_ptrpasid product_id] = '3661' and r.[PM_Ass. 0Km Mondial Ass] > 0 then '921558'
When r.[pol_ptrpasid product_id] = '3481' and r.[PM_Ass. 0Km Mondial Ass] > 0 then '?'
When r.[pol_ptrpasid product_id] = '3181' and r.[PM_Ass. 0Km Mondial Ass] > 0 then '921406'
When r.[pol_ptrpasid product_id] = '3361' and r.[M_Ass. 25Km Mondial Ass] > 0 then 'Néant'
When r.[pol_ptrpasid product_id] = '3601' and r.[M_Ass. 25Km Mondial Ass] > 0 then 'Néant'
When r.[pol_ptrpasid product_id] = '3661' and r.[M_Ass. 25Km Mondial Ass] > 0 then '921800'
When r.[pol_ptrpasid product_id] = '3481' and r.[M_Ass. 25Km Mondial Ass] > 0 then 'Néant'
When r.[pol_ptrpasid product_id] = '3181' and r.[M_Ass. 25Km Mondial Ass] > 0 then 'B9214060002'
else ' '
end
,ID_Compagnie ='7400'
,ID_assisteur_1 = case when r.[PM_Ass. 0Km Mondial Ass] > 0 then '7600'
when r.[M_Ass. 25Km Mondial Ass] > 0 then '7600'
else ''
end
,ID_assisteur_2 = case when r.[PM_Def. Pen. et Rec. PRO] > 0 then '4300'
else ''
end
,ID_erreur = ' '
,Integration_commentaire =' '
--,r.*
from
echantillon
join Base_versions v
on v.Contract_ID = echantillon.NumAliment
and v.sor_ident = echantillon.sor_ident
and v."Numéro Version" = echantillon.MaxNumeroVersion
left join Base_Risques r
on r.[pol_numpol contract_id] = echantillon.NumAliment
and r.[sor_ident contract_update_id] = echantillon.sor_ident
and r.[Type conducteur] = 'PRINCIPAL'
and v."Numéro Version" = echantillon.MaxNumeroVersion
inner join Base_clients c
on c.client_id = echantillon.Client_ID
where
1=1
--AND [pol_numpol contract_id]='FORP07402'
order by Ref_Contrat
;
thank you for your help
October 14, 2019 at 4:25 pm
You have stated your requirement, but what is your question? What have you tried and what were the results?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 14, 2019 at 4:28 pm
If newtable exists ...
;with cte as
(
select col1, col2, ...
from ...
)
insert into newtable (col1, col2, ...)
select col1, col2, ...
from cte
If newtable does not exist
;with cte as
(
select col1, col2, ...
from ...
)
select *
into newtable
from cte
October 14, 2019 at 4:29 pm
Note also that it is good practice to
a) Explicitly name the columns you are INSERTing to
b) Avoid SELECT * (unless the number of columns and their names has no effect whatsoever on the script's logic)
c) Use the semicolon as a terminator, not an initiator
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply