Consulta MySql

[João Paulo Taraciuk]

Preciso fazer uma consulta que contenha vendas de um determinado período, separado por mês e por vendedor, mais ou menos dessa maneira:

Vendedor------JAN/06----FEV/06-----MAR/06----ABR/06-----MAI/06 ...Até Dezembro

Vendedor1----800.00----200.00-----500.00-----300.00-----400.00

Vendedor2----700.00----500.00-----300.00-----150.00-----200.00

-----------------1500.00---700.00-----800.00-----450.00-----600.00

---------------------------------------------------Total geral : 4050.00

Vendedor-----JAN/07----FEV/07------MAR/07----ABR/07-----MAI/07 ... Até Dezembro

Vendedor1----800.00----200.00-----500.00-----300.00-----400.00

Vendedor2----700.00----500.00-----300.00-----150.00-----200.00

-----------------1500.00---700.00-----800.00-----450.00-----600.00

---------------------------------------------------Total geral : 4050.00

As tabelas estão assim

Tabela Vendas

VD_ID_pedido

VD_DATA

VD_ID_VENDEDOR

Tabela Vendedores

ID_VENDEDOR

NOME_VENDEDOR

Tabela Items_vendas

ID_PEDIDO

TOTAL

Elas se cruzam assim Vendas.VD_ID_PEDIDO=Items_vendas.ID_PEDIDO

Vendas.VD_ID_VENDEDOR=Vendedores.ID_VENDEDOR

Mais ou menos assim, mas eu não estou conseguindo, o cliente quer que saia no mesmo relatório separado por vendedor, por mês e depois por ano, até três anos no mesmo relatório, e não estou conseguindo realizar este relatório, Utilizo o Delphi 7, Banco de dados MySql e o ZEOS para comunicação.

Gostaria de saber como eu poderia realizar esse relatório, para separar as colunas por mês e por o títuo nelas, se eu deveria utilizar uma ZQuery para cada mês, ou outra maneira, poderia ser no QReport ou mesmo em um txt.

Se alguém puder me ajudar, eu agradeço

Abraço!!!

[Micheus]

João Paulo, dê uma olhada neste tópico em Tutoriais & Dicas - Construindo Uma Consulta Sql Passo-a-passo, Obtendo uma matriz como resultado

É uma explicação passo-a-passo de como fazer este tipo de consulta. O exemplo foi desenvolvido em INTERBASE, mas o conceito envolvido é exatamente o mesmo - uso de sub-selects.

Veja se você consegue compreender. Depois podemos ir aparando as arestas.

Abraços

[João Paulo Taraciuk]

Então Micheus, antes de mais nada, obrigado pela ajuda, tentando montar a pesquisa aqui eu fiz da seguinte maneira, estou jogando um select em cada ZQuery para testar em DBGrid, a Primeira ficou assim

SELECT DISTINCT nome_vendedor,vd_data FROM //Onde nome_vendedor é da tabela nendedores e vd_data é da tabela vendas
vendedores, vendas WHERE vendas.vd_data BETWEEN '2008-01-01' AND '2008-12-31'

Aí tranquilo, apareceu lá o nome do vendedor e as datas das vendas a segunda ficou dessa maneira

