Is it possible to perform a filter through a calculated column?

0

Gentlemen, I am performing the following select in my database.

SELECT TOP 5 A.APELIDO,
        A.REALIZADO,
        A.META,
        CAST((A.REALIZADO/A.META)*100 AS DECIMAL(10)) AS ACUMULADO,
        DENSE_RANK() OVER (ORDER BY ((A.REALIZADO/A.META)*100)DESC) AS RANKING

        FROM (

        SELECT

        VEN2.APELIDO,

        ((SELECT ISNULL(SUM((ITE1.QTDNEG*ITE1.VLRUNIT)-ITE1.VLRDESC-ITE1.VLRREPRED),0)
        FROM TGFCAB CAB1 (NOLOCK)
        LEFT JOIN TGFITE   ITE1 (NOLOCK) ON ITE1.NUNOTA=CAB1.NUNOTA 
        LEFT JOIN TGFVEN   VEN1 (NOLOCK) ON VEN1.CODVEND=CAB1.CODVEND
        LEFT JOIN TGFPRO   PRO1 (NOLOCK) ON PRO1.CODPROD = ITE1.CODPROD
        WHERE CAB1.TIPMOV = 'V'
        AND   CONVERT(DATE,CAB1.DTFATUR) BETWEEN '2018-10-01' AND '2019-01-31'
        AND   ITE1.CODCFO IN (5102,5403,5405,5922,6102,6108,6110,6403,6404,6922)
        AND   CAB1.STATUSNFE <> 'D'
        AND   PRO1.MARCA = 'SAMSUNG'
        AND   PRO1.CODGRUPOPROD = 1001001
        AND   VEN1.AD_CODCANAL IN (1)
        AND   VEN1.CODVEND = VEN2.CODVEND ) -
        (SELECT CONVERT(DECIMAL(10,2),ISNULL(SUM((ITE1.QTDNEG*ITE1.VLRUNIT)-ITE1.VLRDESC-ITE1.VLRREPRED) ,0))
        FROM TGFCAB CAB1 (NOLOCK)
        LEFT JOIN TGFITE   ITE1 (NOLOCK) ON ITE1.NUNOTA=CAB1.NUNOTA 
        LEFT JOIN TGFVEN   VEN1 (NOLOCK) ON VEN1.CODVEND=CAB1.CODVEND
        LEFT JOIN TGFPRO   PRO1 (NOLOCK) ON PRO1.CODPROD = ITE1.CODPROD
        WHERE CAB1.TIPMOV = 'D'
        AND   CONVERT(DATE,CAB1.DTFATUR) BETWEEN '2018-10-01' AND '2019-01-31'
        AND   ITE1.CODCFO IN (1202,1411,2202,2204,2411)
        AND   CAB1.STATUSNOTA = 'L'
        AND   PRO1.MARCA = 'SAMSUNG'
        AND   PRO1.CODGRUPOPROD = 1001001
        AND   VEN1.AD_CODCANAL IN (1)
        AND   VEN1.CODVEND = VEN2.CODVEND

         )) AS REALIZADO,

        (SELECT 
        SUM(MET.PREVREC) AS META_VN
        FROM TGMMET MET
        LEFT JOIN TGFVEN   VEN (NOLOCK) ON VEN.CODVEND = MET.CODVEND
        WHERE   MET.CODMETA = 6
        AND     VEN.AD_CODCANAL IN (1)
        AND    VEN.CODVEND = VEN2.CODVEND

        ) AS META


        FROM TGFVEN VEN2
        WHERE VEN2.AD_CODCANAL IN (1)--VN
        )A

The result of this select brings the result below. In the column nickname brings the name of the people, I pointed out because it does not make sense to show them.

I want to be able to filter through the Ranking column which is a calculated column. For example, I would like to pass a Where / And on it, is it possible?

    
asked by anonymous 09.10.2018 / 14:42

1 answer

1

I understand this is what you need:

SELECT *
FROM (
SELECT TOP 5 A.APELIDO,
        A.REALIZADO,
        A.META,
        CAST((A.REALIZADO/A.META)*100 AS DECIMAL(10)) AS ACUMULADO,
        DENSE_RANK() OVER (ORDER BY ((A.REALIZADO/A.META)*100)DESC) AS RANKING

        FROM (

        SELECT

        VEN2.APELIDO,

        ((SELECT ISNULL(SUM((ITE1.QTDNEG*ITE1.VLRUNIT)-ITE1.VLRDESC-ITE1.VLRREPRED),0)
        FROM TGFCAB CAB1 (NOLOCK)
        LEFT JOIN TGFITE   ITE1 (NOLOCK) ON ITE1.NUNOTA=CAB1.NUNOTA 
        LEFT JOIN TGFVEN   VEN1 (NOLOCK) ON VEN1.CODVEND=CAB1.CODVEND
        LEFT JOIN TGFPRO   PRO1 (NOLOCK) ON PRO1.CODPROD = ITE1.CODPROD
        WHERE CAB1.TIPMOV = 'V'
        AND   CONVERT(DATE,CAB1.DTFATUR) BETWEEN '2018-10-01' AND '2019-01-31'
        AND   ITE1.CODCFO IN (5102,5403,5405,5922,6102,6108,6110,6403,6404,6922)
        AND   CAB1.STATUSNFE <> 'D'
        AND   PRO1.MARCA = 'SAMSUNG'
        AND   PRO1.CODGRUPOPROD = 1001001
        AND   VEN1.AD_CODCANAL IN (1)
        AND   VEN1.CODVEND = VEN2.CODVEND ) -
        (SELECT CONVERT(DECIMAL(10,2),ISNULL(SUM((ITE1.QTDNEG*ITE1.VLRUNIT)-ITE1.VLRDESC-ITE1.VLRREPRED) ,0))
        FROM TGFCAB CAB1 (NOLOCK)
        LEFT JOIN TGFITE   ITE1 (NOLOCK) ON ITE1.NUNOTA=CAB1.NUNOTA 
        LEFT JOIN TGFVEN   VEN1 (NOLOCK) ON VEN1.CODVEND=CAB1.CODVEND
        LEFT JOIN TGFPRO   PRO1 (NOLOCK) ON PRO1.CODPROD = ITE1.CODPROD
        WHERE CAB1.TIPMOV = 'D'
        AND   CONVERT(DATE,CAB1.DTFATUR) BETWEEN '2018-10-01' AND '2019-01-31'
        AND   ITE1.CODCFO IN (1202,1411,2202,2204,2411)
        AND   CAB1.STATUSNOTA = 'L'
        AND   PRO1.MARCA = 'SAMSUNG'
        AND   PRO1.CODGRUPOPROD = 1001001
        AND   VEN1.AD_CODCANAL IN (1)
        AND   VEN1.CODVEND = VEN2.CODVEND

         )) AS REALIZADO,

        (SELECT 
        SUM(MET.PREVREC) AS META_VN
        FROM TGMMET MET
        LEFT JOIN TGFVEN   VEN (NOLOCK) ON VEN.CODVEND = MET.CODVEND
        WHERE   MET.CODMETA = 6
        AND     VEN.AD_CODCANAL IN (1)
        AND    VEN.CODVEND = VEN2.CODVEND

        ) AS META


        FROM TGFVEN VEN2
        WHERE VEN2.AD_CODCANAL IN (1)--VN
        )A
) XX
WHERE XX.RANKING > 3
    
09.10.2018 / 14:47