none
Média Com data diferente RRS feed

  • Pergunta

  • Tenho uma consulta SQL que me traz  o valor das vendas por vendedor no intervalo de 6 meses, porém quero pegar a média de vendas dos 5 meses anteriores. 
    Tem como subtrair um mês com AVG ?

    DECLARE @DATA AS SMALLDATETIME, @DATA2 AS SMALLDATETIME
    SET @DATA  = '2019-01-01'
    SET @DATA2 = ''2019-06-30''

                                       SELECT

                                 VENDEDOR , SUM(VALORVENDAS)FROM VENDAS 
                                   WHERE DATAVENDAS BETWEEN @DATA AND @DATA2

     Mas preciso da media de vendas de 
     01/01/2019 até 
     30/05/2019
                                          

              

    quarta-feira, 19 de junho de 2019 17:04

Respostas

  •  Olá Jose Diz; a construção das datas é dinâmica conforme preencho data inicial e data final no Form. 
     A coluna DATAVENDAS é do tipo Smalldatetime.
    O código correto sem colocar nomes bonitos é este. 
    (...)

    William, no código o período de emissão é de 2/2019 a 6/2019; ou seja, 5 meses. Já no resultado que transcreveu anteriormente não há valores para junho/2019. Por isso que MEDIAGERAL e MEDIAMENOS1MES retornaram os mesmos valores.

    ---

    Minha dúvida que permanece: o valor de MEDIA GERAL deve ser:

    (1) a somatória de valores divididos pelo número de casos (no exemplo me parece que foram 9), resultando em 1087,066666
    ou
    (2) a somatória dos valores mensais dividido pelo número de meses?

    Se for o caso (1), eu diria que o seu código está correto; o que houve foi ausência de dados para o período solicitado.

    Se for o caso (2), isto é:
         (3924,34 + 2688,66 + 956,55 + 2214,05) / 4
      = 9783,60  / 4
      = 2445,9

    então terá que substituir
             ,AVG(J08_008_B) AS MEDIAGERAL
             ,AVG(CASE WHEN J07.J07_006_D < @DATA3 THEN J08_008_B END )AS MEDIAMENOS1MES
    por
             ,sum (J08_008_B) / (datediff (month, @DATA, @DATA3) +1) as MEDIAGERAL
             ,sum (CASE WHEN J07.J07_006_D < @DATA3 THEN J08_008_B END) / datediff (month, @DATA, @DATA3) as MEDIAMENOS1MES

    Aliás, esta proposta do caso (2) é semelhante à que "gapimex" propôs em "19 de junho de 2019 20:16".   

    ---

    VENDEDOR1 | 3924,34 |2688,66 | 956,55 | 2214,05| 0,00 | 9783,60 |
    Onde as colunas são
    Vendedor | 02/2019 | 03/2019 | 04/2019 | 05/2019 | 06/2019 | somadasvendas

    O que você precisa nos informar é: considerando-se os valores que transcreveu anteriormente para [02/2019], [03/2019], [04/2019], [05/2019] e [06/2019], quais seriam os valores corretos para MEDIAGERAL e MEDIAMENOS1MES?


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.
    • Editado José Diz segunda-feira, 24 de junho de 2019 11:57
    • Marcado como Resposta William_droops segunda-feira, 24 de junho de 2019 13:21
    segunda-feira, 24 de junho de 2019 11:39

