IF EXISTS
(
    SELECT *
    FROM SYS.VIEWS
    WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_PDV_VD_IT_CLIENTE_ULTIMAS_VENDAS]')
)
    DROP VIEW [DBO].[V_PDV_VD_IT_CLIENTE_ULTIMAS_VENDAS];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO


--select * from V_PDV_VD_IT_CLIENTE_ULTIMAS_VENDAS WHERE CD_EMP = 1 AND CD_CLI = 1808023
CREATE VIEW [dbo].[V_PDV_VD_IT_CLIENTE_ULTIMAS_VENDAS]
AS
     SELECT TMP.*,
            P.DS_PROD AS PRODUTO,
            CASE
                WHEN PR.VLR_TABELA > PR.VLR_OFERTA
                THEN PR.VLR_TABELA
                ELSE PR.VLR_OFERTA
            END AS PRECO
     FROM
     (
         SELECT V.CD_EMP AS CD_EMP,
                V.CD_FILIAL AS CD_FILIAL,
                V.CD_CLI AS CD_CLI,
                I.CD_PROD CODIGO,
                COUNT(*) AS QTD
         FROM PDV_VD V
              INNER JOIN PDV_VD_IT I ON V.CD_EMP = I.CD_EMP
                                        AND V.CD_FILIAL = I.CD_FILIAL
                                        AND V.CD_VD = I.CD_VD
                                        AND V.DT_VD = I.DT_VD
         WHERE I.CD_EMP = 1
               AND I.DT_VD > DATEADD(DAY, -91, GETDATE())
			AND V.ST_VD=0
			AND I.ST_IT=1
         GROUP BY V.CD_EMP,
                  V.CD_CLI,
                  V.CD_FILIAL,
                  I.CD_PROD
     ) TMP
     INNER JOIN EST_PROD P ON TMP.CD_EMP = P.CD_EMP
                              AND TMP.CODIGO = P.CD_PROD
     INNER JOIN EST_PROD_PRECO PR ON TMP.CD_EMP = PR.CD_EMP
                                     AND TMP.CD_FILIAL = PR.CD_FILIAL
                                     AND TMP.CODIGO = PR.CD_PROD
							  
	WHERE  P.CD_EMP=1;
GO