-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE procedure [C6MartPeriodico].[GESTIONE_SELECT_S80_EVOLUZIONE_CTV_dopoMart]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
SELECT
		INI.rete AS Rete,
		INI.cod_fiscale AS [Codice fiscale],
		convert(varchar(8), rep.DATA_INVIO, 112) AS [Data fase],
		rep.ctv_ext as Extrarendimento,
		rep.ctv_inv as Investimento,
		rep.ctv_pre as Previdenza,
		rep.ctv_ris as Riserva,
		rep.ctv_liq as Liquidità,
		rep.CTV_NA AS [Risorse non allocate],
		rep.CTV_CC AS [Conto Corrente],
		ISNULL(rep.PARTITA_VIAGGIANTE,0) AS [Partita viaggiante],
		--CTV_TOTALE + CTV_CC + CTV_NA  + ISNULL(PARTITA_VIAGGIANTE,0)  AS ControvaloreComplessivo  --V aggiunto il cc e na per coerenza con le altre fasi
		rep.CTV_TOTALE  + ISNULL(rep.PARTITA_VIAGGIANTE,0)  AS [Totale CTV],  --V tolto il cc e na in quanto gia conteggiati
		rep.VAR_COMPLESSIVO AS [Var complessivo],
rep.VAR_COPERTURA AS [Var copertura],
rep.VAR_MAX_PROFILO AS [Var MAX profilo finanziario],
isnull(rep.RISKCLASS, 0) as RisksClass, --0 al posto di n.a. altrimenti rompeva
rep.RISKCLASSMAX AS RisksClassMax
--,case when ra.creditrisk is null then 'n.a.' else null end as RiskClassString,
--	case when mifid.riskclass is null then 'n.a.' else null end as RiskClassMaxString
	FROM
		C6MartPeriodico.MONITORAGGIO_INIZIALE INI
        left outer join
        C6MartPeriodico.MONITORAGGIO_REPORT rep
        on 
        REP.RETE=INI.RETE
        AND 
        REP.COD_FISCALE=INI.COD_FISCALE
--	WHERE 1=1
--		
--		and INI.DATA_FOTO < ISNULL(REP.DATA_INVIO,cast('29991231' as datetime))
--		and ISNULL(DATA_INVIO,cast('29991231' as datetime))<'20110331'
--
--		AND rep.rete is not null
END