sql-server – Query union, group by and count in the same query by filtering by date range

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.
Scroll to Top
AllEscort