Buscar Vendas Clientes PJ

Tarefa 2138.sql

SELECT storeno AS Loja, id_pessoa AS 'Codigo Cliente', cliente AS Cliente,
       email AS Email, IF(isnull(ddd), '', IF(ddd = 0, '', ddd)) AS DDD, IF(isnull(telefone), '', telefone) AS Telefone,
       (
         SELECT REPLACE(ROUND(SUM(xa2.price / 100 * (abs(xa2.qtty) / 1000)), 2), '.', ',')
         FROM sqlpdv.pxa pxa
           INNER JOIN sqldados.xalog2 xa2 ON (xa2.storeno = pxa.storeno AND xa2.pdvno = pxa.pdvno AND xa2.xano = pxa.xano)
         WHERE xa2.custno = id_pessoa
               AND xa2.xano = transacaoUltimaCompra
         AND xa2.qtty > 0
       ) AS 'Ultima Compra', acumulado AS Acumulado
FROM (
       SELECT
         MAX(storeno) as storeno, id_pessoa, cliente, email, ddd, telefone, MAX(pdvno) as pdvno,
         REPLACE(ROUND(SUM(acumulado), 2), '.', ',')      AS acumulado,
         MAX(transacaoUltimaCompra)                       AS transacaoUltimaCompra
       FROM (
              SELECT
                pxa.storeno,pxa.pdvno,pxa.xano,pxa.date,pe.id_pessoa,
                pe.nome_pessoa cliente,pe.email,pe.ddd_telefone ddd,pe.telefone,
                SUM(xa.price / 100 * (abs(xa.qtty) / 1000) ) AS acumulado,
                MAX(pxa.xano) AS transacaoUltimaCompra
              FROM sqlpdv.pxa pxa
                INNER JOIN webpdv.pessoas pe ON (pe.custno = pxa.custno AND pe.id_tipo_pessoa = 'J')
                INNER JOIN sqldados.xalog2 xa ON (xa.storeno = pxa.storeno AND xa.pdvno = pxa.pdvno AND xa.xano = pxa.xano)
              WHERE pxa.date BETWEEN 20150101 AND 20160310
              GROUP BY pxa.xano
            ) t1
       GROUP BY t1.id_pessoa
     ) t2
GROUP BY t2.id_pessoa
ORDER BY t2.storeno
;