0
Gentlemen(s), I am performing the following select on 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 surname brings the name of people, I highlighted because it makes no sense to show them.
I want to be able to filter by the column Ranking which is a calculated column. For example, I would like to spend a Where/And on it, it is possible?
