Array
(
)

SQL UNION E GROUPBY

Roberto
   - 02 jan 2015

Olá pessoal, preciso de ajuda com uma instrução SQL que estou com dificuldade de acertar o resultado.
USO INTERBASE 6.5.
Preciso fazer uma consulta SQL onde me retorne o valor da comissão do vendedor abatendo as devoluções feitas no mesmo período.
Segue SQL e o resultado obtido:
#Código

SELECT TAB026.nvendedor, Tab017.tdescricao, SUM(TAB027.ntotal * (TAB006.ncomissao)/100) as Comissao
FROM TAB017, TAB027, TAB026, TAB006
WHERE (Tab017.chave = Tab026.nvendedor)
  AND (Tab027.nnumero = Tab026.chave)
  AND (TAB027.nproduto = TAB006.nproduto)
  AND (TAB027.ttabela = Tab006.tdescricao)
  AND (Tab026.DDATA BETWEEN '29.12.2014 00:00' AND '29.12.2014 23:59')
  AND tab026.nempresa = 1
  AND TAB026.tcfop = '5.102'
  AND tab026.ndocumentoprincipal IS NOT NULL
  Group BY TAB026.nvendedor, Tab017.tdescricao
union
SELECT Tab026.nvendedor, TAB017.tdescricao,
      (SUM(TAB083.ntotal * (TAB006.ncomissao)/100)*-1) as ComissaoDV
FROM tab082,  TAB083, TAB026, TAB017, TAB006
where TAB082.chave = Tab083.nnumero
  AND TAB026.chave = TAB082.npedido
  AND TAB017.chave = TAB026.nvendedor
  AND TAB006.tdescricao = TAB083.ttabela
  AND TAB006.nproduto = TAB083.nproduto
  AND (Tab082.DDATA BETWEEN '29.12.2014 00:00' AND '29.12.2014 23:59')
  Group BY TAB026.nvendedor, Tab017.tdescricao

O Resultado:
Resultado Sql (Clique na imagem para abrir em uma nova janela)
Preciso que a coluna comissão tenha o resultado da comissão devida já descontando o valor negativo referente a devolução no período.
Agradeço desde já a ajuda.
Abraço

Alex Lekao
   - 03 jan 2015

Oi Roberto, bom dia!!!

Acredito que teria uma forma melhor de fazer, mas vou tentar colocar algo para resolver mais rapidamente e assim vc testa e ve se atende, ai depois procura uma solucao mais adequada.

#Código

SELECT
   NVENDEDOR,
   TDESCRICAO,
   SUM(COMISSAO) AS COMISSAO
FROM (
SELECT TAB026.nvendedor, Tab017.tdescricao, SUM(TAB027.ntotal * (TAB006.ncomissao)/100) as Comissao
FROM TAB017, TAB027, TAB026, TAB006
WHERE (Tab017.chave = Tab026.nvendedor)
  AND (Tab027.nnumero = Tab026.chave)
  AND (TAB027.nproduto = TAB006.nproduto)
  AND (TAB027.ttabela = Tab006.tdescricao)
  AND (Tab026.DDATA BETWEEN ''29.12.2014 00:00'' AND ''29.12.2014 23:59'')
  AND tab026.nempresa = 1
  AND TAB026.tcfop = ''5.102''
  AND tab026.ndocumentoprincipal IS NOT NULL
  Group BY TAB026.nvendedor, Tab017.tdescricao
union
SELECT Tab026.nvendedor, TAB017.tdescricao,
      (SUM(TAB083.ntotal * (TAB006.ncomissao)/100)*-1) as Comissao
FROM tab082,  TAB083, TAB026, TAB017, TAB006
where TAB082.chave = Tab083.nnumero
  AND TAB026.chave = TAB082.npedido
  AND TAB017.chave = TAB026.nvendedor
  AND TAB006.tdescricao = TAB083.ttabela
  AND TAB006.nproduto = TAB083.nproduto
  AND (Tab082.DDATA BETWEEN ''29.12.2014 00:00'' AND ''29.12.2014 23:59'')
  Group BY TAB026.nvendedor, Tab017.tdescricao
) AS TST
GROUP BY    NVENDEDOR,TDESCRICAO,