SELECT nome_vendedor, SUM(TOTAL) as total_jan, vd_data //Onde TOTAL é da tabela Items_vendas
FROM items_vendas, vendedores, vendas
where extract(month from vd_data)= 1
and extract(year from vd_data)   {Aqui se eu coloco um ano por exemplo =2008, ele não retorna nada na pesquisa, da maneira que está, ele retorna o nome do vendedor, o valor total e uma data , no meu caso retornou data 02/01/2008
GROUP BY nome_vendedor

Eu não entendi como ou de onde saiu aquele SALES_REP_<MES_NOME>e aquele <DATA_INICIAL> do seu exemplo

Ei tentei colocar nome_vendedor_jan, mas como não tem este campo na tabela, não deu certo.

SELECT SALES_REP_<MES_NOME>, SUM(TOTAL_VALUE) AS TOTAL_VALUE_<MES_NOME>

FROM SALES

WHERE EXTRACT(MONT FROM SHIP_DATE) = <MES>

AND EXTRACT(YEAR FROM SHIP_DATE) = EXTRACT(YEAR FROM <DAT_INICIAL>)

GROUP BY SALES_REP

Resumindo, eu consegui fazer as duas primeiras selects, tentei baixar o exemplo para verificar, mas ele parece em formato .fdb, aí eu não consegui abrí-lo

Editado Janeiro 30, 2008 por João Paulo Taraciuk

[Micheus]

SELECT nome_vendedor, SUM(TOTAL), vd_data //Onde TOTAL é da tabela Items_vendas
FROM items_vendas, vendedores, vendas
where extract(month from vd_data)= 1
and extract(year from vd_data)   {Aqui se eu coloco um ano por exemplo =2008, ele não retorna nada na pesquisa, da maneira que está, ele retorna o nome do vendedor, o valor total e uma data , no meu caso retornou data 02/01/2008
GROUP BY nome_vendedor

nesta consulta tem um campo a mais no SELECT - vd_data. Se a totalização é por vendedor, você não pode incluir algo que vá interferir neste agrupamento - gerando várias linhas para um mesmo vendedor.

Eu não entendi como ou de onde saiu aquele SALES_REP_<MES_NOME>e aquele <DATA_INICIAL> do seu exemplo

SELECT SALES_REP_<MES_NOME>, SUM(TOTAL_VALUE) AS TOTAL_VALUE_<MES_NOME>

FROM SALES

WHERE EXTRACT(MONT FROM SHIP_DATE) = <MES>

AND EXTRACT(YEAR FROM SHIP_DATE) = EXTRACT(YEAR FROM <DAT_INICIAL>)

GROUP BY SALES_REP

Resumindo, eu consegui fazer as duas primeiras selects, tentei baixar o exemplo para verificar, mas ele parece em formato .fdb, aí eu não consegui abrí-lo

Como eu disse, o exemplo foi feito para INTERBASE/FIREBIRD, mas o conceito seria exatamente o mesmo.

Quanto a SALES_REP_<MES_NOME> e <DATA_INICIAL>, o objetivo desta consulta é trazer todas as vendas do <MES> e ano (EXTRACT(YEAR FROM <DAT_INICIAL>)) onde <DATA_INICIAL> seria data do período informado - neste caso havia sido de janeiro a dezembro de determinado ano.

Voce parou logo início da explicação. Sugiro que você tente acompanhar ela do início ao fim, para depois ir tentando implementar, porque esta parte inicial é basicamente conceitual - na sequencia eu tento explicar como será traduzida para código final. Se você continuar lendo SALES_REP_<MES_NOME>, você verá que este é apenas uma definição do padrão de nome que irei utilizar na sequencia. Posterior mente as colunas de valores serão renomeadas (aliases criados com o uso de AS) para representar uma coluna para cada mês totalizado.

Abraços

[João Paulo Taraciuk]

Olá Micheus, ainda estou tentando utilizar o seu código, que por sinal, é exatamente a consulta que eu preciso, bom, vamos lá, devido à minha falta de experiência, não estou conseguindo fazê-lo funcionar como deveria, vou passar o código da maneira que eu consegui fazer

procedure Tfrm_relatoriovendas.selectano;
begin
ZQuery_vendas.Active:=False;
ZQuery_vendas.SQL.Clear;
ZQuery_vendas.SQL.Add('select nome_vendedor, total_jan');
ZQuery_vendas.SQL.Add('from (select distinct nome_vendedor from ');
ZQuery_vendas.SQL.Add('vendedores where vendas.vd_data between :dat_inicio and :dat_fim as cnsempregadovenda');
ZQuery_vendas.SQL.Add('left outer join(select nome_vendedor as nome_vendedor_jan, sum(total)total_jan from items_vendas, vendas, vendedores');
ZQuery_vendas.SQL.Add('where extract(month from vd_data)=1 and extract(year from vd_data)= extract(year from :dat_inicio)');
ZQuery_vendas.SQL.Add('group by nome_vendedor)as cnsvendajan on(cnsvendajan.nome_vendedor_jan=csnvendedores.nome_vendedor');
ZQuery_vendas.ParamByName('dat_inicio').Value:=StrToDate(MaskEdit1.Text);
ZQuery_vendas.ParamByName('dat_fim').Value:=StrToDate(MaskEdit2.Text);
ZQuery_vendas.Active:=True;
end;
procedure Tfrm_relatoriovendas.Button1Click(Sender: TObject);
begin
selectano();
end;

No meu caso, como eu já peguei o sistema em andamento, tenho que tirar os dados de três tabelas,

Tabela Vendedores

id_vendedor

nome_vendedor

Tabela items_vendas

id_pedido

total

Tabela vendas

vd_id_pedido

vd_data

vd_id_vendedor

Se eu não coloco nada no maskedit, diz que ' / / 'não é uma data válida, mas se eu coloco no maskEdit1 01/01/2007, dá o seguinte erro 'You have an error in your SQL sintax near'select distinct nome_vendedor from endedores where vendas.vd_data between '2007'at line 1''

Se puder me dizer onde estou errando, eu agradeço.

Obrigado!!

[Micheus]

Se eu não coloco nada no maskedit, diz que ' / / 'não é uma data válida, mas se eu coloco no maskEdit1 01/01/2007, dá o seguinte erro 'You have an error in your SQL sintax near'select distinct nome_vendedor from endedores where vendas.vd_data between '2007'at line 1''

a questão de deixar a data em branco é um item a ser tratado. É claro que você não poderá fazer a consulta se a data não for válida.

Vamos tentar clarear um pouco as coisas.

Conforme as tabelas, a consulta para obter o total das vendas por vendedor ficaria deste modo:

SELECT V.id_vendedor, sum(I.total) AS total_venda
FROM vendas V, items_vendas I
WHERE V.id_pedido = I.id_pedido 
GROUP BY V.id_vendedor

Se queremos agrupar por data, então temos que acrescentá-la na cláusula GROUP BY, ficando assim:

SELECT V.id_vendedor, V.vd_data, sum(I.total) AS total_venda
FROM vendas V, items_vendas I
WHERE V.id_pedido = I.id_pedido 
GROUP BY V.id_vendedor, V.vd_data

Mas isto ainda não resulta no que se quer. Queremos agrupar apenas pelo MES e ANO - sem agrupar pelo dia. Supondo que desejamos o período de um ano (de 01/Jan/2007 a 31/Dez/2007), podemos escrever a consulta para o mês de Janeiro da seguinte forma:

SELECT V.id_vendedor, sum(I.total) AS total_venda_JAN
FROM vendas V, items_vendas I
WHERE V.id_pedido = I.id_pedido 
  AND extract(month from V.vd_data) = 1  /* Mês de janeiro */
  AND extract(year from V.vd_data) = extract(year from :dat_inicio) /* ano do filtro */
GROUP BY V.id_vendedor

agora temos todas as vendas do mês de janeiro agrupadas por vendedor. para fevereiro, seria similar:

SELECT V.id_vendedor, sum(I.total) AS total_venda_FEV
FROM vendas V, items_vendas I
WHERE V.id_pedido = I.id_pedido 
  AND extract(month from V.vd_data) = 2  /* Mês de fevereiro */
  AND extract(year from V.vd_data) = extract(year from :dat_inicio) /* ano do filtro */
GROUP BY V.id_vendedor

e assim por diante... Bom, tentando "corrigir" seu código (é meio difícil porque não tenho a base para testar - é tudo virtual), inclui alguns comentários, identei e acrescentei alguns espaços de modo a manter a consulta SQL visível - sem muita confusão. Numa seqüência deste tamanho (o que vai ficar) a compreensão dele fica muito prejudicada se você não puder colocar os olhos e enxergar logo onde as coisas estão. ;) Também fiz uso do with ... do, que é muito útil ao minimizar a repetição do nome das variáveis em várias linhas.

procedure Tfrm_relatoriovendas.selectano;
begin
  with ZQuery_vendas do
  begin
    Active:=False;
    SQL.Clear;
    SQL.Add('select nome_vendedor, total_jan');
   // este sub-select tem o objetivo de buscar apenas uma ocorrência dos 
   // vendedores que realizaram vendas no período sendo avaliado
    SQL.Add('from (select distinct id_vendedor ');
    SQL.Add(      'from vendas ');
    SQL.Add(      'where vd_data between :dat_inicio and :dat_fim) as cnsempregadovenda ');
   // essa parte traz o valor vendido Janeiro, do ano em dat_inicio, para
   // cada vendedor listado no sub-select anterior
   // esta sequencia deve ser repetida para os demais meses
    SQL.Add('left outer join(select id_vendedor as id_vendedor_jan, sum(total)total_jan ');
    SQL.Add(                'from items_vendas, vendas');
    SQL.Add(                'where V.id_pedido = I.id_pedido ');
    SQL.Add(                  'and extract(month from vd_data) = 1 ');
    SQL.Add(                  'and extract(year from vd_data) = extract(year from :dat_inicio)');
    SQL.Add(                'group by id_vendedor) as cnsvendajan on (cnsvendajan.id_vendedor_jan = csnvendedores.id_vendedor');
  // este join tem como objetivo buscar os dados do vendefor na consulta
  // cnsempregadovenda, que a consulta que trouxe apenas os vendedores com
  // vendas no período. No caso você está apenas buscando nome_vendedor.
    SQL.Add('left join vendedores V on (V.id_vendedor = cnsempregadovenda.id_vendedor');

    ParamByName('dat_inicio').AsDateTime := StrToDate(MaskEdit1.Text);
    ParamByName('dat_fim').AsDateTime := StrToDate(MaskEdit2.Text);
    Active:=True;
  end;
end;

Tente o código como está agora e dê um retorno (não sei se poderei verificar as mensagens amanhã, talvez só no domingo)

Caso você utilize um período que fique fora do mesmo ano, tipo, quando começa em um ano e termina em outro, me avise. A codificação está preparada para trabalhar no mesmo ano. Da mesma forma, quando o período for bem variável (acho que é o caso) permitindo apenas alguns meses, será necessário alguns ajustes, já que estamos fixando o mês nas consultas - mas isto é o de menos. O importante é entender o espírito da coisa.

Abraços

[João Paulo Taraciuk]

Infelizmente não consegui, esta dando o erro You have an error in SQL sintax near 'select distinct nome_vendedor from vendas where vd_data between'2008-01-01'and'at line 1

[Micheus]

Infelizmente não consegui, esta dando o erro You have an error in SQL sintax near 'select distinct nome_vendedor from vendas where vd_data between'2008-01-01'and'at line 1

João, desculpe por ter esquecido deste tópico. :blush:

Você poderia postar o seu SQL como está nesta última situação?

Abraços

[João Paulo Taraciuk]

Então amigo, antes de mais nada, obrigado pela atenção, mas, pelo que eu entendi, na versão de banco que eu utilizo, não tem a opção de subselect, e o erro acontece exatamente onde inicia o primeiro subselect, e eu tive de dar uma parada com os relatórios, para terminar a parte de vendas e por o sistema para rodar, mas logo logo vou ter de mecher de novo, aí a gente tenta matar essa aí.

Eu utilizo o banco MySql 3.23(Não sei se teria uma ferramenta gráfica para trabalhar com ele)e Delphi 7, com o ZEOS 6.5

Obrigado!!!

Editado Março 4, 2008 por João Paulo Taraciuk

[Micheus]

Eu utilizo o banco MySql 3.23(Não sei se teria uma ferramenta gráfica para trabalhar com ele)e Delphi 7, com o ZEOS 6.5

Não pensou possibilidade de atualizar a versão do banco?

Quanto a ferramentas, não foi para você que citei umas existentes no site do MySQL, porém para a versão 5?

Abraços