Olá amigos.
Estou fazendo um relátorio de Faturamento, para mostrar em um gráfico o faturamento de cada dia do mes, só que o problema é que
se não tiver NUNHUM pedido em determinado dia ele não retorna nada, deveria retornar pelo menos com o dia e o faturamento = 0.
o SQL que tou usando.
select
Extract(YEAR From dataHora) AS ano,
Extract(DAY From dataHora) AS dia,
sum(precoTotal) as valor
from
Pedido this_
where
Extract(Month From dataHora) in (1)
and Extract(YEAR From dataHora) in (2009 , 2008)
and status ='Concluido'
group by
dia,
ano
order by
ano desc,
dia asc
Observe que os dias 1, 3, 8… não aparece pois não tem nenhum registro, eu preciso que eles apareçam mesmo com o Valor 0.
Alguem tem uma dica ?!?
Achei uma solução que não gostei… que será criar uma tabela de Calendario e fazer um Left Join nela, pois ai ele traria os valores…
Mas não considero essa solução (Gambiarra… rsrsrrs)
Olá Ricardo,
Acho que a solução que você considera Gambi é a única saída para resolver o problema, pois como que o banco de dados vai saber quais os dias trabalhados para poder deduzir que não houve vendas neste dia? O jeito é voce ter uma tabela “calendário” com dias úteis. Talvez até já exista na empresa uma tabela deste tipo com datas, procure no sistema de RH ou contabilidade. Aí é só fazer o cruzamento destas tabelas.
è exatamente esse o problema, eu gostaria de saber se algum banco já tem alguma implementação para resolver esse tipo de problema. Ou se existe alguma
tecnica de SQL para fazer isso.
A outra solução que encontrei foi de fazer um tratamento desses dados (com Java) para preencher essas lacunas…
(mas n considero isso uma solução limpa.)
Aquestão se de ter uma tabela de calendário foge um pouco do escopo, pois o projeto do Banco de Dados é altamente orientado a Objetos, ai lá
no meio do banco taco uma tabela que nem se quer a aplicação vai usar… Acho isso meio érrado (mas é uma sulução… :lol: )
não sei se estou bem certo se isso funciona no seu caso (e pra ser honesto também não é uma opção muito bonita de resolver o problema), mais você pode colocar seu software para quando abrir, inserir um pedido com valor 0 e status como concluido, e quando for inserir o primeiro pedido do dia, apagar esse pedido com valor 0 e então inserir o pedido real.
Isso não é uma forma la muito bonita de resolver o problema, mas me parece melhor do que criara uma tabela calendario e alimentar ela com todos os dias uteis…
se for o caso de usar a tabela calendario, poderia-se alimentar o dia corrente como util ao abrir o software do mesmo jeito… ai fica a teu criterio (ao menos enquanto não se tem uma opção melhor).
Me parece perfeitamente coerente um sistema de faturamento, ou qualquer outro sistema empresarial, ter um controle de dias úteis. Projeto altamente orientado a objetos? Calendário não é um objeto?
ah… esqueceram este detalhe na modelagem? … não tem problema, melhor consertar agora do que ficar fazendo as gambiarras depois…
Eu iria precisar apenas de uma Faixa de numeros de 1…31 (que são os dias do Mês)
não seria um para pegar os Dias Úteis, acredito que nesse caso não tinha jeito de evitar a Tabela de Calendario.
Cara teitei com criar uma tabela de Calendario, mas n consegui direito… alguem tem um exemplo ???
Tentei COM inner join, left join, left outer join (sou meio ruim de SQL) e só sai isso…
Ta faltando dia 1 e 3, que são os dias que realmente nao tem nenhum pedido.
A Tabela de Calendario
O Sql:
select
Extract(YEAR From dataHora) AS anoPedido
,Extract(DAY From dataHora) AS diaPedido
,Pedido.dataHora
,sum(precoTotal) as valor
,Calendario.mes,Calendario.dia, Calendario.data
from
Pedido
left outer join Calendario ON (Pedido.dataHora = Calendario.data)
where
Extract(MONTH From dataHora) in (1)
and status ='Concluido'
and Extract(YEAR From dataHora) in (2009)
group by
diaPedido,
anoPedido
order by
anoPedido desc,
diaPedido asc
Em vez de poluir o banco com informações irrelevantes(tabela calendário), ou transformar a sua query de consulta num monstro de 7 cabeças(usando magia-negra específica do seu BD) eu adotaria uma solução na camada Java da sua aplicação.
Eu penso assim:
para o caso do seu atual relatório, faz sentido aparecerem as informações dos dias em que não houveram vendas, mas e para outros relatórios? É relevante essa informação?
Se for, dai vc pode voltar a pensar em controlar isso pela base(com uma tabela de calendário, por exemplo). Caso contrário, significa que essa situação é exclusiva (ou quase) do seu relatório, então nada mais justo que o seu relatório se virar para arrumar as informações da maneira correta.
Eu voto pela solução Java.
Ela seria limpa, concisa e não quebraria nenhum conceito de OO.
drop table vendas if exists;
drop table calendario if exists;
create table vendas(dia int, faturamento int);
create table calendario(dia int);
insert into calendario values(1 );
insert into calendario values(2 );
insert into calendario values(3 );
insert into calendario values(4 );
insert into calendario values(5 );
insert into vendas values(2, 10);
insert into vendas values(3, 15);
insert into vendas values(3, 10);
insert into vendas values(3, 15);
insert into vendas values(5, 20);
select calendario.dia, (select sum(vendas.faturamento)
from vendas where vendas.dia = calendario.dia )
from calendario;
select
YEAR(Calendario.data) AS ano
,DAY(Calendario.data) AS dia
,SUM(COALESCE(precoTotal, 0)) as valor
,Calendario.data as DataCalendario
FROM
Calendario left join Pedido ON (Calendario.data = DATE(Pedido.dataHora))
where
ano in (2009)
AND mes in (1)
AND (Pedido.status = 'Concluido' or Pedido.status is NULL)
group by
dia,
ano
order by
ano desc,
dia asc
Considerações
[quote]- A Tabela de calendário precisa ser completa (com datas de vários anos), queria só com DIAS de 1 a 31 mas não consequi…
SUM(COALESCE(precoTotal, 0)) é importante, pois se precoTotal for NULL ele traz o valor 0;
Foi escolhido: "YEAR(Calendario.data) AS ano" ao invés de: "Extract(YEAR From Calendario.data)", pois é compativel com
o derby, mysql, outros eu não sei, so sei que no postgree deve ser com Extract
Deve ter sempre a verifição de NULL: (Pedido.status = ‘Concluido’ or Pedido.status is NULL), esse foi o problema
que não tinha me atentado, já tava até certo a consulta só que a como todos os valores de Pedido serão NULL
qualquer restrição que precisar ser adicionado deve ter um or Pedido.XXX is NULL
Deve ser um left join de Calendario->Pedido. Como o pmlm disse[/quote]
Agora, assim… não vou usar essa solução … :shock:
É, apesar de ser a solução mais facil a curto prazo, pode ser prejudicial a longo prazo.
E tambem como alguns concordaram, isso foge dos padrões de POO e do Design da minha aplicação (na minha opinião), ai vocês se
perguntam: calendário num é um Objeto ??? . Sim, mais ele não faz parte do DOMINIO da minha aplicação, por isso que disse que não se encaixa nesse caso.
A solução que ou vou adotar é fazer isso em Java. Criar uma classe que faça a "Higienização" dos dados retornados.
Acredito que essa seja a solução mais facil de Manter
A consulta será feita usando Critéria, trabalhei aqui com SQL pois é mais facil de testar e exergar os detalhes
De qualquer modo aprendi muitas coisas que não sabia em SQL, valeu galera.
Fica ai de exemplo no caso alguem precisar e querer usar dessa forma.