Estou mais acostumado a fazer isso no SQL Server, nao sei se tem muita diferenca em relacao ao interbase, acho que nao, estou tomando o valor da comissao de devolucao como sendo apresentado negativamente(nao avaliei profundamente o codigo), mudei o nome para comissao para ficar igual ao do primeiro script.

Espero que ajude.

Abraco.

Roberto
   - 03 jan 2015

Bom dia Alex, obrigado pela ajuda.

Infelizmente o Interbase não permite a Select dentro de outra Select. Eu tentei algo parecido antes e é retornado o erro na linha do segundo Select. Eu testei sua versão e não funcionou por esse motivo.

Obrigado pela ajuda mesmo assim.

Abraço

Alex Lekao
   - 03 jan 2015

Qual foi o erro?

Na pior das hipoteses crie uma view que faca esses selects seus e manipule a sua view no select, vc tera o mesmo resultado final, acredito eu. rsrsr

Abraco.

Roberto
   - 03 jan 2015

Rsrsrs....Segue o erro. O Pior que eu só posso fazer consulta neste BD. O BD é utilizado por um software de terceiro e eu uso os dados para gerar relatórios personalizados e "não tenho acesso" (Não posso) criar nada :(.

Resultado Sql Alex (Clique na imagem para abrir em uma nova janela)

Abraço

Alex Lekao
   - 03 jan 2015

Cara, complicou.

Vamos tentar de outro jeito entao. rsrsr

Como vc precisaria que ficasse apresentado o relatorio?

Sugiro fazer subselects entao.

Roberto
   - 03 jan 2015

Pois é rsrs...

Preciso que a consulta resulte como a figura abaixo. Mas neste caso está sem o desconto da comissão negativa referente as devoluções. Como está no meu primeiro post.

Esperado (Clique na imagem para abrir em uma nova janela)

Valew...

Alex Lekao
   - 03 jan 2015

vc precisa apresentar apenas uma coluna com a comissao ja subtraida, ou vc precisa apresentar tudo, comissao, comissaodev e comissao liq?

Roberto
   - 03 jan 2015

Só uma coluna com a comissão já subtraída!

Alex Lekao
   - 03 jan 2015

Otimo.

Tente fazer o seguinte

Vamos ver se assim da certo, nao conseguindo pensar em algo melhor para te ajudar. rsrsr

Monte uma consulta dos vendedores e uma subconsulta referenciando o vendedor da consulta principal e um outra subtraindo as duas referenciando o vendeodr da prncipal.

Por exemplo:
#Código

SELECT
   CODIGO,
   VENDEDOR,
   SUM((SELECT
               SUM(TOTAL)
            FROM VENDAS
            WHERE VENDAS.CODIGO = VENDEDORES.CODIGO
               AND DATA BETWEEN 'DATA INICIO' AND 'DATA FIM'
            GROUP BY VENDAS.CODIGO,VENDAS.VENDEDOR)-
            SELECT
               SUM(TOTAL)
            FROM DEVOLUCAO
            WHERE VENDAS.CODIGO = VENDEDORES.CODIGO
               AND DATA BETWEEN 'DATA INICIO' AND 'DATA FIM')
            GROUP BY DEVOLUCAO.CODIGO,DEVOLUCAO.VENDEDOR) AS COMISSAO
FROM VENDEDORES
GROUP BY CODIGO,VENDEDOR


Costumo fazer algumas assim no SQL Server e da certo, vamos ver se dara certo para vc, fica mais pesado, mas nao to conseguindo elaborar nada mais sofisiticado e performatico no momento. rsrsr

Abraco.

Marisiana
   - 03 jan 2015

Roberto, estive analisando o código e não entendi o porquê de fazer dois select e utilizar o UNION...
Todas as tabelas possuem ligações entre elas que permitem criar um único select para retornar as informações que você precisa.
Veja um exemplo que fiz com base na estrutura q vc apresentou na questão inicial:
#Código

SELECT res.nvendedor,
              res.tdescricao,
              (res.comissao - res.comissao2) as comissao
