218 lines
10 KiB
SQL
218 lines
10 KiB
SQL
CREATE VIEW [C6MartPeriodico].[vBrsVarAllAggr] --where codicefiscale='BRCMRC67H15L736N' order by 3
|
|
AS
|
|
select * from (
|
|
--complessivo BF no CC (OK con Prom)
|
|
select rete,
|
|
case when isnull(codman,'') <> '' then 'FF@' + codman
|
|
when isnull(Piva,'') <> '' and isnull(codman,'') = '' then Piva
|
|
else codicefiscale
|
|
end as codiceFiscale,
|
|
case when(isnull(position_id,'')='') then concat_aggregazione else substring(position_id,1,charindex('@',position_id)-1) end as portafoglio,
|
|
var,
|
|
varp,
|
|
cvar,
|
|
cvarp,
|
|
risksize,
|
|
mcvar,
|
|
mcvarp,
|
|
diversificationindex,
|
|
copertura,
|
|
totale,
|
|
0 as clsint,
|
|
'ISR' as mdclcclsint,
|
|
0 as clsr,
|
|
0 as cicontroparte,
|
|
0 as cltipo,
|
|
0 as clmercato,
|
|
0 as clliquidita,
|
|
0 as isr,
|
|
0 as indmercato,
|
|
0 as indcontroparte,0 as indliquidita,0 as ully,0 as ully_perc,0 as elly_perc,0 as crc,0 as rctot,0 as ully_perc_rc,0 as ullyperc_se,totale_coperto,0 as coperturaproxyrc,0 as numtit,numtitrc
|
|
from c6stagingperiodico.brs_aggr_out_aladdin--_20210630
|
|
where concat_aggregazione like 'COMPLESSIVO|BF%' and (position_id like '%@%' or position_id is null) --and codicefiscale='BRCMRC67H15L736N'
|
|
union
|
|
--ASUL -->
|
|
select rete,case when isnull(codman,'') <> '' then 'FF@' + codman
|
|
when isnull(Piva,'') <> '' and isnull(codman,'') = '' then Piva
|
|
else codicefiscale
|
|
end as codiceFiscale,position_id as portafoglio,
|
|
var,
|
|
varp,
|
|
cvar,
|
|
cvarp,
|
|
risksize,
|
|
mcvar,
|
|
mcvarp,
|
|
diversificationindex,
|
|
copertura,
|
|
totale
|
|
,0 as clsint,'ISR' as mdclcclsint,0 as clsr,0 as cicontroparte,0 as cltipo,0 as clmercato,0 as clliquidita,0 as isr,0 as indmercato,
|
|
0 as indcontroparte,0 as indliquidita,0 as ully,0 as ully_perc,0 as elly_perc,0 as crc,0 as rctot,0 as ully_perc_rc,0 as ullyperc_se,totale_coperto,0 as coperturaproxyrc,0 as numtit,numtitrc
|
|
from c6stagingperiodico.brs_aggr_out_aladdin--_20210630
|
|
where concat_aggregazione like 'COMPLESSIVO|BF%' and SLEEVETAG='WRAPPER_TYPE' --and codicefiscale='BRCMRC67H15L736N'
|
|
union
|
|
--risfin piramide no CC (OK con Prom)
|
|
select rete,case when isnull(codman,'') <> '' then 'FF@' + codman
|
|
when isnull(Piva,'') <> '' and isnull(codman,'') = '' then Piva
|
|
else codicefiscale
|
|
end as codiceFiscale,case when(isnull(position_id,'')='') then Replace(concat_aggregazione,'COMPLESSIVO|BF','RISFIN|PIRAMIDE') else substring(Replace(position_id,'COMPLESSIVO|BF','RISFIN|PIRAMIDE'),1,charindex('@',position_id)) end as portafoglio,
|
|
var,
|
|
varp,
|
|
cvar,
|
|
cvarp,
|
|
risksize,
|
|
mcvar,
|
|
mcvarp,
|
|
diversificationindex,
|
|
copertura,
|
|
totale
|
|
,0 as clsint,'ISR' as mdclcclsint,0 as clsr,0 as cicontroparte,0 as cltipo,0 as clmercato,0 as clliquidita,0 as isr,0 as indmercato,
|
|
0 as indcontroparte,0 as indliquidita,0 as ully,0 as ully_perc,0 as elly_perc,0 as crc,0 as rctot,0 as ully_perc_rc,0 as ullyperc_se,totale_coperto,0 as coperturaproxyrc,0 as numtit,numtitrc
|
|
from c6stagingperiodico.brs_aggr_out_aladdin--_20210630--_20210719
|
|
where concat_aggregazione like 'COMPLESSIVO|BF%' -- and codicefiscale='TRMRRT42H25G852P'
|
|
and substring(isnull(position_id,''),1,charindex('@',isnull(position_id,''))) not like 'COMPLESSIVO|BF|CC%' and isnull(sleevetag,'')<>'WRAPPER_TYPE'
|
|
--and codicefiscale='BRCMRC67H15L736N'
|
|
union --ASUL-->
|
|
select rete,case when isnull(codman,'') <> '' then 'FF@' + codman
|
|
when isnull(Piva,'') <> '' and isnull(codman,'') = '' then Piva
|
|
else codicefiscale
|
|
end as codiceFiscale,Replace(position_id,'COMPLESSIVO|BF','RISFIN|PIRAMIDE') as portafoglio,
|
|
var,
|
|
varp,
|
|
cvar,
|
|
cvarp,
|
|
risksize,
|
|
mcvar,
|
|
mcvarp,
|
|
diversificationindex,
|
|
copertura,
|
|
totale
|
|
,0 as clsint,'ISR' as mdclcclsint,0 as clsr,0 as cicontroparte,0 as cltipo,0 as clmercato,0 as clliquidita,0 as isr,0 as indmercato,
|
|
0 as indcontroparte,0 as indliquidita,0 as ully,0 as ully_perc,0 as elly_perc,0 as crc,0 as rctot,0 as ully_perc_rc,0 as ullyperc_se,totale_coperto,0 as coperturaproxyrc,0 as numtit,numtitrc
|
|
from c6stagingperiodico.brs_aggr_out_aladdin--_20210630--_20210719
|
|
where concat_aggregazione like 'COMPLESSIVO|BF%' -- and codicefiscale='TRMRRT42H25G852P'
|
|
and substring(isnull(position_id,''),1,charindex('@',isnull(position_id,''))) not like 'COMPLESSIVO|BF|CC%' and sleevetag='WRAPPER_TYPE'-- and codicefiscale='BRCMRC67H15L736N'
|
|
union
|
|
--RISFIN solo cc (ok con prom)
|
|
select rete,case when isnull(codman,'') <> '' then 'FF@' + codman
|
|
when isnull(Piva,'') <> '' and isnull(codman,'') = '' then Piva
|
|
else codicefiscale
|
|
end as codiceFiscale,case when(isnull(position_id,'')='') then Replace(concat_aggregazione,'COMPLESSIVO|BF','RISFIN|PIRAMIDE|Liq') else
|
|
substring(Replace(position_id,'COMPLESSIVO|BF','RISFIN|PIRAMIDE|Liq'),1,charindex('@',Replace(position_id,'COMPLESSIVO|BF','RISFIN|PIRAMIDE|Liq'))-1) end as portafoglio,
|
|
var,
|
|
varp,
|
|
cvar,
|
|
cvarp,
|
|
risksize,
|
|
mcvar,
|
|
mcvarp,
|
|
diversificationindex,
|
|
copertura,
|
|
totale
|
|
,0 as clsint,'ISR' as mdclcclsint,0 as clsr,0 as cicontroparte,0 as cltipo,0 as clmercato,0 as clliquidita,0 as isr,0 as indmercato,
|
|
0 as indcontroparte,0 as indliquidita,0 as ully,0 as ully_perc,0 as elly_perc,0 as crc,0 as rctot,0 as ully_perc_rc,0 as ullyperc_se,totale_coperto,0 as coperturaproxyrc,0 as numtit,numtitrc
|
|
from c6stagingperiodico.brs_aggr_out_aladdin--_20210719
|
|
where substring(isnull(position_id,''),1,charindex('@',isnull(position_id,''))) like 'COMPLESSIVO|BF|CC%'-- and codicefiscale='TRMRRT42H25G852P'
|
|
union
|
|
--PTF COMPLESSIVO (ok con prom)
|
|
select rete,case when isnull(codman,'') <> '' then 'FF@' + codman
|
|
when isnull(Piva,'') <> '' and isnull(codman,'') = '' then Piva
|
|
else codicefiscale
|
|
end as codiceFiscale,case when(isnull(position_id,'')='') then concat_aggregazione else
|
|
substring(position_id,1,charindex('@',position_id)-1) end as portafoglio,
|
|
var,
|
|
varp,
|
|
cvar,
|
|
cvarp,
|
|
risksize,
|
|
mcvar,
|
|
mcvarp,
|
|
diversificationindex,
|
|
copertura,
|
|
totale
|
|
,0 as clsint,'ISR' as mdclcclsint,0 as clsr,0 as cicontroparte,0 as cltipo,0 as clmercato,0 as clliquidita,0 as isr,0 as indmercato,
|
|
0 as indcontroparte,0 as indliquidita,0 as ully,0 as ully_perc,0 as elly_perc,0 as crc,0 as rctot,0 as ully_perc_rc,0 as ullyperc_se,totale_coperto,0 as coperturaproxyrc,0 as numtit,numtitrc
|
|
from c6stagingperiodico.brs_aggr_out_aladdin--_20210630--_20210719
|
|
where concat_aggregazione = 'COMPLESSIVO'
|
|
union
|
|
--TIPOPRODTERZI|TERZI -->fam prod (ok con prom)
|
|
select rete,case when isnull(codman,'') <> '' then 'FF@' + codman
|
|
when isnull(Piva,'') <> '' and isnull(codman,'') = '' then Piva
|
|
else codicefiscale
|
|
end as codiceFiscale,case when(isnull(position_id,'')='') then Replace(concat_aggregazione,'COMPLESSIVO|TERZI','TIPOPRODTERZI|TERZI') else
|
|
substring(Replace(position_id,'COMPLESSIVO|TERZI','TIPOPRODTERZI|TERZI'),1,charindex('@',position_id)) end as portafoglio,
|
|
var,
|
|
varp,
|
|
cvar,
|
|
cvarp,
|
|
risksize,
|
|
mcvar,
|
|
mcvarp,
|
|
diversificationindex,
|
|
copertura,
|
|
totale
|
|
,0 as clsint,'ISR' as mdclcclsint,0 as clsr,0 as cicontroparte,0 as cltipo,0 as clmercato,0 as clliquidita,0 as isr,0 as indmercato,
|
|
0 as indcontroparte,0 as indliquidita,0 as ully,0 as ully_perc,0 as elly_perc,0 as crc,0 as rctot,0 as ully_perc_rc,0 as ullyperc_se,totale_coperto,0 as coperturaproxyrc,0 as numtit,numtitrc
|
|
from c6stagingperiodico.brs_aggr_out_aladdin--_20210719
|
|
where concat_aggregazione like 'COMPLESSIVO|TERZI%' --and codicefiscale='TRMRRT42H25G852P'
|
|
union
|
|
--COMPLESSIVO|TERZI --> Interm (ok con prom)
|
|
select a.rete,case when isnull(a.codman,'') <> '' then 'FF@' + a.codman
|
|
when isnull(a.Piva,'') <> '' and isnull(a.codman,'') = '' then a.Piva
|
|
else codicefiscale
|
|
end as codiceFiscale,
|
|
isnull(b.tipoptf,'')+'|'+isnull(intermediario,'')+'|'+chiave_prodotto_terzi as portafoglio,
|
|
var,
|
|
varp,
|
|
cvar,
|
|
cvarp,
|
|
risksize,
|
|
mcvar,
|
|
mcvarp,
|
|
diversificationindex,
|
|
copertura,
|
|
totale
|
|
,0 as clsint,'ISR' as mdclcclsint,0 as clsr,0 as cicontroparte,0 as cltipo,0 as clmercato,0 as clliquidita,0 as isr,0 as indmercato,
|
|
0 as indcontroparte,0 as indliquidita,0 as ully,0 as ully_perc,0 as elly_perc,0 as crc,0 as rctot,0 as ully_perc_rc,0 as ullyperc_se,totale_coperto,0 as coperturaproxyrc,0 as numtit,numtitrc
|
|
from c6stagingperiodico.brs_aggr_out_aladdin--_20210719
|
|
a inner join c6martperiodico.w6clirep b
|
|
on a.position_id=b.position_id
|
|
where concat_aggregazione like 'complessivo|terzi%' --and codicefiscale='TRMRRT42H25G852P'--and a.codman='7168'--a.piva='01840910150'
|
|
union
|
|
select rete,case when isnull(codman,'') <> '' then 'FF@' + codman
|
|
when isnull(Piva,'') <> '' and isnull(codman,'') = '' then Piva
|
|
else codicefiscale
|
|
end as codiceFiscale,case when(isnull(position_id,'')='') then concat_aggregazione else
|
|
substring(position_id,1,charindex('@',position_id)) end as portafoglio,
|
|
var,
|
|
varp,
|
|
cvar,
|
|
cvarp,
|
|
risksize,
|
|
mcvar,
|
|
mcvarp,
|
|
diversificationindex,
|
|
copertura,
|
|
totale
|
|
,0 as clsint,'ISR' as mdclcclsint,0 as clsr,0 as cicontroparte,0 as cltipo,0 as clmercato,0 as clliquidita,0 as isr,0 as indmercato,
|
|
0 as indcontroparte,0 as indliquidita,0 as ully,0 as ully_perc,0 as elly_perc,0 as crc,0 as rctot,0 as ully_perc_rc,0 as ullyperc_se,totale_coperto,0 as coperturaproxyrc,0 as numtit,numtitrc
|
|
from c6stagingperiodico.brs_aggr_out_aladdin--_20210719
|
|
where concat_aggregazione = 'COMPLESSIVO|TERZI'--and codman='7168'--a.piva='01840910150'
|
|
union
|
|
select a.rete,case when isnull(a.codman,'') <> '' then 'FF@' + a.codman
|
|
when isnull(a.Piva,'') <> '' and isnull(a.codman,'') = '' then a.Piva
|
|
else codfis
|
|
end as codiceFiscale,
|
|
isnull(a.tipoptf,'')+'|'+isnull(a.intermediario,'')+'|'+'CC' as portafoglio,
|
|
0 as var,0 as varp,0 as cvar,0 as cvarp,0 as risksize,0 as mcvar,0 as mcvarp,0 as diversificationindex,
|
|
100 as copertura,case when sum(ctv)>10000 then sum(ctv)-10000 else sum(ctv) end as totale,1 as clsint,'ISR' as mdclcclsint,1 as clsr,1 as cicontroparte,1 as cltipo,
|
|
1 as clmercato,1 as clliquidita,0 as isr,0 as indmercato,0 as indcontroparte,0 as indliquidita,0 as ully,0 as ully_perc,0 as elly_perc,0 as crc,0 as rctot,
|
|
0 as ully_perc_rc,0 as ullyperc_se,100 as totale_coperto,0 as coperturaproxyrc,1 as numtit,0 as numtitrc
|
|
from c6martperiodico.w6clirep a where tipoptf like 'complessivo|terzi%' and famprodt='Conti correnti' --and a.codman='7168'--piva='01840910150'
|
|
group by a.rete,case when isnull(a.codman,'') <> '' then 'FF@' + a.codman
|
|
when isnull(a.Piva,'') <> '' and isnull(a.codman,'') = '' then a.Piva
|
|
else codfis
|
|
end,
|
|
isnull(a.tipoptf,'')+'|'+isnull(a.intermediario,'')+'|'+'CC'
|
|
) a
|
|
--where codicefiscale in (select cod_fiscale from c6martperiodico.tb_campione) |