Question:
I'm not able to advance in the creation of a query that can have UNION, GROUP BY and COUNT based on a date period in the same query. In order for me to reach my goal, I need the following actions in the same consultation:
1st) filter, group and count all customer orders in a given period
2nd) filter and group customers who have not sent orders in the same period
3rd) identify the customers who sent an order with a status = ACTIVE
4th) Identify customers who did NOT send an order with a status = INACTIVE
5) In my query below I still need to put in the WHERE clause the filter by date range that I will pass through parameter.
Thanks in advance for the help of colleagues to achieve this goal.
select "Cliente"."Nome" as Conveniado, "Municipios"."Nome" as Cidade,
"Estado"."Nome" as Estado, 'ATIVO' as status
from ((((dbo.Pedidos prt
left join dbo.Cliente Cliente
on (Cliente.Id = prt.ClienteId))
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.Inativo = 0)
UNION
select Cliente.Nome as Conveniado, Municipios.Nome as Cidade,
Estado.Nome as Estado, 'INATIVO' as status
from ((((dbo.Pedidos prt
left join dbo.Cliente Cliente
on (Cliente.Id = prt.ClienteId))
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 ( ) // AQUI deverei implementar uma busca por intervalo de data, baseado no campo [prt.Cadastro], que deverá retornar os cliente que AINDA NÃO fizeram pedidos no intervalo de datas
Answer:
Create the initial structure as a table…
select * from (
select "Cliente"."Nome" as Conveniado, "Municipios"."Nome" as Cidade,
"Estado"."Nome" as Estado, 'ATIVO' as status
from ((((dbo.Pedidos prt
left join dbo.Cliente Cliente
on (Cliente.Id = prt.ClienteId))
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.Inativo = 0)
UNION
select Cliente.Nome as Conveniado, Municipios.Nome as Cidade,
Estado.Nome as Estado, 'INATIVO' as status
from ((((dbo.Pedidos prt
left join dbo.Cliente Cliente
on (Cliente.Id = prt.ClienteId))
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))) as tb
where tb. -- aqui vc coloca seu filtro.