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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply