Consulta UNION, GROUP BY e COUNT na mesma query

[Linked 0]

Olá pessoal,

 

Não estou conseguindo avançar na criação de uma query que possa ter UNION, GROUP BY e COUNT baseado em um periodo de data na mesma consulta. Para eu atingir meu objetivo, necessito das seguintes ações na mesma consulta:

1. 
   
2. filtrar, agrupar e contar todos os pedidos de clientes em um determinado período
   
3. filtrar e agrupar os clientes que não enviaram pedidos no mesmo período
   
4. identificar os clientes que enviaram pedido com um status = ATIVO
   
5. Identificar os clientes que NÃO enviaram pedido com um status = INATIVO
   

Na minha query abaixo ainda falta colocar na claúsula WHERE o filtro por intervalo de datas que passarei através de parâmetro.

 

Agradeço antecipadamente a ajuda dos colegas para atingir esse objetivo.

select "Cliente"."Nome" as Conveniado, "Municipios"."Nome" as Cidade,       "Estado"."Nome" as Estado, 'ATIVO' as status  from ((((dbo.Pedidos prt  inner join dbo.Cliente Cliente       on (Cliente.Id = prt.ClienteId))  inner join dbo.Logradouro Logradouro       on (Logradouro.Id = Cliente.Logradouro01Id))  inner join dbo.Municipios Municipios       on (Municipios.Id = Logradouro.MunicipioId))  inner join dbo.Estado Estado       on (Estado.Id = Municipios.EstadoId)) where (prt.Inativo = 0)UNION select Cliente.Nome as Conveniado, Municipios.Nome as Cidade,       Estado.Nome as Estado, 'INATIVO' as status  from ((((dbo.Pedidos prt  inner join dbo.Cliente Cliente       on (Cliente.Id = prt.ClienteId))  inner join dbo.Logradouro Logradouro       on (Logradouro.Id = Cliente.Logradouro01Id))  inner join dbo.Municipios Municipios       on (Municipios.Id = Logradouro.MunicipioId))  inner join dbo.Estado Estado       on (Estado.Id = Municipios.EstadoId)) where ((prt.ClienteId Is Null))

só um detalhe que esqueci pessoal, acho também que eu não deveria usar INNER JOIN mas LEFT JOIN. De qualquer forma, agradeço mais uma vez a colaboração de vocês.

[Motta 645]

Creio que não precisa do UNION , basta o OUTER JOIN tratando a ausencia pela presença da chave nula.

[Linked 0]

Creio que não precisa do UNION , basta o OUTER JOIN tratando a ausencia pela presença da chave nula.

Bom dia Motta, obrigado pelo feedback!

 

Por gentileza, baseado nos objetivos acima que expus, você poderia me demonstrar como fazer isso?

 

Desculpe-me pela ignorância, mas não entendo bem de SQL SERVER, eu trabalho mais é com o LINQ no asp.net mvc. Neste momento necessito que a consulta seja em SQL no formato que citei acima. Se você puder me ajudar fico grato.

 

Abs,

[Motta 645]

Creio ser isto , talvez algum detalhe da síntexe possa ter passado , mas parte-se de Cliente e se busca Pedidos com OUTER JOIN , a não existência da chave em PEDIDOS indica Cliente sem Pedido

select *

from (((( dbo.Cliente Cliente 

left join dbo.Pedidos prt  on (prt.ClienteId = Cliente.Id))

inner join dbo.Logradouro Logradouro on (Logradouro.Id = Cliente.Logradouro01Id))

inner join dbo.Municipios Municipios on (Municipios.Id = Logradouro.MunicipioId))

inner join dbo.Estado Estado on (Estado.Id = Municipios.EstadoId))

[Linked 0]

 

Creio ser isto , talvez algum detalhe da síntexe possa ter passado , mas parte-se de Cliente e se busca Pedidos com OUTER JOIN , a não existência da chave em PEDIDOS indica Cliente sem Pedido

select *
from (((( dbo.Cliente Cliente 
left join dbo.Pedidos prt  on (prt.ClienteId = Cliente.Id))
inner join dbo.Logradouro Logradouro on (Logradouro.Id = Cliente.Logradouro01Id))
inner join dbo.Municipios Municipios on (Municipios.Id = Logradouro.MunicipioId))
inner join dbo.Estado Estado on (Estado.Id = Municipios.EstadoId))

Puxa vida Motta fiquei feliz demais; você realmente entende de SQL.

A partir de sua consulta acima, você me ajudou a economizar linhas de desenvolvimento na query, ou seja, aquele "trambolho" de consulta UNION que fiz com 27 linhas deixará de existir e será substituída pela sua sugestão.

 

O único problema que estou tendo agora que na alteração da clausula WHERE que fiz abaixo e não sei a razão, a consulta não me filtra mais os Clientes sem pedidos. Veja:

select Cliente.Nome as Cli, Municipios.Nome as Cidade,
       Estado.Sigla as Estado, count(prt.Id) as TOTAL, IIF(count(prt.Id) > 0,'ATIVOS','INATIVOS') as status
from (((( dbo.Cliente Cliente 
left join dbo.Pedidos prt  on (prt.ClienteId = Cliente.Id))
left join dbo.Logradouro Logradouro on (Logradouro.Id = Cliente.Logradouro01Id))
left join dbo.Municipios Municipios on (Municipios.Id = Logradouro.MunicipioId))
left join dbo.Estado Estado on (Estado.Id = Municipios.EstadoId))
 where (
     (convert(date,prt.Cadastro) BETWEEN '01/01/2000' and '05/09/2015' )
     and Cliente.IsConveniado = 1
     and prt.Inativo = 0 )
GROUP BY Cliente.Nome, Municipios.Nome, Estado.Sigla

Perceba que para os clientes ATIVOS (que possuem pedidos), filtro por intervalo de datas e PEDIDOS que não estão cancelados (prt.Inativo = 0). Até aqui tudo bem, a consulta me traz os clientes com pedidos, mas infelizmente não me traz os Clientes SEM PEDIDOS.

 

Como que eu faço para essa consulta trazer também os clientes sem pedidos???

[Motta 645]

Teste a chave nulo de pedidos

 

... eft join dbo.Estado Estado on (Estado.Id = Municipios.EstadoId))

where ( (prt.ClienteId is null)

or

(convert(date,prt.Cadastro) BETWEEN '01/01/2000'

and '05/09/2015' )

and Cliente.IsConveniado = 1

and prt.Inativo = 0 ) ) ....

[Linked 0]

Teste a chave nulo de pedidos

 

... eft join dbo.Estado Estado on (Estado.Id = Municipios.EstadoId))

where ( (prt.ClienteId is null)

or

(convert(date,prt.Cadastro) BETWEEN '01/01/2000'

and '05/09/2015' )

and Cliente.IsConveniado = 1

and prt.Inativo = 0 ) ) ....

Motta, mais uma vez muito obrigado mesmo, funcionou perfeitamente!!!

 

Não sei se isso seria relevante, mas toda vez que vou fazer uma consulta baseado em um período de data, fico na dúvida se devo converter e inverter o formato da data para "YYYY/MM/DD". O que você me sugere, continuo pesquisando no padrão brasileiro ou inverto para o padrão americano ?

 

 

Teste a chave nulo de pedidos

 

... eft join dbo.Estado Estado on (Estado.Id = Municipios.EstadoId))

where ( (prt.ClienteId is null)

or

(convert(date,prt.Cadastro) BETWEEN '01/01/2000'

and '05/09/2015' )

and Cliente.IsConveniado = 1

and prt.Inativo = 0 ) ) ....

Boa noite Motta,

 

Me perdoe, me enganei quando lhe disse que deu certo, na realidade a consulta acima não pegou os Clientes SEM pedidos DENTRO do período, ou seja, a consulta me trouxe corretamente os Clientes COM pedidos no período e apenas Clientes que NUNCA mandaram pedidos. Eu preciso que a consulta me retorne TAMBÉM os Clientes que não possuem pedidos DENTRO do período informado. Isso é possível Motta?

[Motta 645]

Faça um subselect com NOT EXISTS ou NOT IN.

