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)