FROM (
SELECT TAB026.nvendedor, 
               Tab017.tdescricao, 
               SUM(TAB027.ntotal * (TAB006.ncomissao)/100) as Comissao,     
               (SUM(TAB083.ntotal * (TAB006.ncomissao)/100)  * - 1) as Comissao2 
FROM TAB017
INNER JOIN TAB026
ON Tab017.chave = Tab026.nvendedor
 INNER JOIN TAB027 
ON Tab027.nnumero = Tab026.chave
AND TAB027.ttabela = Tab006.tdescricao
INNER JOIN TAB006
ON TAB006.nproduto = TAB027.nproduto 
INNER JOIN TAB083
ON  TAB083.ttabela = TAB006.tdescricao 
 AND TAB083.nproduto = TAB006.nproduto 
INNER JOIN tab082
ON TAB082.chave = Tab083.nnumero
AND TAB082.npedido = TAB026.chave 
INNER JOIN TAB017
ON TAB017.chave = TAB026.nvendedor
WHERE  Tab026.DDATA BETWEEN ''29.12.2014 00:00'' AND ''29.12.2014 23:59''
AND Tab082.DDATA BETWEEN ''29.12.2014 00:00'' AND ''29.12.2014 23:59''
  AND tab026.nempresa = 1
  AND TAB026.tcfop = ''5.102''
  AND tab026.ndocumentoprincipal IS NOT NULL
  Group BY TAB026.nvendedor, 
                      Tab017.tdescricao
    ) res
GROUP BY res.nvendedor,
                      res.tdescricao


A menos que exista alguma condição especial a ser atendida e q, por isso, precise fazer o selects separados, pode ser q a minha idéia atenda a necessidade.

Roberto
   - 03 jan 2015

Olá Marisiana, agradeço o tempo disponibilizado a me ajudar. Vou testar esse código e te passo o feedback na segunda-feira. Esse Select que VC colocou após o From o interbase não aceita no script mas eu já tenho outra alternativa que o Alex-Lekao me passou.

Obrigado bom final de semana.

Abraço

Marisiana
   - 04 jan 2015

Nesse caso, você pode fazer o cálculo direto sem fazer o subselect...
#Código

SELECT TAB026.nvendedor, 
               Tab017.tdescricao, 
               SUM(TAB027.ntotal * (TAB006.ncomissao)/100) as Comissao,     
               (SUM(TAB083.ntotal * (TAB006.ncomissao)/100)  * - 1) as Comissao2,
               ( SUM(TAB027.ntotal * (TAB006.ncomissao)/100) - (SUM(TAB083.ntotal * (TAB006.ncomissao)/100)  * - 1) ) as resultado
FROM TAB017
INNER JOIN TAB026
ON Tab017.chave = Tab026.nvendedor
 INNER JOIN TAB027 
ON Tab027.nnumero = Tab026.chave
AND TAB027.ttabela = Tab006.tdescricao
INNER JOIN TAB006
ON TAB006.nproduto = TAB027.nproduto 
INNER JOIN TAB083
ON  TAB083.ttabela = TAB006.tdescricao 
 AND TAB083.nproduto = TAB006.nproduto 
INNER JOIN tab082
ON TAB082.chave = Tab083.nnumero
AND TAB082.npedido = TAB026.chave 
INNER JOIN TAB017
ON TAB017.chave = TAB026.nvendedor
WHERE  Tab026.DDATA BETWEEN ''''29.12.2014 00:00'''' AND ''''29.12.2014 23:59''''
AND Tab082.DDATA BETWEEN ''''29.12.2014 00:00'''' AND ''''29.12.2014 23:59''''
  AND tab026.nempresa = 1
  AND TAB026.tcfop = ''''5.102''''
  AND tab026.ndocumentoprincipal IS NOT NULL
  Group BY TAB026.nvendedor, 
                      Tab017.tdescricao


Só tem q ver se funciona...

Roberto
   - 05 jan 2015

Alex e Marisiana, bom dia e obrigado pela ajuda. Estamos quase conseguindo. Veja o resultado que deu e o que acho que está afetando o resultado correto.

A Consulta

#Código

SELECT TAB026.nvendedor, TAB017.tdescricao,
       SUM(TAB027.ntotal  * (TAB006.ncomissao)/100) as Comissao,
       (SUM(TAB083.ntotal * (TAB006.ncomissao)/100)  * - 1) as ComissaoDev,
       (SUM(TAB027.ntotal * (TAB006.ncomissao)/100) -
       (SUM(TAB083.ntotal * (TAB006.ncomissao)/100)  * - 1) ) as resultado
