--  [C6MartPeriodico].[PL_D2_S153PatrimonioFinanziario] 's','LMNFNC40L14L219U'
CREATE procedure [C6MartPeriodico].[PL_D2_S153PatrimonioFinanziario]
	-- Add the parameters for the stored procedure here
	@Rete char(1), 
	@CodiceFiscale varchar(16)
AS
BEGIN
	SELECT 
		Intermediario,
		Ordine,
		Banca,
		Controvalore,
		round(Percentuale,2) as Percentuale
	FROM 
	(
		SELECT DISTINCT
			Intermediario,
			Ordine,
			CASE @Rete WHEN 'F' THEN 'Fideuram' WHEN 'S' THEN  'Sanpaolo Invest' ELSE 'IW Private Investments' END AS Banca,			 
			SUM(ctv) over (partition by Intermediario,Ordine) AS CONTROVALORE,
			(
				(SUM(ctv) over (partition by Intermediario,Ordine)) / 
				(SUM(ctv) over (partition by 'Totale'))
			 ) * 100 AS PERCENTUALE
		FROM 
		(
			SELECT 
				CASE @Rete WHEN 'F' THEN 'Fideuram' WHEN 'S' THEN  'Sanpaolo Invest' ELSE 'IW Private Investments' END AS Intermediario,
				SUM(ROUND(CTV,2)) AS ctv,
				1 AS ordine
				FROM C6MartPeriodico.PATRIMONIO_BF patrBF
				WHERE 1 = 1
					AND patrBF.Rete = @Rete
					AND patrBF.Cod_Fiscale = @CodiceFiscale
					and patrBF.id_area  not in ('CC','SELF') 
			UNION ALL
			SELECT 
				Intermediario, 
				SUM(ROUND(CTV,2)) AS ctv,
				2 AS ordine
				FROM C6MartPeriodico.PATRIMONIO_TERZI patrTerzi
				WHERE 1 = 1
					AND patrTerzi.Rete = @Rete
					AND patrTerzi.Cod_Fiscale = @CodiceFiscale			
				GROUP BY patrTerzi.Intermediario
		) PATR
	) A
	WHERE CONTROVALORE IS NOT NULL
	ORDER BY Ordine
END
--USE [C6StampeCentralizzate]
--GO
--/****** Object:  StoredProcedure [C6MartPeriodico].[PL_D2_S153PatrimonioFinanziario]    Script Date: 09/02/2022 11:23:41 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
----  [C6MartPeriodico].[PL_D2_S153PatrimonioFinanziario] 's','LMNFNC40L14L219U'
--ALTER procedure [C6MartPeriodico].[PL_D2_S153PatrimonioFinanziario]
--	-- Add the parameters for the stored procedure here
--	@Rete char(1), 
--	@CodiceFiscale varchar(16)
--AS
--BEGIN
--	SELECT 
--		Intermediario,
--		Ordine,
--		Banca,
--		Controvalore,
--		round(Percentuale,2) as Percentuale
--	FROM 
--	(
--		SELECT DISTINCT
--			Intermediario,
--			Ordine,
--			CASE @Rete WHEN 'F' THEN 'Fideuram' ELSE 'Sanpaolo Invest' END AS Banca,			 
--			SUM(ctv) over (partition by Intermediario,Ordine) AS CONTROVALORE,
--			(
--				(SUM(ctv) over (partition by Intermediario,Ordine)) / 
--				(SUM(ctv) over (partition by 'Totale'))
--			 ) * 100 AS PERCENTUALE
--		FROM 
--		(
--			SELECT 
--				CASE @Rete WHEN 'F' THEN 'Fideuram' ELSE 'Sanpaolo Invest' END AS Intermediario,
--				SUM(ROUND(CTV,2)) AS ctv,
--				1 AS ordine
--				FROM C6MartPeriodico.PATRIMONIO_BF patrBF
--				WHERE 1 = 1
--					AND patrBF.Rete = @Rete
--					AND patrBF.Cod_Fiscale = @CodiceFiscale
--					and patrBF.id_area  not in ('CC','SELF') 
--			UNION ALL
--			SELECT 
--				Intermediario, 
--				SUM(ROUND(CTV,2)) AS ctv,
--				2 AS ordine
--				FROM C6MartPeriodico.PATRIMONIO_TERZI patrTerzi
--				WHERE 1 = 1
--					AND patrTerzi.Rete = @Rete
--					AND patrTerzi.Cod_Fiscale = @CodiceFiscale			
--				GROUP BY patrTerzi.Intermediario
--		) PATR
--	) A
--	WHERE CONTROVALORE IS NOT NULL
--	ORDER BY Ordine
--END