[Linked 0]

Faça um subselect com NOT EXISTS ou NOT IN.

Já fiz, mas infelizmente ele também não me traz os Clientes cujo o status "Cliente.IsConveniado = 1"

 

 

Faça um subselect com NOT EXISTS ou NOT IN.

Veja a consulta que eu fiz, não deu certo:

select Cliente.Cliente as Cli, Municipios.Nome as Cidade,       Estado.Sigla as Estado, count(prt.Id) as TOTAL, IIF(count(prt.Id) > 0,'ATIVOS','INATIVOS') as statusfrom (((( dbo.Cliente Cliente left join dbo.Pedidos prt  on (prt.ClienteId = Cliente.Id))left join dbo.Logradouro Logradouro on (Logradouro.Id = Cliente.Logradouro01Id))left join dbo.Municipios Municipios on (Municipios.Id = Logradouro.MunicipioId))left join dbo.Estado Estado on (Estado.Id = Municipios.EstadoId))where ( (prt.ClienteId is null and Cliente.IsConveniado = 1)or( Cliente.IsConveniado=1 and NOT EXISTS (SELECT NULL                  FROM dbo.Pedidos p                  WHERE p.ClienteId  = Cliente.Id                   AND (convert(date,p.Cadastro) BETWEEN '01/09/2015' and                                                          '09/09/2015' )))or (convert(date,prt.Cadastro) BETWEEN '01/09/2015'and '09/09/2015'  and Cliente.IsConveniado = 1 and prt.Inativo = 0 ) ) GROUP BY Cliente.Nome, Municipios.Nome, Estado.Sigla

Da forma que eu alterei acima, a consulta deveria me trazer o seguinte:

1. 
   
2. Os clientes COM PEDIDOS dentro do Período e que estejam com a situação (Cliente.IsConveniado = 1)
   
3. Os clientes que NUNCA enviaram pedidos e que estejam com a situação (Cliente.IsConveniado = 1)
   
4. Os clientes SEM PEDIDOS no PERIÓDO informado que estejam com a situação (Cliente.IsConveniado = 1)
   

Sincerante não consigo "enxergar" onde estou errando, tem hora que dá vontade de desistir viu... B)

[Motta 645]

Os itens 2 e 3 se somam poiscse nunca teve não teve no período, correto ?

 

Como seria a saída dos dados pedida ?

[Linked 0]

Os itens 2 e 3 se somam poiscse nunca teve não teve no período, correto ?

 

Como seria a saída dos dados pedida ?

 

Vamos as resposta de suas questões:

 

"Os itens 2 e 3 se somam poiscse nunca teve não teve no período, correto ?"

 

R: Na verdade não são a mesma coisa.

• No item 02 => eu tenho Clientes que desde que foram cadastrados na base de dados, NUNCA tiveram pedidos
• No item 03 => existem Clientes que mandaram pedidos anteriormente, MAS DENTRO DO PERÍODO que o usuário está solicitando, a data de cadastro destes pedidos pode não estar dentro do intervalo.

 

"Como seria a saída dos dados pedida ? "

 

Cliente Cidade Estado Total (qtde de pedidos) status

====== ====== ====== ================== =====

Paulo Araguari MG 10 ATIVO

Paula Cuiabá MT 5 ATIVO

Joaquim Uberaba MG 0 INATIVO (**** este cliente aqui nunca enviou pedido )

Eliane Prata MG 0 INATIVO (**** já esta cliente aqui já enviou pedidos anteriormente, mas no período que o usuário solicitou, ou seja, ela tem que aparecer porque não existem pedidos compreendidos no período solicitado)

 

 

 

O correto seria ter uma saída conforme demonstrado abaixo, mas achei muito difícil de criar uma consulta assim, já que não entendo bem de SQL crú:

 

Cliente Cidade Estado Total (qtde de pedidos) status

====== ====== ====== ================== ======================

Paulo Araguari MG 10 Enviou no período solicitado

Paula Cuiabá MT 5 Enviou no período solicitado

Joaquim Uberaba MG 0 Nunca enviou pedidos

Eliane Prata MG 0 Enviou FORA do período solicitado

 

Seria isso Motta, acho que agora eu consegui formatar melhor a minha questão.

 

Fico no aguardo de seu retorno ok?

 

Um abraço

[Motta 645]

Pensaria um um subselect como coluna ou uma FUNCTION.

 

http://www.devmedia.com.br/pl-sql-functions-e-procedures/29882

[Linked 0]

Pensaria um um subselect como coluna ou uma FUNCTION.

Bom dia,

 

Não sei se você percebeu, mas eu fiz uma subselect no exemplo logo acima usando NOT EXISTS, conforme você sugeriu, mas não deu certo.

 

Acredito que como moderador deste grupo, você tenha muitas solicitações de ajuda e talvez pelo volume de pedidos, não tenha tempo disponível para dispor em questões mais longas com essa. Pode ter certeza, estou muito agradecido por você já ter me dado algumas dicas até agora.

 

Por um acaso você poderia me indicar outro colega que possa me ajudar a nessa consulta?

 

 

http://www.devmedia.com.br/pl-sql-functions-e-procedures/29882

Obrigado pela dica Motta, mas por enquanto PL-SQL é um nível muito avançado pra mim. Se nessa consulta mais simples para desenvolvedores experientes como você eu não estou conseguindo, imagina usando FUNCTIONS.

 

De qualquer forma, obrigado por mais essa dica.

 

Forte abraço e fique na paz.

[Motta 645]

Parece complicado mas não é tanto , muitas vezes em pesquisas um pouco mais complexax a FUNCTION é uma solução viável

[Linked 0]

Parece complicado mas não é tanto , muitas vezes em pesquisas um pouco mais complexax a FUNCTION é uma solução viável

 

Acredito que seja viável, embora o colega JOSEDR, do nosso grupo de discussão tenha me orientado nesse link:

http://forum.imasters.com.br/topic/534476-filtrar-dados-a-partir-de-campos-datetime/#entry2133827 a evitar funções em clausula WHERE. Mesmo assim infelizmente ainda não tenho competência para criar consultas neste nível.

 

Se você ainda puder me ajudar, fico muito grato, caso contrário ficaria grato também se você me direcionar para ou tro colega do forum ou outro moderador.

[Hugo Anselmo 4]

 

Veja a consulta que eu fiz, não deu certo:

select Cliente.Cliente as Cli, Municipios.Nome as Cidade,
       Estado.Sigla as Estado, count(prt.Id) as TOTAL, IIF(count(prt.Id) > 0,'ATIVOS','INATIVOS') as status
from (((( dbo.Cliente Cliente 
left join dbo.Pedidos prt  on (prt.ClienteId = Cliente.Id))
left join dbo.Logradouro Logradouro on (Logradouro.Id = Cliente.Logradouro01Id))
left join dbo.Municipios Municipios on (Municipios.Id = Logradouro.MunicipioId))
left join dbo.Estado Estado on (Estado.Id = Municipios.EstadoId))
where (
 (prt.ClienteId is null and Cliente.IsConveniado = 1)
or

(
 Cliente.IsConveniado=1 and
 NOT EXISTS (SELECT NULL
                  FROM dbo.Pedidos p
                  WHERE p.ClienteId  = Cliente.Id 
                  AND (convert(date,p.Cadastro) BETWEEN '01/09/2015' and 
                                                         '09/09/2015' ))
)

or 

(convert(date,prt.Cadastro) BETWEEN '01/09/2015'
and '09/09/2015'  
and Cliente.IsConveniado = 1 
and prt.Inativo = 0 ) ) 
GROUP BY Cliente.Nome, Municipios.Nome, Estado.Sigla

Da forma que eu alterei acima, a consulta deveria me trazer o seguinte:

 

1. Os clientes COM PEDIDOS dentro do Período e que estejam com a situação (Cliente.IsConveniado = 1)
2. Os clientes que NUNCA enviaram pedidos e que estejam com a situação (Cliente.IsConveniado = 1)
3. Os clientes SEM PEDIDOS no PERIÓDO informado que estejam com a situação (Cliente.IsConveniado = 1)

 

 

Sincerante não consigo "enxergar" onde estou errando, tem hora que dá vontade de desistir viu... B)

A questão é que em consultas complexas como essa eu costumo simplificar com tabela temporária. Faça 3 selects apartados para cada situação, em cada um deles insira o resultado numa tabela temporária e depois faça o select na tabela temporária, descomplicado.

 

Eu não estou na minha máquina por isso montei o select no bloco de notas, caso dê erro verifique o where de cada select que o problema estará lá. Se não entendeu mande MP.

 

 

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp

select Cliente.Cliente as Cli, Municipios.Nome as Cidade,
       Estado.Sigla as Estado, count(prt.Id) as TOTAL, IIF(count(prt.Id) > 0,'ATIVOS','INATIVOS') as status
into #temp
from (((( dbo.Cliente Cliente 
left join dbo.Pedidos prt  on (prt.ClienteId = Cliente.Id))
left join dbo.Logradouro Logradouro on (Logradouro.Id = Cliente.Logradouro01Id))
left join dbo.Municipios Municipios on (Municipios.Id = Logradouro.MunicipioId))
left join dbo.Estado Estado on (Estado.Id = Municipios.EstadoId))
where prt.ClienteId is null and Cliente.IsConveniado = 1 
GROUP BY Cliente.Nome, Municipios.Nome, Estado.Sigla

go

insert into #temp (cli, cidade, estado, total, status)
select Cliente.Cliente as Cli, Municipios.Nome as Cidade,
       Estado.Sigla as Estado, count(prt.Id) as TOTAL, IIF(count(prt.Id) > 0,'ATIVOS','INATIVOS') as status
from (((( dbo.Cliente Cliente 
left join dbo.Pedidos prt  on (prt.ClienteId = Cliente.Id))
left join dbo.Logradouro Logradouro on (Logradouro.Id = Cliente.Logradouro01Id))
left join dbo.Municipios Municipios on (Municipios.Id = Logradouro.MunicipioId))
left join dbo.Estado Estado on (Estado.Id = Municipios.EstadoId))
where 
 Cliente.IsConveniado=1 and
 NOT EXISTS (SELECT NULL
                  FROM dbo.Pedidos p
                  WHERE p.ClienteId  = Cliente.Id 
                  AND (convert(date,p.Cadastro) BETWEEN '01/09/2015' and 
                                                         '09/09/2015' )
GROUP BY Cliente.Nome, Municipios.Nome, Estado.Sigla

go

insert into #temp (cli, cidade, estado, total, status)
select Cliente.Cliente as Cli, Municipios.Nome as Cidade,
       Estado.Sigla as Estado, count(prt.Id) as TOTAL, IIF(count(prt.Id) > 0,'ATIVOS','INATIVOS') as status
from (((( dbo.Cliente Cliente 
left join dbo.Pedidos prt  on (prt.ClienteId = Cliente.Id))
left join dbo.Logradouro Logradouro on (Logradouro.Id = Cliente.Logradouro01Id))
left join dbo.Municipios Municipios on (Municipios.Id = Logradouro.MunicipioId))
left join dbo.Estado Estado on (Estado.Id = Municipios.EstadoId))
where (convert(date,prt.Cadastro) BETWEEN '01/09/2015'
and '09/09/2015'  
and Cliente.IsConveniado = 1 
and prt.Inativo = 0 )  
GROUP BY Cliente.Nome, Municipios.Nome, Estado.Sigla

go

select * from #temp

Perdoe-me se entendi mal mas se entendi corretamente é exatamente dessa forma que eu faria para fugir de subquery e union.

[Linked 0]

Boa tarde Hugo Anselmo,

 

Puxa vida muito obrigado por se dispor a me ajudar.

 

Você sem querer está me incentivando a trabalhar com tabelas temporárias, que é outra questão que mais cedo ou mais tarde eu terei que aprender, por mais isso, te agradeço imensamente por essa ajuda.

 

Irei estudar a sua query ao máximo, antes de postar alguma dúvida que porventura eu venha ter em um MP pra você ok?

 

Forte abraço e fique na paz!