IF EXISTS(SELECT * FROM SYSOBJECTS O, SYSUSERS U
          WHERE O.NAME = 'PERCENTUAL_ALT_CARD' AND
                TYPE = 'FN'                    AND
                U.UID = O.UID                  AND
                U.NAME = USER)

DROP FUNCTION PERCENTUAL_ALT_CARD
GO

CREATE FUNCTION PERCENTUAL_ALT_CARD(
   @P_CDFILIAL     VARCHAR(4),
   @P_CDSERVFILI   VARCHAR(5),
   @P_CDPRATO      VARCHAR(10),
   @P_CDFILAUXPRAT VARCHAR(4),
   @P_DTMESCARD    DATETIME) RETURNS NUMERIC(10,2)
AS
BEGIN
   DECLARE @V_NRPRATOMAT INT;
   DECLARE @V_NRPRATO    INT;
   DECLARE @V_QTDEPRATO  INT;
   DECLARE @V_RESULT     DOUBLE PRECISION;

   SET  @V_RESULT = 0;

   SELECT @V_NRPRATOMAT = ISNULL((SELECT COUNT(1)
                                    FROM COMPCARD CP, CARDADIA CA
                                   WHERE CA.CDFILIAL = @P_CDFILIAL
                                     AND CA.CDSERVFILI = @P_CDSERVFILI
                                     AND CA.DTCARDAPIO BETWEEN @P_DTMESCARD AND DATEADD(MM,1,@P_DTMESCARD)-DAY(@P_DTMESCARD)
                                     AND CA.CDFILIAL = CP.CDFILIAL
                                     AND CA.NRCARDAPIO = CP.NRCARDAPIO
                                     AND CP.CDPRATOMAT = @P_CDPRATO
                                     AND CP.CDFILAUXPRATMAT = @P_CDFILAUXPRAT
                                     AND NOT EXISTS(SELECT X.CDPRATO
                                                      FROM COMPCARD X, CARDADIA Y
                                                     WHERE X.CDFILIAL = Y.CDFILIAL
                                                       AND X.NRCARDAPIO = Y.NRCARDAPIO
                                                       AND Y.CDFILIAL = CA.CDFILIAL
                                                       AND Y.CDSERVFILI = CA.CDSERVFILI
                                                       AND Y.DTMESCARD BETWEEN CA.DTMESCARD AND DATEADD(MM,1,CA.DTMESCARD)-DAY(CA.DTMESCARD)
                                                       AND X.CDPRATOMAT = CP.CDPRATO
                                                       AND X.CDFILAUXPRAT = CP.CDFILAUXPRAT
                                                       AND X.CDPRATO = CP.CDPRATOMAT
                                                   ) 

                                   GROUP BY CP.CDPRATOMAT), 0);
                                   
   SELECT @V_NRPRATO = ISNULL((SELECT COUNT(1) AS NRPRATO
                                 FROM COMPCARD CP, CARDADIA CA
                                WHERE CA.CDFILIAL = @P_CDFILIAL
                                  AND CA.CDSERVFILI = @P_CDSERVFILI
                                  AND CA.DTCARDAPIO BETWEEN @P_DTMESCARD AND DATEADD(MM,1,@P_DTMESCARD)-DAY(@P_DTMESCARD)
                                  AND CA.CDFILIAL = CP.CDFILIAL
                                  AND CA.NRCARDAPIO = CP.NRCARDAPIO
                                  AND CP.CDPRATO = @P_CDPRATO
                                  AND CP.CDFILAUXPRAT = @P_CDFILAUXPRAT
                                  GROUP BY CP.CDPRATO), 0);

      IF (@V_NRPRATOMAT > 0) AND (@V_NRPRATO = 0) BEGIN
         SET @V_RESULT = 100;
      END
      ELSE BEGIN
         SET @V_QTDEPRATO = @V_NRPRATOMAT + @V_NRPRATO;
         IF (@V_QTDEPRATO <> 0) BEGIN
            SET @V_RESULT = (@V_NRPRATOMAT * 100) / @V_QTDEPRATO;
         END
         ELSE BEGIN
            SET @V_RESULT = 0;
        END;
      END;
   RETURN(@V_RESULT);
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'PERCENTUAL_ALT_CARD','5.20.000';
GO