Query union, group by and count in same query filtering by date range

3

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
    
asked by anonymous 06.09.2015 / 01:44

1 answer

-1

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.
    
07.04.2017 / 02:00