Query Union, group by and Count in the same query filtering by date range

Question

Query Union, group by and Count in the same query filtering by date range

Asked 9 years, 2 months ago

Viewed 298 times

3

I am not making progress in creating a query that can have UNION, GROUP BY and COUNT based on a date period in the same query. For me to achieve my goal, I need the following actions in the same query:

1º) filter, group and count all customer orders in a given period

2º) filter and group customers who did not send orders in the same period

3º) Identify customers who submitted an order with a status = ACTIVE

4º) Identify customers who DID NOT submit a request with a status = INACTIVE

5º) In my query below we still have to put in WHERE the filter by date range that I will pass through parameter.

Thank you 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

2

Please create an example on sqlfiddle.com So we can help better!

– Giovanni Machado

2015/09/12 at 01:32
I believe that if you implement Where should work, if you want you can create SELECT INTO #Memory, and then access #Memory by making filters after.

– Roger Oliveira

2015/09/23 at 00:28
Guys, I work with Asp.net mvc 4 and Linq. Forgive me for not understanding pure sql queries. Roger, could you give me an example of how to work with #Memory in my case?

– Hugo

2015/09/24 at 14:03

1 answer

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.

by Samuel Fales • 14 points · Answer 1 · 2017-04-07T00:00:32+00:00

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.