FROM TAB017
INNER JOIN TAB026 ON TAB017.chave   = TAB026.nvendedor
INNER JOIN TAB027 ON Tab027.nnumero = TAB026.chave
INNER JOIN TAB006 ON TAB006.nproduto= TAB027.nproduto   AND TAB006.tdescricao = TAB027.ttabela
INNER JOIN TAB083 ON TAB083.ttabela = TAB006.tdescricao AND TAB083.nproduto   = TAB006.nproduto
INNER JOIN TAB082 ON TAB082.chave   = TAB083.nnumero
INNER JOIN TAB017 ON TAB017.chave   = TAB026.nvendedor
WHERE TAB026.DDATA BETWEEN '29.12.2014 00:00' AND '29.12.2014 23:59'
 AND TAB082.ddata BETWEEN '29.12.2014 00:00' AND '29.12.2014 23:59'
  AND TAB026.nempresa = 1
  AND TAB026.tcfop = '5.102/P'
  AND TAB026.ndocumentoprincipal IS NOT NULL
  Group BY TAB026.nvendedor, TAB017.tdescricao


Eu retirei essa ligação entre as tabelas

#Código
TAB082.npedido = TAB026.chave 


Pois se eu deixar o Resultado da consulta é NULL.
Bom retirando essa ligação foi dado o seguinte resultado:

Consulta Alterada (Clique na imagem para abrir em uma nova janela)

Está com os valores muito abaixo do que se é esperado pois o filtro de datas está determinando isso nesta parte do script:

#Código
AND TAB082.ddata BETWEEN '29.12.2014 00:00' AND '29.12.2014 23:59'


Essa TAB082 são só as devoluções geradas. E eu quero resgatar só se houver essas devoluções no período selecionado e não que ela seja um filtro que impeça que os pedidos gerados na TAB027 não sejam listados se não estiverem na TAB082. (Não sei se eu expliquei corretamente rsrsrs).

No entanto o que eu constatei é que a TAB083 que são os produtos das devoluções, essa não possui um campo de DATA. Dependendo da TAB082 para que eu possa setar esse periodo. Diferente do que ocorre com a TAB027 que são os produtos dos pedidos, essa possui um campo data que une ela a TAB026.

Resumindo, se eu deixar o código abaixo o resultado é muito menor do que o esperado para o RESULTADO.
#Código
AND TAB082.ddata BETWEEN '29.12.2014 00:00' AND '29.12.2014 23:59'


...Se eu retirar esse código acima o RESULTADO é muito alem do esperado. Penso eu porque falta a ligação entre a TAB026 E A TAB082 mas se eu há fizer o resultado da consulta é NULL. Não sei mais o que fazer.

Muito obrigado pela ajuda de vocês e pelo tempo que estão me disponibilizando.
Forte Abraço
Tenham uma semana abençoada!

Marisiana
   - 05 jan 2015

Nesse caso, é necessário utilizar LEFT JOIN no lugar de INNER JOIN nas tabelas que podem retornar ou não retornar dados.
Pelo que entendi são as tabelas TAB083 e TAB082

#Código

SELECT TAB026.nvendedor, TAB017.tdescricao,
       SUM(TAB027.ntotal  * (TAB006.ncomissao)/100) as Comissao,
       (SUM(TAB083.ntotal * (TAB006.ncomissao)/100)  * - 1) as ComissaoDev,
       (SUM(TAB027.ntotal * (TAB006.ncomissao)/100) -
       (SUM(TAB083.ntotal * (TAB006.ncomissao)/100)  * - 1) ) as resultado
FROM TAB017
INNER JOIN TAB026 ON TAB017.chave   = TAB026.nvendedor
INNER JOIN TAB027 ON Tab027.nnumero = TAB026.chave
INNER JOIN TAB006 ON TAB006.nproduto= TAB027.nproduto   AND TAB006.tdescricao = TAB027.ttabela
LEFT JOIN TAB083 ON TAB083.ttabela = TAB006.tdescricao AND TAB083.nproduto   = TAB006.nproduto
LEFT JOIN TAB082 ON TAB082.chave   = TAB083.nnumero
INNER JOIN TAB017 ON TAB017.chave   = TAB026.nvendedor
WHERE TAB026.DDATA BETWEEN '29.12.2014 00:00' AND '29.12.2014 23:59'
 AND TAB082.ddata BETWEEN '29.12.2014 00:00' AND '29.12.2014 23:59'
  AND TAB026.nempresa = 1
  AND TAB026.tcfop = '5.102/P'
  AND TAB026.ndocumentoprincipal IS NOT NULL
  Group BY TAB026.nvendedor, TAB017.tdescricao


