CREATE VIEW [C6Mart].[vBrsIndDiv]
AS
select * from (
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 cod_fiscale,diversificationindex as indicatore
from c6staging.brs_aggr_out_aladdin a
where concat_aggregazione='COMPLESSIVO'
/*and case when isnull(a.codman,'') <> '' then 'FF@' + a.codman
        when isnull(a.Piva,'') <> '' and isnull(a.codman,'') = '' then a.Piva
       else codicefiscale
end in (select cod_fiscale from c6martperiodico.tb_campione)*/
union
select a.rete,case when isnull(a.codman,'') <> '' then 'FF@' + a.codman+'_BF' 
        when isnull(a.Piva,'') <> '' and isnull(a.codman,'') = '' then a.Piva+'_BF'
       else codicefiscale+'_BF'
end as cod_fiscale,diversificationindex as indicatore
from c6staging.brs_aggr_out_aladdin a
where concat_aggregazione='COMPLESSIVO|BF'
/*and case when isnull(a.codman,'') <> '' then 'FF@' + a.codman
        when isnull(a.Piva,'') <> '' and isnull(a.codman,'') = '' then a.Piva
       else codicefiscale
end in (select cod_fiscale from c6martperiodico.tb_campione)*/
union
select a.rete,case when isnull(a.codman,'') <> '' then 'FF@' + a.codman+'_TERZI' 
        when isnull(a.Piva,'') <> '' and isnull(a.codman,'') = '' then a.Piva+'_TERZI'
       else codicefiscale+'_TERZI'
end as cod_fiscale,diversificationindex as indicatore
from c6staging.brs_aggr_out_aladdin a
where concat_aggregazione='COMPLESSIVO|TERZI'
/*and case when isnull(a.codman,'') <> '' then 'FF@' + a.codman
        when isnull(a.Piva,'') <> '' and isnull(a.codman,'') = '' then a.Piva
       else codicefiscale
end in (select cod_fiscale from c6martperiodico.tb_campione)*/
) a