0
I have to make a report that shows the main customers of each seller (the top 10 customers who spent on each seller)
I am using the Oracle database to make this query.
Each seller has more than 40 customers, I need to limit 10 customers to all sellers.
The select I’m wearing:
SELECT * FROM ( SELECT FILIAL, vendedor, CODV, cliente,SUM(valor), PERIODO fROM (SELECT ITPED.CODFIL FILIAL,
ITPED.CODVENDR CODV,
MAX(VEND.NOME) vendedor,
cliente.nomcli cliente,
SUM(ITPED.VLTOTITEM) VALOR,
TO_CHAR(ITPED.DTPEDIDO, 'mm/yyyy') PERIODO
FROM MOV_ITPED ITPED,
CAD_ITPROD ITPROD,
VEN_VEND VEND,
cad_cliente cliente
WHERE ITPROD.CODITPROD = ITPED.CODITPROD AND
ITPED.CODVENDR = VEND.CODVENDR AND
ITPED.CODFIL = VEND.CODFIL AND
itped.codcli = cliente.codcli AND
ITPED.DTPEDIDO BETWEEN '01/03/2019' AND '18/04/2019' AND
ITPED.CODFIL = 3 AND ITPED.STATUS <> 9
GROUP BY ITPED.CODFIL,
ITPED.CODVENDR,
ITPED.DTPEDIDO,
cliente.nomcli
UNION ALL
SELECT ITSAIDA.CODFIL FILIAL,
ITSAIDA.CODVENDR CODV,
MAX(VEND.NOME) vendedor,
cliente.nomcli cliente,
SUM(ITSAIDA.VLTOTITEM) VALOR,
TO_CHAR(ITSAIDA.DTNOTA, 'mm/yyyy') PERIODO
FROM MOV_ITSAIDA ITSAIDA,
CAD_ITPROD ITPROD,
VEN_VEND VEND,
cad_cliente cliente
WHERE ITPROD.CODITPROD = ITSAIDA.CODITPROD AND
ITSAIDA.CODVENDR = VEND.CODVENDR AND
ITSAIDA.CODFIL = VEND.CODFIL AND
ITSAIDA.codcli = cliente.codcli AND
ITSAIDA.DTNOTA BETWEEN '01/03/2019' AND '18/04/2019' AND
ITSAIDA.CODFIL = 3 AND ITSAIDA.STATUS <> 9
GROUP BY ITSAIDA.CODFIL,
ITSAIDA.CODVENDR,
cliente.nomcli,
ITSAIDA.DTNOTA) GROUP BY FILIAL, vendedor, CODV, cliente, PERIODO ORDER BY 3 ASC, 5 DESC ) WHERE ROWNUM <= 10
I was wondering if it would be possible to make a Loop by passing the sellers' codes, because I was able to limit the amount of line the select returns.
What this query returns to you and what you want it to return?
– RXSD
It returns the seller’s branch, the seller’s name and all customers who made purchases with it, and the month the purchase was made. However have seller who made purchases for more than 40, would need to pick up type 10 customers who spent more for each seller.
– Isac Oliveira
The ROWNUM <= 10 clause does not comply?
– anonimo
Actually no, I used it to try to limit the amount of customer but it limits the entire select, in case only the first seller and its customers will appear, but the second seller does not appear.
– Isac Oliveira
https://oracle-base.com/articles/miscanalytic-functions http://www.oracletutorial.com/oracle-analytic-functions/oracle-rank/ use analytic functions, examples of this top n for any break , RANK , DENSE_RANK etc
– Motta