SQL retornando Taxa Conversao

Question

 Eu preciso realizar o resultado desejado para uma procedure conforme abaixo:

Eu tenho uma Tabela Orcamento com:

  Id, Usuario,  DataCriacao, Status (0 - Aberto, 1 - Atendido), e DataAtendimento 

Eu preciso retornar em uma procedure o seguinte resultado conforme o ano passado por parâmetro:

  Usuário || Mes || Taxa Conversao  

  Cristiano,  01,  20
  Cristiano,  02,  15
   Maria,      01,  40

  A coluna Taxa de Conversão seria o Número de Orçamentos do Usuário com DataAtendimento do Mês * 100 e Dividido pelo Total de Orçamentos Criados do Mês através da DataCriacao. 

   Ex. Em Janeiro, o usuario Cristiano Criou 10 Orçamentos e existem no Mês de Janeiro 2 Orçamentos com Status = 1 Atendido, entao o resultado da coluna Taxa Conversao seria 20%, ou seja.  (2 * 100) / 10.

  Agradeço a ajuda.

 

 

Answer 1

Boa tarde,

Experimente fazer uns testes dessa forma para ver se é obtido o resultado esperado:

with 
    CTE_Criad as
    (
        select 
            Usuario,
            Month(DataCriacao) as Mes,
            Year(DataCriacao) as Ano,
            count(1) as Qtd
        from Orcamento
        group by
            Usuario,
            Month(DataCriacao),
            Year(DataCriacao)
    ),

    CTE_Atend as
    (
        select 
            Usuario,
            Month(DataAtendimento) as Mes,
            Year(DataAtendimento) as Ano,
            count(1) as Qtd
        from Orcamento
        where
            Status = 1 
        group by
            Usuario,
            Month(DataAtendimento),
            Year(DataAtendimentoo)
    )

select
    c.Usuario.
    c.Mes,
    c.Ano,
    100.0 * a.Qtd / c.Qtd as TaxaConversao

from CTE_Criad as c
left join CTE_Atend as a
    on 
        a.Usuario = c.Usuario and
        a.Mes = c.Mes and
        a.Ano = c.Ano

Espero que ajude

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 2

 

  Boa Tarde Gapimex!

  Ela funcionou no aspecto geral, apenas tem uma situação que acaba não trazendo valor para determinado mês, por exemplo:

  Mes: 07 (Julho) teve 1 Orçamento Atendido, mas que neste mês de julho não houve nenhum Criado, apenas 1 Atendido que foi criado no mês anterior. Na pratica esse caso teria que mostrar 100%, ou se tivesse 3 atendidos e 1 criado ficaria 300%.

   Como que poderia tratar isso, pois a base do select é o CTE_Criad conforme seu exemplo.

  Obrigado!

Answer 3

Tente fazer uns testes deixando a parte final da consulta da seguinte forma:

select
    coalesce(c.Usuario, a.Usuario) as Usuario,
    coalesce(c.Mes, a.Mes) as Mes,
    coalesce(c.Ano, a.Ano) as Ano,
    100.0 * a.Qtd / coalesce(c.Qtd, 1) as TaxaConversao
from CTE_Criad as c
full join CTE_Atend as a
    on 
        a.Usuario = c.Usuario and
        a.Mes = c.Mes and
        a.Ano = c.Ano

Espero que ajude

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 4

 

  Funcionou 100%, não conhecia esse Full Join.

  Obrigado!