IF EXISTS (SELECT * FROM SYS.views WHERE NAME = 'V_EST_PROD_CUSTO_PRICING')
BEGIN
	DROP VIEW V_EST_PROD_CUSTO_PRICING;
END
GO
--select TOP 10 * from V_EST_PROD_CUSTO_PRICING
CREATE VIEW [dbo].[V_EST_PROD_CUSTO_PRICING]
AS 
SELECT A.CD_PROD,
	   C.UF,
	   (SUM(A.VLR_CUSTO) / D.QNT) AS VLR_CUSTO_PONDERADO,
	   (SUM(A.VLR_CUSTO_MD) / D.QNT) AS VLR_CUSTO_MD_PONDERADO,
	   (SUM(A.VLR_CUSTO_GER) / D.QNT) AS VLR_CUSTO_GER_PONDERADO 
FROM EST_PROD_CPL A 
INNER JOIN	PRC_FILIAL B ON 
A.CD_EMP = B.CD_EMP AND
A.CD_FILIAL = B.CD_FILIAL 
INNER JOIN GLB_CID C ON
B.CD_CID = C.CD_CID
INNER JOIN 
(SELECT
	C.UF,
	COUNT(B.CD_FILIAL) AS QNT
FROM
PRC_FILIAL B 
INNER JOIN GLB_CID C ON
B.CD_CID = C.CD_CID
GROUP BY C.UF) D ON 
D.UF = C.UF
GROUP BY A.CD_PROD, C.UF,D.QNT
GO


