I'm not making progress on creating a query that can have UNION, GROUP BY, and COUNT based on a date range in the same query. To achieve my goal, I need the following actions in the same query:
1st) filter, group and count all customer requests in a given period
2º) filter and group the customers who did not send requests in the same period
3º) identify the customers that sent the request with a status = ACTIVE
4º) Identify clients that DO NOT send request with a status = INACTIVE
5º) In my query below I still need to place in the WHERE clause the filter by date range that I will pass through parameter.
Thank you in advance for the help of your 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