Eu não conheço a estrutura das tabelas, mas aconselho a verificar quais são as ligações entre elas (FK e PK) para ver se não falta incluir alguma delas.
Por exemplo: Existe algum relacionamento direto entre a TAB027 e a TAB082?

Alex Lekao
   - 05 jan 2015

Oi Roberto, boa tarde!!!

Hoje esta meio corrido, desculpe nao poder dar uma atencao melhor.

De uma olhada no que foi sugerido pela Marisiana, a sugestao de usar Left Join no Lugar do Inner Join, ou coisa eh que parte onde dara o resultado vc teria que tirar o menos um( - 1 ) que esta fazendo com que o resultado seja somado no lugar de ser subtraido.

De:
#Código

(SUM(TAB083.ntotal * (TAB006.ncomissao)/100)  * - 1) ) as resultado


Para:
#Código
(SUM(TAB083.ntotal * (TAB006.ncomissao)/100)) ) as resultado


Verifique que o resultado ficara correto pensando no calculo matematico para o resultado, subtracao das comissoes das devolucoes da comissoes de vendas.

Faca os ajustes que a Marisiana sugeriu e vamos ver o que resolve.

Abraco.

Roberto
   - 05 jan 2015

Alex e Marisiana, boa tarde..

Alex, já tinha visto sobre a soma do Resultado, mas obrigado por avisa.

Marisiana estou tentando fazer o que me sugeriu mas ainda sem resultado. Estou olhando as chaves conforme vc me orientou e sobre a ligação da Tab027 com a TAB082 é TAB082.npedido = TAB027.nnumero é a unica ligação entre elas que é o Numero do pedido. O que eu estou vendo aqui com mais calma e acabei achando um outro impasse.

Os pedido são gravados como PEDIDO PAI(TAB026) e PRODUTOS PEDIDO PAI(TAB027) e deste pedido pai é gerado pelo menos 1 pedido FILHO que é o pedido de ENTREGA. e as devoluções só são geradas por esses pedidos de entrega. O Problema do valor das comissões estarem alem do esperado se deve a duplicidade destes pedidos, PAI e Filho.

Eu sei que fica difícil pra vcs saberem o que ocorre devido não saber as estruturas da tabela e dos dados, por isso agradeço e muito o esforço. Estou vendo aqui como resolver isso e logo coloco o resultado.

Obrigado

Abraço

Alex Lekao
   - 05 jan 2015

Blz Roberto.

Precisando de algo posta ae, se estiver ao meu alcanco a gente ve o que consegue. rsrsr

Abraco.

Marisiana
   - 06 jan 2015

Boa tarde!!

Esses pedidos de ENTREGA são identificados como?
Eles são cadastrados na tabela q contém os pedidos FILHOS?
Existe algum campo TIPO que diferencie um do outro?

Roberto
   - 06 jan 2015

Boa tarde, então Marisiana eles a principio eu identifiquei pelo Tab026.ndocumentoprincipal is not null. Pois os pedidos filhos possuem esses campos nulos. Mas as devoluções só são geradas pelos filhos então na consulta separada de comissão funciona e na de devolução também mas qndo tento unir em uma única consulta a situação do campo tab026.ndocumentoprincipal is null ai então bloqueia a consulta das devoluções. Eu acho que consegui te explicar rsrs. E não achei outro campo para diferenciar esses pedidos para não gerar comissão em duplicidade, pedido pai e pedido filho.

Espero que não tenha te confundido mais. Olhe no primeiro post as consultas ligadas pelo union. VC entenderá melhor.

Obrigado

Abraço

Marisiana
   - 06 jan 2015

Eu acho que entendi...
Você identifica os documentos que são devoluções atrás desses dois filtros:?
#Código

  AND TAB026.tcfop = ''5.102/P''
  AND TAB026.ndocumentoprincipal IS NOT NULL