Todas as Respostas

  • Boa tarde,

    Não sei se entendi corretamente mas segue uma sugestão para testes:

    DECLARE @DATA AS DATE, @DATA2 AS DATE, @DATAINIMEDIA AS DATE
    SET @DATA  = '2019-01-01'
    SET @DATA2 = '2019-06-30'
    SET @DATAINIMEDIA = DATEADD(MONTH, 1, @DATA) 
    
    SELECT
        VENDEDOR, 
        SUM(VALORVENDAS) AS SOMA,
        AVG(CASE WHEN DATAVENDAS BETWEEN @DATAINIMEIDA AND @DATA2 THEN VALORVENDAS END) AS MEDIA
    FROM VENDAS 
    WHERE 
        DATAVENDAS BETWEEN @DATA AND @DATA2

    Espero que ajude


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

    quarta-feira, 19 de junho de 2019 17:23
  • A ideia é mais ou menos essa, você adicionou um Mês na data inicial, no meu caso preciso tirar um Mês da data final, porém não esta batendo as médias.
    Mas os valores não bate, mesmo usando só o AVG, já não bate, olha a Query completa. 

    DECLARE @DATA AS SMALLDATETIME, @DATA2 AS SMALLDATETIME,  @DATA3 AS SMALLDATETIME
    SET @DATA  = '2019-01-01'
    SET @DATA2 = '2019-06-30'
    
    SET @DATA3 = DATEADD(MONTH,-1, @DATA2)
    
                       SELECT 
                         VENDEDOR 
                         SUM(VALORVENDAS) AS VENDAS
    
                        REPLACE(CONVERT(DECIMAL (10,2), SUM(CASE WHEN DATEPART(MONTH,DATAVENDAS) = 01 AND DATEPART(YEAR,DATAVENDAS) = 2019 THEN VALORVENDAS END)),'.',',') as '01/2019'
    ,REPLACE(CONVERT(DECIMAL (10,2), SUM(CASE WHEN DATEPART(MONTH,DATAVENDAS) = 02 AND DATEPART(YEAR,DATAVENDAS) = 2019 THEN VALORVENDAS END)),'.',',') as '02/2019'
    ,REPLACE(CONVERT(DECIMAL (10,2), SUM(CASE WHEN DATEPART(MONTH,DATAVENDAS) = 03 AND DATEPART(YEAR,DATAVENDAS) = 2019 THEN VALORVENDAS END)),'.',',') as '03/2019'
    ,REPLACE(CONVERT(DECIMAL (10,2), SUM(CASE WHEN DATEPART(MONTH,DATAVENDAS) = 04 AND DATEPART(YEAR,DATAVENDAS) = 2019 THEN VALORVENDAS END)),'.',',') as '04/2019'
    ,REPLACE(CONVERT(DECIMAL (10,2), SUM(CASE WHEN DATEPART(MONTH,DATAVENDAS) = 05 AND DATEPART(YEAR,DATAVENDAS) = 2019 THEN VALORVENDAS END)),'.',',') as '05/2019'
    ,REPLACE(CONVERT(DECIMAL (10,2), SUM(CASE WHEN DATEPART(MONTH,DATAVENDAS) = 06 AND DATEPART(YEAR,DATAVENDAS) = 2019 THEN VALORVENDAS END)),'.',',') as '06/2019'  
    
     ,AVG(CASE WHEN DATAVENDAS BETWEEN @DATA AND @DATA3 THEN VALORVENDAS END )AS MEDIA2



    quarta-feira, 19 de junho de 2019 18:48
  • Que média você quer obter? Por venda? Por mês? Ou outro tipo de média?

    Da forma como foi sugerido você deve estar obtendo a média de por venda.


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

    quarta-feira, 19 de junho de 2019 18:56
  • A média (valor de venda) por período ,  Data inicial até 1 mês ha menos da data final. 

    No Select eu informo a venda mensal e o valor total, agora quero exibir a média de vendas do mês 01 até o mês 05. 
    quarta-feira, 19 de junho de 2019 19:00
  • Tenho uma consulta SQL que me traz  o valor das vendas por vendedor no intervalo de 6 meses, porém quero pegar a média de vendas dos 5 meses anteriores. 
    (...)
    Mas preciso da media de vendas de
     01/01/2019 até
     30/05/2019

    Experimente

    -- código #1
    DECLARE @DATA AS SMALLDATETIME, @DATA2 AS SMALLDATETIME, @DATA3 as smalldatetime;
    SET @DATA  = '2019-01-01';
    SET @DATA2 = '2019-06-30';
    
    set @DATA3= dateadd (month, -1, dateadd (day, +1, @DATA2));
    
    SELECT VENDEDOR, 
           SUM(VALORVENDAS) as Soma,
           avg (case when DATAVENDAS < @DATA3 then VALORVENDAS) as [Média]
      from VENDAS 
      where DATAVENDAS BETWEEN @DATA AND @DATA2
    group by VENDEDOR;

    A função AVG() calcula a média considerando-se a soma total dos valores das vendas nos meses de janeiro a maio/2019 dividido pelo número de vendas realizadas no período. Ou seja, o valor do ticket médio. 

    ---

    Mas se o objetivo é a média mensal, eis outra sugestão:

    -- código #2 DECLARE @DATA AS SMALLDATETIME, @DATA2 AS SMALLDATETIME, @DATA3 AS SMALLDATETIME; SET @DATA = '2019-01-01'; SET @DATA2 = '2019-06-30'; with soma_Mensal as (
    SELECT VENDEDOR,
    SUM(VALORVENDAS) as VENDAS,
    SUM(CASE WHEN DATEPART(MONTH,DATAVENDAS) = 01 AND DATEPART(YEAR,DATAVENDAS) = 2019 THEN VALORVENDAS END) as Jan2019,
           SUM(CASE WHEN DATEPART(MONTH,DATAVENDAS) = 02 AND DATEPART(YEAR,DATAVENDAS) = 2019 THEN VALORVENDAS END) as Fev2019,
           SUM(CASE WHEN DATEPART(MONTH,DATAVENDAS) = 03 AND DATEPART(YEAR,DATAVENDAS) = 2019 THEN VALORVENDAS END) as Mar2019,
           SUM(CASE WHEN DATEPART(MONTH,DATAVENDAS) = 04 AND DATEPART(YEAR,DATAVENDAS) = 2019 THEN VALORVENDAS END) as Abr2019,
           SUM(CASE WHEN DATEPART(MONTH,DATAVENDAS) = 05 AND DATEPART(YEAR,DATAVENDAS) = 2019 THEN VALORVENDAS END) as Mai2019,
           SUM(CASE WHEN DATEPART(MONTH,DATAVENDAS) = 06 AND DATEPART(YEAR,DATAVENDAS) = 2019 THEN VALORVENDAS END) as Jun2019
    from VENDAS
    where DATAVENDAS BETWEEN @DATA and @DATA2
    group by VENDEDOR
    )
    SELECT VENDEDOR, VENDAS,
    Jan2019, Fev2019, Mar2019, Abr2019, Mai2019, Jun2019,
           (coalesce(Jan2019, 0) + coalesce(Fev2019, 0) + coalesce(Mar2019, 0) +
    coalesce(Abr2019, 0) + coalesce(mai2019, 0)) / 5.0 as [Média Jan-Mai 2019]
    from soma_Mensal;



    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz quarta-feira, 19 de junho de 2019 20:15
    quarta-feira, 19 de junho de 2019 19:22
  • William, você quer obter a média mensal dos período? Se for experimente dessa forma:

    SET @DATA3 = DATEADD(MONTH, 5, @DATA) 
    
    SELECT
        VENDEDOR, 
        SUM(VALORVENDAS) AS SOMA,
        sum(CASE WHEN DATAVENDAS < @DATA3 THEN VALORVENDAS END) / 5 AS MEDIA
    FROM VENDAS 
    WHERE 
        DATAVENDAS BETWEEN @DATA AND @DATA2

    Espero que ajude


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

    quarta-feira, 19 de junho de 2019 19:31
  • Não esta vindo com o valor correto. 
    Mas mesmo se eu colocar o AVG de todo o período. 
    quarta-feira, 19 de junho de 2019 19:44
  • Não esta vindo com o valor correto. 
    Mas mesmo se eu colocar o AVG de todo o período. 

    William, refere-se ao código #1 ou ao código #2?

    Você poderia postar os valores obtidos/esperados, para que seja possível compreender o que está ocorrendo?


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz quarta-feira, 19 de junho de 2019 19:51
    quarta-feira, 19 de junho de 2019 19:49
  • O resultado foi este para o código #1

    VENDEDOR1	| 3924,34 |2688,66 |	956,55 |	2214,05| 0,00 |	9783,60	| 1087.066666 |1087.066666
    
    
    Onde as colunas são 
    
    Vendedor | 02/2019 | 03/2019 | 04/2019 | 05/2019 | 06/2019 | somadasvendas | media geral | media menos 1 mes 



    quarta-feira, 19 de junho de 2019 20:01
  • Seria a média Mensal do periodo sim, mas não posso deixar Fixo, pois a data inicial e final pode ser de 11 meses, então precisaria pegar a media mensal dos 10 primeiros meses, deixando o mês 11 de fora. 
    quarta-feira, 19 de junho de 2019 20:05
  • Willian, segue uma sugestão para testes:

    SET @DATA3 = DATEADD(DAY, 1 - DATEPART(DAY, @DATA2), @DATA2)
    
    SELECT
        VENDEDOR, 
        SUM(VALORVENDAS) AS SOMA,
        sum(CASE WHEN DATAVENDAS < @DATA3 THEN VALORVENDAS END) / 
            DATEDIFF(MONTH, @DATA, @DATA3) AS MEDIA
    FROM VENDAS 
    WHERE 
        DATAVENDAS BETWEEN @DATA AND @DATA2

    Espero que ajude


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

    quarta-feira, 19 de junho de 2019 20:16
  • O resultado foi este para o código #1

    VENDEDOR1	| 3924,34 |2688,66 |	956,55 |	2214,05| 0,00 |	9783,60	| 1087.066666 |1087.066666
    
    Onde as colunas são 
    
    Vendedor | 02/2019 | 03/2019 | 04/2019 | 05/2019 | 06/2019 | somadasvendas | media geral | media menos 1 mes 

    William, o código #1 tem somente 3 colunas.  E o código #2 retorna 9 colunas, com mês iniciando em janeiro 2019. O resultado que transcreveu acima não coincide com o leiaute de nenhum dos 2 códigos que postei.

    Conforme você informou em sua primeira mensagem deste tópico, a média é para ser calculada de janeiro a maio; no resultado que postou não há valores para janeiro/2019.

    Poderia verificar essas divergências?

    ---

    Como está declarada a coluna DATAVENDAS da tabela VENDAS?


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz quarta-feira, 19 de junho de 2019 23:52
    quarta-feira, 19 de junho de 2019 20:25
  •  Olá Jose Diz; a construção das datas é dinâmica conforme preencho data inicial e data final no Form. 
     
     A coluna DATAVENDAS é do tipo Smalldatetime.

    O código correto sem colocar nomes bonitos é este. 
    DECLARE @DATA AS SMALLDATETIME, @DATA2 AS SMALLDATETIME,  @DATA3 AS SMALLDATETIME
    	SET @DATA  = '2019-02-01'
    	SET @DATA2 = '2019-06-30'
    	SET @DATA3 = DATEADD(MONTH,-1,DATEADD(day,+1, @DATA2))
    					
    	   SELECT
    	        RTRIM(A03.A03_001_C) AS 'CLIENTE' 							
    			,REPLACE(ISNULL(CONVERT(DECIMAL (10,2), SUM(CASE WHEN DATEPART(MONTH,J07.J07_006_D) = 02 AND DATEPART(YEAR,J07.J07_006_D) = 2019 THEN J08_008_B END)),0),'.',',') as '02/2019'
    			,REPLACE(ISNULL(CONVERT(DECIMAL (10,2), SUM(CASE WHEN DATEPART(MONTH,J07.J07_006_D) = 03 AND DATEPART(YEAR,J07.J07_006_D) = 2019 THEN J08_008_B END)),0),'.',',') as '03/2019'
    			,REPLACE(ISNULL(CONVERT(DECIMAL (10,2), SUM(CASE WHEN DATEPART(MONTH,J07.J07_006_D) = 04 AND DATEPART(YEAR,J07.J07_006_D) = 2019 THEN J08_008_B END)),0),'.',',') as '04/2019'
    			,REPLACE(ISNULL(CONVERT(DECIMAL (10,2), SUM(CASE WHEN DATEPART(MONTH,J07.J07_006_D) = 05 AND DATEPART(YEAR,J07.J07_006_D) = 2019 THEN J08_008_B END)),0),'.',',') as '05/2019'
    			,REPLACE(ISNULL(CONVERT(DECIMAL (10,2), SUM(CASE WHEN DATEPART(MONTH,J07.J07_006_D) = 06 AND DATEPART(YEAR,J07.J07_006_D) = 2019 THEN J08_008_B END)),0),'.',',') as '06/2019'  
    			,REPLACE(ISNULL(SUM(CONVERT(DECIMAL(10,2), J08_008_B)),0),'.',',') AS 'VALOR OVS' 
    			,AVG(J08_008_B) AS MEDIAGERAL
    			,AVG(CASE WHEN J07.J07_006_D < @DATA3 THEN J08_008_B END )AS MEDIAMENOS1MES
    	                     
    	   FROM DBO.A33 (NOLOCK)
    	                     LEFT JOIN DBO.J07 (NOLOCK) ON DBO.A33.UKEY = DBO.J07.A33_UKEY
    	                     LEFT JOIN DBO.J08 (NOLOCK) ON J07.UKEY = J08.J07_UKEY AND J08.J08_995_B > 0
    	                     LEFT JOIN DBO.A54 (NOLOCK) ON LEFT(J07.A36_CODE,5) = A54.A36_UKEY
    						 LEFT JOIN DBO.A03 (NOLOCK) ON A03.UKEY = J07.A03_UKEY AND A03.CIA_UKEY = J07.CIA_UKEY
    	              WHERE 
    	                  A33_066_N <> 1
    					   AND J07.J07_006_D BETWEEN @DATA AND @DATA2
    					   AND J08.J08_995_B > 0  
    					    AND (J08.J08_921_C <> 3) AND (J08.J08_921_C <> 2)
    						and A03.A03_001_C = 'CL.PG.0095'
    	               GROUP BY  A03.A03_001_C, A03.A03_003_C
    	                  

    segunda-feira, 24 de junho de 2019 08:58
  •  Olá Jose Diz; a construção das datas é dinâmica conforme preencho data inicial e data final no Form. 
     A coluna DATAVENDAS é do tipo Smalldatetime.
    O código correto sem colocar nomes bonitos é este. 
    (...)

    William, no código o período de emissão é de 2/2019 a 6/2019; ou seja, 5 meses. Já no resultado que transcreveu anteriormente não há valores para junho/2019. Por isso que MEDIAGERAL e MEDIAMENOS1MES retornaram os mesmos valores.

    ---

    Minha dúvida que permanece: o valor de MEDIA GERAL deve ser:

    (1) a somatória de valores divididos pelo número de casos (no exemplo me parece que foram 9), resultando em 1087,066666
    ou
    (2) a somatória dos valores mensais dividido pelo número de meses?

    Se for o caso (1), eu diria que o seu código está correto; o que houve foi ausência de dados para o período solicitado.

    Se for o caso (2), isto é:
         (3924,34 + 2688,66 + 956,55 + 2214,05) / 4
      = 9783,60  / 4
      = 2445,9

    então terá que substituir
             ,AVG(J08_008_B) AS MEDIAGERAL
             ,AVG(CASE WHEN J07.J07_006_D < @DATA3 THEN J08_008_B END )AS MEDIAMENOS1MES
    por
             ,sum (J08_008_B) / (datediff (month, @DATA, @DATA3) +1) as MEDIAGERAL
             ,sum (CASE WHEN J07.J07_006_D < @DATA3 THEN J08_008_B END) / datediff (month, @DATA, @DATA3) as MEDIAMENOS1MES

    Aliás, esta proposta do caso (2) é semelhante à que "gapimex" propôs em "19 de junho de 2019 20:16".   

    ---

    VENDEDOR1 | 3924,34 |2688,66 | 956,55 | 2214,05| 0,00 | 9783,60 |
    Onde as colunas são
    Vendedor | 02/2019 | 03/2019 | 04/2019 | 05/2019 | 06/2019 | somadasvendas

    O que você precisa nos informar é: considerando-se os valores que transcreveu anteriormente para [02/2019], [03/2019], [04/2019], [05/2019] e [06/2019], quais seriam os valores corretos para MEDIAGERAL e MEDIAMENOS1MES?


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.
    • Editado José Diz segunda-feira, 24 de junho de 2019 11:57
    • Marcado como Resposta William_droops segunda-feira, 24 de junho de 2019 13:21
    segunda-feira, 24 de junho de 2019 11:39
  • Olá José Diz

    O valor média dever ser o 2, com esta solução cheguei ao que desejava. 
    obrigado 

    ,sum (J08_008_B) / (datediff (month, @DATA, @DATA3) +1) as MEDIAGERAL
             ,sum (CASE WHEN J07.J07_006_D < @DATA3 THEN J08_008_B END) / datediff (month, @DATA, @DATA3) as MEDIAMENOS1MES


    segunda-feira, 24 de junho de 2019 13:21