IF EXISTS (SELECT * FROM sysobjects O, sysusers U
          WHERE
            o.name = 'EXTRATO_CONSUMIDOR_VIEW'  and
            type = 'V'                  and
            u.uid = o.uid               and
            u.name = USER)
   DROP VIEW EXTRATO_CONSUMIDOR_VIEW;
GO

CREATE VIEW EXTRATO_CONSUMIDOR_VIEW
(CDCLIENTE,NRINSJURCLIE,IDTPIJURCLIE,NMFANTCLIE,CDCONSUMIDOR,NMCONSUMIDOR,CDTIPOCONS,CDCCUSCLIE,VRPESUBCONS,
 MAXDTMOVCLI,VRMOVCLICONSUMO,VRMOVCLIOUTR,VRCREDITOS, VRAJUSTEDEBITOS,VRMOVPESCONSUMO,VRMOVPESOUTR,VRCREDITOSPES,VRAJUSTEDEBPES,
 VRSALDOCONS, VRSALDPECONS, DTMOVCLI) AS
   SELECT A.CDCLIENTE   , A.NRINSJURCLIE,   A.IDTPIJURCLIE, A.NMFANTCLIE,
          A.CDCONSUMIDOR, A.NMCONSUMIDOR,   A.CDTIPOCONS  , A.CDCCUSCLIE, A.VRPESUBCONS,
          A.MAXDTMOVCLI , A.VRMOVCLICONSUMO,A.VRMOVCLIOUTR, A.VRCREDITOS, A.VRAJUSTEDEBITOS,A.VRMOVPESCONSUMO,
          A.VRMOVPESOUTR, A.VRCREDITOSPES,A.VRAJUSTEDEBPES,
          M.VRSALDOCONS , M.VRSALDPECONS, A.DTMOVCLI
   FROM
      MOVICLIE M,
      (SELECT M.CDCLIENTE, CL.NRINSJURCLIE, CL.IDTPIJURCLIE, CL.NMFANTCLIE,
              M.CDCONSUMIDOR, C.NMCONSUMIDOR, C.CDTIPOCONS, C.CDCCUSCLIE, C.VRPESUBCONS,
              MAX(M.DTMOVCLI) AS MAXDTMOVCLI, CONVERT(DATETIME,CONVERT(VARCHAR,M.DTMOVCLI,103)) AS DTMOVCLI,
              SUM(CASE M.NRSEQVENDA 
                    WHEN NULL THEN 0
                    WHEN 'D'  THEN (-1)*M.VRMOVCLI
                  END) AS VRMOVCLICONSUMO,
              SUM(CASE M.IDTIPMOCVLI
                   WHEN 'C' THEN M.VRMOVCLI
                   WHEN 'A' THEN (-1)*M.VRMOVCLI
                   ELSE CASE M.IDTIPMOCVLI+ISNULL(M.NRSEQVENDA,'D') 
                          WHEN 'DD' THEN (-1)*M.VRMOVCLI
                        END
                   END) AS VRMOVCLIOUTR,
              SUM(CASE M.IDTIPMOCVLI
                    WHEN 'C' THEN M.VRMOVCLI
                  END) AS VRCREDITOS,
              SUM(CASE M.IDTIPMOCVLI
                    WHEN 'A' THEN (-1)*M.VRMOVCLI
                    ELSE (CASE M.IDTIPMOCVLI+ISNULL(M.NRSEQVENDA,'D')
                           WHEN 'DD' THEN (-1)*M.VRMOVCLI
                           ELSE 0
                         END)
                    END) AS VRAJUSTEDEBITOS,
              SUM(CASE M.NRSEQVENDA
                    WHEN NULL THEN 0
                    ELSE (CASE M.IDTIPMOCVLI
                            WHEN 'D' THEN (-1)*M.VRMOVPESCONS
                          END)
                  END) AS VRMOVPESCONSUMO,
              SUM(CASE M.IDTIPMOCVLI
                    WHEN 'C' THEN M.VRMOVPESCONS
                    WHEN 'A' THEN (-1)*M.VRMOVPESCONS
                    ELSE (CASE M.IDTIPMOCVLI+ISNULL(M.NRSEQVENDA,'D')
                           WHEN 'DD' THEN (-1)*M.VRMOVPESCONS
                           ELSE 0
                         END) 
                  END) AS VRMOVPESOUTR,
              SUM(CASE M.IDTIPMOCVLI
                    WHEN 'C' THEN M.VRMOVPESCONS
                  END) AS VRCREDITOSPES,
              SUM(CASE M.IDTIPMOCVLI
                    WHEN 'A' THEN (-1)*M.VRMOVPESCONS
                    ELSE (CASE M.IDTIPMOCVLI+ISNULL(M.NRSEQVENDA,'D')
                           WHEN 'DD' THEN (-1)*M.VRMOVPESCONS
                           ELSE 0
                         END)
                  END) AS VRAJUSTEDEBPES
       FROM MOVICLIE M, CONSUMIDOR C, CLIENTE CL
       WHERE (M.CDCLIENTE    = CL.CDCLIENTE  ) AND
             (C.CDCLIENTE    = M.CDCLIENTE   ) AND
             (C.CDCONSUMIDOR = M.CDCONSUMIDOR)
       GROUP BY M.CDCLIENTE, CL.NRINSJURCLIE, CL.IDTPIJURCLIE, CL.NMFANTCLIE,
                M.CDCONSUMIDOR, C.NMCONSUMIDOR, C.CDTIPOCONS, C.CDCCUSCLIE,C.VRPESUBCONS,CONVERT(DATETIME,CONVERT(VARCHAR,M.DTMOVCLI,103))
       ) A
   WHERE M.CDCLIENTE    = A.CDCLIENTE
     AND M.CDCONSUMIDOR = A.CDCONSUMIDOR
     AND M.DTMOVCLI     = A.MAXDTMOVCLI
GO