CREATE procedure [C6MartPeriodico].[SRV_ESTRAZIONE_FASE_2_PRE_MART]
as
declare @dataFase as varchar(30)
select @dataFase = dbo.ToShortDateString(c6martperiodico.getPreviousTrimestre(getDate(),0))
SELECT
		a.rete,
		a.cod_fiscale,
		@dataFase as datafase,
		EXTRARENDIMENTO,
		INVESTIMENTO,
		PREVIDENZA,
		RISERVA,
		LIQUIDITA,
		RisorseNonAllocate,
		contocorrente,
		PARTVIA_DISINV + isnull(b.importo,0) AS Partita_viaggiante,
		--sum(case Upper(a.id_area) when 'CC' then 0 else a.ctv end ) as risorseFinanziarie,
		a.CTV + isnull(b.importo,0)+ PARTVIA_DISINV AS Totale,
		--V
		CASE WHEN RA.VAR_PERC_PTF IS NULL THEN 0.00
			 ELSE ROUND(RA.VAR_PERC_PTF,2)
		END  AS VarComplessivo,
		isnull(RA.COPERTURA,0) AS Copertura,
		CR.MAX_VAR AS VarMassimoProfiloFinanziario,
		-- TRINGALI MODIFICARE ---MODIFICATO!
		case when ra.creditrisk is null then 'n.a.'
        else cast(ra.creditrisk as varchar) end as RISKCLASS,
        mifid.riskclass as RISKCLASSMAX
FROM
		(select
		rete,
		cod_fiscale,
		sum(case Upper(id_area) when 'EXT' then ctv else 0 end ) AS EXTRARENDIMENTO,
		sum(case Upper(id_area) when 'INV' then ctv else 0 end ) AS INVESTIMENTO,
		sum(case when Upper(id_area) = 'PRE1' or Upper(id_area) ='PRE2' then ctv else 0 end ) AS PREVIDENZA,
		sum(case Upper(id_area) when 'RIS' then ctv else 0 end ) AS RISERVA,
		sum(case Upper(id_area) when 'LIQ' then ctv else 0 end ) AS LIQUIDITA,
			sum(case Upper(id_area) when 'NA' then ctv else 0 end ) AS RisorseNonAllocate,
				sum(case Upper(id_area) when 'CC' then ctv else 0 end ) as contocorrente,
		sum((isnull(PARTVIA_DISINV,0))) as PARTVIA_DISINV,
		SUM(CTV) as CTV
		from 
		[C6MartPeriodico].PATRIMONIO_BF
		group by rete, cod_fiscale 
		)  a
left join c6martperiodico.partite_viaggianti b
on a.rete=b.rete and a.cod_fiscale=b.cod_fiscale
INNER JOIN C6MartPeriodico.MIFID AS MIFID
	ON 	a.RETE = MIFID.RETE
	AND a.COD_FISCALE = MIFID.COD_FISCALE
INNER JOIN C6MartPeriodico.CODIFICA_RISCHIO AS CR
	ON MIFID.PROFILO_ASS = CR.PROFILO
LEFT OUTER JOIN C6MartPeriodico.RISCHIO_AGGREGATO AS RA
	ON 	RA.RETE = a.RETE
	AND RA.COD_FISCALE = a.COD_FISCALE
	AND RA.COD_AGGREG = 'COMPLESSIVO|BF'