IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_TOP_SHOW_EST_ARV_MERC_FAMILIA_PDV_VD]'))
DROP VIEW [dbo].[V_EST_TOP_SHOW_EST_ARV_MERC_FAMILIA_PDV_VD]
GO
CREATE VIEW V_EST_TOP_SHOW_EST_ARV_MERC_FAMILIA_PDV_VD AS  
 
WITH TopShow as (SELECT 
					S.CD_EMP,
					S.CD_TOP_SHOW,
					S.DS_TOP_SHOW,
					S.DT_INI_TOP_SHOW,
					S.DT_FIM_TOP_SHOW,
					l.CD_FILIAL,
					l.CD_ARV_MERC_FAMILIA,
					FA.DS_ARV_MERC_FAMILIA,
					l.PERC_META PERC_META,
					l.QTDE_META,
					ISNULL(F.CD_FUN,0) CD_FUN,	
					ISNULL(vdd.NM_FUN,'NAO DEFINIDO') NM_FUN,
					ISNULL(F.PERC_META,0) PERC_META_VEND,
					ROUND((ISNULL(F.PERC_META,0)/100.000) * l.QTDE_META,0) QTDE_META_VEND 
				 FROM 
					EST_TOP_SHOW S
					INNER JOIN EST_TOP_SHOW_EST_ARV_MERC_FAMILIA_PRC_FILIAL l ON
					S.CD_EMP		= L.CD_EMP	AND
					S.CD_TOP_SHOW	= L.CD_TOP_SHOW

					INNER JOIN EST_ARV_MERC_FAMILIA FA ON 
					L.CD_EMP			  = FA.CD_EMP AND
					L.CD_ARV_MERC_FAMILIA = FA.CD_ARV_MERC_FAMILIA

					INNER JOIN EST_TOP_SHOW_FP_FUNC F ON
					S.CD_EMP		= F.CD_EMP AND
					S.CD_TOP_SHOW	= F.CD_TOP_SHOW AND
					L.CD_FILIAL		= F.CD_FILIAL
					LEFT JOIN FP_FUNC vdd ON
					F.CD_EMP	= vdd.CD_EMP AND
					F.CD_FUN	= vdd.CD_FUN 
	),
	Venda as (select
				S.CD_EMP,
				S.CD_TOP_SHOW,
				S.CD_FILIAL,
				V.CD_VEND,
				f.CD_ARV_MERC_FAMILIA,	
				SUM(V.QT_IT)  QTD_VD,
				SUM(V.TOT_IT) VLR_VD 
			From	
				TopShow s				
				INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA F ON
				S.CD_EMP	= F.CD_EMP AND
				S.CD_ARV_MERC_FAMILIA = F.CD_ARV_MERC_FAMILIA

				INNER JOIN V_PDV_VD_IT V ON
				S.CD_EMP	= V.CD_EMP AND
				S.CD_FILIAL = V.CD_FILIAL AND
				f.CD_PROD	= V.CD_PROD 

				AND	V.DT_VD >= S.DT_INI_TOP_SHOW
				AND V.DT_VD <= S.DT_FIM_TOP_SHOW
			group by 
					S.CD_EMP,
					S.CD_TOP_SHOW,
					S.CD_FILIAL,
					f.CD_ARV_MERC_FAMILIA,
					V.CD_VEND),
	Func as (select
				S.CD_EMP,
				S.CD_TOP_SHOW,
				S.CD_FILIAL,
				V.CD_VEND,
				SUM(V.TOT_IT) /COUNT(DISTINCT CD_VD) TKT_MD
			From	
				(SELECT DISTINCT 
						CD_EMP, 
						CD_FILIAL, 
						CD_TOP_SHOW, 
						CD_FUN,
						DT_INI_TOP_SHOW,
						DT_FIM_TOP_SHOW 
					FROM
						TopShow ) s
				INNER JOIN V_PDV_VD_IT V ON
				S.CD_EMP	= V.CD_EMP AND
				S.CD_FILIAL = V.CD_FILIAL AND
				S.CD_FUN	= V.CD_VEND
				AND	V.DT_VD >= S.DT_INI_TOP_SHOW
				AND V.DT_VD <= S.DT_FIM_TOP_SHOW
			group by 
				S.CD_EMP,
				S.CD_TOP_SHOW,
				S.CD_FILIAL,
				V.CD_VEND)
 select 
	s.CD_EMP,
	s.CD_TOP_SHOW,
	s.DS_TOP_SHOW,
	s.DT_INI_TOP_SHOW,
	s.DT_FIM_TOP_SHOW,
	s.CD_FILIAL, 
	s.CD_ARV_MERC_FAMILIA,
	S.DS_ARV_MERC_FAMILIA,
	'NAO DEFINIDO' DS_ARV_MERC_CLASSIFICACAO,
	s.PERC_META, 
	S.QTDE_META, 
	S.CD_FUN,	
	S.NM_FUN,
	ISNULL(Func.TKT_MD,0) TKT_MD,
	ISNULL(S.PERC_META_VEND,0) PERC_META_VEND,
	ROUND((ISNULL(S.PERC_META_VEND,0)/100.000) * S.QTDE_META,0) QTDE_META_VEND,
	SUM(ISNULL(V.QTD_VD,0)) QTD_VD,
	SUM(ISNULL(V.VLR_VD,0)) VLR_TOT_VD 
 From	
	TopShow s
	LEFT JOIN Venda v ON
	S.CD_EMP	  = V.CD_EMP AND
	S.CD_TOP_SHOW = V.CD_TOP_SHOW AND
	S.CD_FILIAL   = V.CD_FILIAL AND
	S.CD_FUN	  = V.CD_VEND	AND
	S.CD_ARV_MERC_FAMILIA	  = V.CD_ARV_MERC_FAMILIA
	LEFT JOIN Func on 
	S.CD_EMP	= FUNC.CD_EMP AND
	S.CD_TOP_SHOW = FUNC.CD_TOP_SHOW AND
	S.CD_FILIAL	= FUNC.CD_FILIAL AND
	s.CD_FUN	= FUNC.CD_VEND  
	  
GROUP BY 
	s.CD_EMP,
	s.CD_TOP_SHOW,
	s.DS_TOP_SHOW,
	s.DT_INI_TOP_SHOW,
	s.DT_FIM_TOP_SHOW,
	s.CD_FILIAL, 
	s.CD_ARV_MERC_FAMILIA,
	S.DS_ARV_MERC_FAMILIA,
	S.CD_FUN,
	S.NM_FUN,
	Func.TKT_MD ,
	S.PERC_META_VEND,
	S.QTDE_META,
	s.PERC_META