none
Média Tarifada/Average com outros 3 Select na mesma query RRS feed

  • Pergunta

  • Bom dia a todos

    Pessoal por gentileza vejam se coseguem me orientar, preciso da média da P1, P2 e P3

    São os 3 Selects que fiz no código:

    BEGIN TRY DROP TABLE #TARIFASP3METRO END TRY BEGIN CATCH END CATCH
      SELECT 
             AVG(PD.[flt_base]) AS flt_base,  
     

        INTO #TARIFASP1METRO
        FROM #Data D
             INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
                                       PD.[Flt Type]            = D.[Flt Type]
                                       
                       
    WHERE     D.[vch_empresa_editada] = 'P1'

    GROUP BY PD.[dtt_data_captura]

    BEGIN TRY DROP TABLE #TARIFASP2METRO END TRY BEGIN CATCH END CATCH
      SELECT 
             AVG(PD.[flt_base]) AS flt_base,  
     

        INTO #TARIFASP3METRO
        FROM #Data D
             INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
                                       PD.[Flt Type]            = D.[Flt Type]
                                       
                       
    WHERE     D.[vch_empresa_editada] = 'P2'

    GROUP BY PD.[dtt_data_captura]

    BEGIN TRY DROP TABLE #TARIFASP3METRO END TRY BEGIN CATCH END CATCH
      SELECT 
             AVG(PD.[flt_base]) AS flt_base,  
     

        INTO #TARIFASP3METRO
        FROM #Data D
             INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
                                       PD.[Flt Type]            = D.[Flt Type]
                                       
                       
    WHERE     D.[vch_empresa_editada] = 'P3'

    GROUP BY PD.[dtt_data_captura]  
             

    E agora preciso de uma coluna que traga a média dos três. Mas ao fazer o mesmo código mudando o final: WHERE     D.[vch_empresa_editada] IN ('P1','P2','P3') , está me trazendo a média da coluna total e não linha a linha. Obrigada





    segunda-feira, 27 de março de 2017 13:10

Respostas

  • Deleted
    • Marcado como Resposta Robson William Silva quarta-feira, 29 de março de 2017 16:19
    • Não Marcado como Resposta Thaís Sakamoto sexta-feira, 7 de abril de 2017 19:00
    • Marcado como Resposta Thaís Sakamoto sexta-feira, 7 de abril de 2017 19:03
    • Não Marcado como Resposta Thaís Sakamoto segunda-feira, 10 de abril de 2017 13:44
    • Marcado como Resposta Thaís Sakamoto segunda-feira, 10 de abril de 2017 14:41
    terça-feira, 28 de março de 2017 21:29
  • Thaís, me parece que você pode realizar tudo em uma única consulta. Considerando-se o código inicial que postou neste tópico, bem como o modelo de resultado esperado, eis uma sugestão:

    -- código #1
    IF Object_ID('tempDB..#TARIFASP0METRO', 'U') is not null then DROP TABLE #TARIFASP0METRO;

    SELECT [dtt_data_captura], [flt_base P1]= Avg(case when D.[vch_empresa_editada] = 'P1' then PD.[flt_base] else NULL end), [flt_base P2]= Avg(case when D.[vch_empresa_editada] = 'P2' then PD.[flt_base] else NULL end), [flt_base P3]= Avg(case when D.[vch_empresa_editada] = 'P3' then PD.[flt_base] else NULL end), flt_base= Avg(PD.[flt_base]) --into #TARIFASP0METRO from #Data as D inner join #PreData as PD on PD.GrupoQuery = D.GrupoQuery and PD.[Flt Type] = D.[Flt Type]
    where D.[vch_empresa_editada] in ('P1', 'P2', 'P3') group by PD.[dtt_data_captura];

     

    Utilizando o código que transcreveu posteriormente:

    -- código #2 v2
    IF Object_ID('tempDB..#TARIFASINDMETRO', 'U') is not null then DROP TABLE #TARIFASINDMETRO;
              
    SELECT 'IND' AS industria,
           PD.[dtt_data_captura],
           [flt_base G3]= Avg(case when D.[vch_empresa_editada] = 'G3' then PD.[flt_base] else NULL end),  
           [flt_base JJ]= Avg(case when D.[vch_empresa_editada] = 'JJ' then PD.[flt_base] else NULL end),
           [flt_base OC]= Avg(case when D.[vch_empresa_editada] = 'OC' then PD.[flt_base] else NULL end),
           flt_base= Avg(PD.[flt_base]),
           D.[Flt Type], 
           MT.AD,
           D.[dtt_data]
      --into #TARIFASINDMETRO
      from #Data as D
           inner join #PreData as PD on PD.GrupoQuery = D.GrupoQuery and
                                        PD.[dtt_geracao_ql2] = D.QTS and
                                        PD.[vch_empresa_editada] = D.[vch_empresa_editada] and
                                       --PD.[vch_tipo_arquivo] = D.[vch_tipo_arquivo] and
                                        PD.[vch_od] = D.[vch_od] and
                                        PD.[dtt_data] = D.[dtt_data] and
                                        PD.[Flt Type] = D.[Flt Type]
            inner join @Metro as MT on MT.OC = D.[vch_od]
      where D.[vch_empresa_editada] in ('G3', 'JJ', 'OC') 
      group by PD.[dtt_data_captura], D.[Flt Type], MT.AD, D.[dtt_data];

     

    -----
    O Wesley postou uma observação que me pareceu pertinente. As médias que você apresenta na coluna "Valor Correto / AVERAGE" também me parecem incorretas. Por exemplo, na primeira linha, a média para
           (804,9 + 669,9 + NULL)  é 491,6, mas você informa 737,4. Se é para obter a média das 3 colunas, então a divisão tem que ser sempre por 3.

     

    -----
    Cuidado que a média aritmética de médias aritméticas nem sempre representa o mesmo que a média do todo.

    Por exemplo, se temos os seguintes dados:

    G3 JJ OC
    1 10 18 23
    2 20 89
    3 30 22 2

    teremos as seguintes médias aritméticas, por empresa:

    G3 JJ OC
    1 10 18 23
    2 20 89
    3 30 22 2
    média 20 20 38

    Se optamos por calcular qual é o valor usando a média aritmética das médias aritméticas, teremos
       (20 + 20 + 38) / 3 = 26

    Mas a média aritmética correta é
      (10 + 18 + 23 + 20 + 89 + 30 + 22 + 2) / 8 = 26,75.

    Para calcular média de médias, onde o número de casos de cada média varia, sugiro o uso da média aritmética ponderada:
      ((20 * 3) + (20 * 2) + (38 * 3)) / 8 = 26,75.

     

    -----
    Em tempo, a função AVG divide a somatória dos valores pelo número de casos, desconsiderando-se ausência de valores (NULL).


    e-mail       José Diz     Belo Horizonte, MG - Brasil




    Excelente José

    Deu super certo. Obrigada

    industria flt_base G3 flt_base JJ flt_base OC flt_base
    IND 1039.588 NULL NULL 1039.588
    IND 1039.588 NULL NULL 1039.588
    IND 1119.178 NULL NULL 1119.178
    IND 876.2333 597.2 NULL 776.5786
    IND 1119.178 517.7714 NULL 722.0226
    IND 876.2333 NULL NULL 876.2333
    IND 854.9 517.7714 NULL 559.9125
    IND 702.7571 517.7714 NULL 625.6798

    quinta-feira, 30 de março de 2017 11:49
  • Deleted
    • Marcado como Resposta Thaís Sakamoto segunda-feira, 10 de abril de 2017 14:41
    segunda-feira, 10 de abril de 2017 14:29
  • Deleted
    • Marcado como Resposta Thaís Sakamoto segunda-feira, 10 de abril de 2017 18:54
    segunda-feira, 10 de abril de 2017 14:40

Todas as Respostas

  • Bom dia Thaís,

    Na verdade, o que você quer fazer não é possível com uma função agregada desta forma, pois elas incidem no resultado das colunas mesmo.

    Sugiro fazer algo neste sentido:

    
    SELECT [dtt_data_captura], (flt_base1 + flt_base2 + flt_base3) / 3
    
    	FROM (
    
    		SELECT
    				(SELECT 
    							AVG(PD.[flt_base]) AS flt_base,
    							PD.[dtt_data_captura]
    					FROM #Data D
    							INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
    													PD.[Flt Type]            = D.[Flt Type]                                                  
    				WHERE     D.[vch_empresa_editada] = 'P1'
    				GROUP BY PD.[dtt_data_captura] ) as flt_base1,
    
    				(  SELECT 
    							AVG(PD.[flt_base]) AS flt_base,
    							PD.[dtt_data_captura]
     					FROM #Data D
    							INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
    													PD.[Flt Type]            = D.[Flt Type]
    				WHERE     D.[vch_empresa_editada] = 'P2'
    				GROUP BY PD.[dtt_data_captura]) flt_base2,
    				
    				(SELECT 
    							AVG(PD.[flt_base]) AS flt_base,
    							PD.[dtt_data_captura]
    					FROM #Data D
    							INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
    													PD.[Flt Type]            = D.[Flt Type]  
    				WHERE     D.[vch_empresa_editada] = 'P3'
    				GROUP BY PD.[dtt_data_captura]) flt_base3
    	) TAB

    Espero ter ajudado.

    Abraço.

    segunda-feira, 27 de março de 2017 14:26
  • Olá Thais 

    1) Sua media sempre será de 3 meses ??

    por que seu resultado correto é  (737.4) (Soma das Medias dividido por 2)

    O correto não seria 491.6 (soma das medias divido por 3 meses

    Fiz um exemplo bem simples  que acho que resolve seu problema

    DECLARE @Medias TABLE 
    (
     P1 DECIMAL(18,1),
     P2 DECIMAL(18,1),
     P3 DECIMAL(18,1)
    )
    
    INSERT INTO @Medias
            ( P1, P2, P3 )
        VALUES ( 804.9, 669.9, NULL  ),( 804.9, 592.9, NULL  )
    
    	SELECT M.P1 ,
               M.P2 ,
               M.P3,
    		   Media = (ISNULL(p1,0) + ISNULL(p2,0) + ISNULL(p3,0)) / 2
    		   FROM @Medias AS M
    		   


    Wesley Neves

    segunda-feira, 27 de março de 2017 14:27
  • Excelente Elton,

    Dá para compreeder, mas ao montar na minha query apresenta o erro:

    Msg 102, Level 15, State 1, Line 2075
    Incorrect syntax near 'TAB'.

    Você sabe o que está acontecendo na sintaxe?

    Obrigada

    segunda-feira, 27 de março de 2017 15:24
  • Olá Wesley

    As minhas medidas não serão meses, e sim tarifas de empresas diferentes. 

    Que mudam conforme a data de pesquisa. E preciso calcular a média delas em uma nova coluna.

    Obrigada

    segunda-feira, 27 de março de 2017 15:26
  • Excelente Elton,

    Dá para compreeder, mas ao montar na minha query apresenta o erro:

    Msg 102, Level 15, State 1, Line 2075
    Incorrect syntax near 'TAB'.

    Você sabe o que está acontecendo na sintaxe?

    Obrigada

    Thaís, confere se você copiou toda a consulta, criei tabelas no mesmo formato e não apresenta o erro.

    segunda-feira, 27 de março de 2017 17:36
  • Elton

    É que o meu é um pouco diferente, veja o trecho que preciso mudar para trazer a média tarifada. 

    Mesmo assim eu testei o seu codigo e continua apontando o erro do Msg 102 TAB

    BEGIN TRY DROP TABLE #TARIFASINDMETRO END TRY BEGIN CATCH END CATCH
    SELECT 
              
            'IND' AS industria,
             PD.[dtt_data_captura],
             AVG(PD.flt_base) AS flt_base, 
             D.[Flt Type], 
             MT.AD,
    D.[dtt_data]


        INTO #TARIFASINDMETRO
        FROM #Data D
             INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
                                       PD.[dtt_geracao_ql2]     = D.QTS AND
                                       PD.[vch_empresa_editada] = D.[vch_empresa_editada] AND
                                       --PD.[vch_tipo_arquivo]    = D.[vch_tipo_arquivo] AND
                                       PD.[vch_od]              = D.[vch_od] AND
      PD.[dtt_data]            = D.[dtt_data] AND
                                       PD.[Flt Type]            = D.[Flt Type]
                                     
    INNER JOIN @Metro MT   ON MT.OC                    = D.[vch_od]                                                                      
                                      

    WHERE     D.[vch_empresa_editada] IN ('G3','JJ','OC') 


    GROUP BY PD.[dtt_data_captura],
             D.[Flt Type], MT.AD, D.[dtt_data]
    BEGIN TRY DROP TABLE #TARIFASG3METRO END TRY BEGIN CATCH END CATCH
      SELECT 
             
             D.[vch_empresa_editada], 
             AVG(PD.flt_base) AS flt_base, 
             D.[Flt Type], 
             MT.AD,
             PD.[dtt_data_captura],
    		 D.[dtt_data]
    
        INTO #TARIFASG3METRO
        FROM #Data D
             INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
                                       PD.[dtt_geracao_ql2]     = D.QTS AND
                                       PD.[vch_empresa_editada] = D.[vch_empresa_editada] AND
                                       --PD.[vch_tipo_arquivo]    = D.[vch_tipo_arquivo] AND
                                       PD.[vch_od]              = D.[vch_od] AND
    								   PD.[dtt_data]            = D.[dtt_data] AND
                                       PD.[Flt Type]            = D.[Flt Type]
                                       
             INNER JOIN @Metro MT   ON MT.OC                    = D.[vch_od]
         
    WHERE     D.[vch_empresa_editada] = 'G3'
    
    GROUP BY PD.[dtt_data_captura],
             D.[vch_empresa_editada], 
             D.[Flt Type], MT.AD, D.[dtt_data] 
    
    --Select * from #TARIFASG3METRO
     -----------------------------------------------------------------------------------------------------------------
              
    
    BEGIN TRY DROP TABLE #TARIFASJJMETRO END TRY BEGIN CATCH END CATCH
      SELECT 
             
             D.[vch_empresa_editada], 
             AVG(PD.flt_base) AS flt_base, 
             D.[Flt Type], 
             MT.AD,
             PD.[dtt_data_captura],
    		 D.[dtt_data]
    
        INTO #TARIFASJJMETRO
        FROM #Data D
             INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
                                       PD.[dtt_geracao_ql2]     = D.QTS AND
                                       PD.[vch_empresa_editada] = D.[vch_empresa_editada] AND
                                       --PD.[vch_tipo_arquivo]    = D.[vch_tipo_arquivo] AND
                                       PD.[vch_od]              = D.[vch_od] AND
    								   PD.[dtt_data]            = D.[dtt_data] AND
                                       PD.[Flt Type]            = D.[Flt Type]
                                       
             INNER JOIN @Metro MT   ON MT.OC                    = D.[vch_od]                                   
    
    WHERE     D.[vch_empresa_editada] = 'JJ'
    
    GROUP BY PD.[dtt_data_captura],
             D.[vch_empresa_editada], 
             D.[Flt Type], MT.AD, D.[dtt_data]
    
    --Select * from #TARIFASJJMETRO
    -----------------------------------------------------------------------------------------------------------------
    BEGIN TRY DROP TABLE #TARIFASOCMETRO END TRY BEGIN CATCH END CATCH
      SELECT 
             
             D.[vch_empresa_editada],
    		 AVG(PD.[flt_base]) AS flt_base,    
             D.[Flt Type], 
             MT.AD,
    		 PD.[dtt_data_captura],
    		 D.[dtt_data]
    
        INTO #TARIFASOCMETRO
        FROM #Data D
             INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
                                       PD.[dtt_geracao_ql2]     = D.QTS AND
                                       PD.[vch_empresa_editada] = D.[vch_empresa_editada] AND
                                       --PD.[vch_tipo_arquivo]    = D.[vch_tipo_arquivo] AND
                                       PD.[vch_od]              = D.[vch_od] AND
    								   PD.[dtt_data]            = D.[dtt_data] AND
                                       PD.[Flt Type]            = D.[Flt Type]
                                       
             INNER JOIN @Metro MT   ON MT.OC                    = D.[vch_od]                                   
    
    WHERE     D.[vch_empresa_editada] = 'OC'
    
    GROUP BY PD.[dtt_data_captura],
             D.[vch_empresa_editada], 
             D.[Flt Type],  MT.AD, D.[dtt_data]  
    
    --Select * from #TARIFASOCMETRO
    
     -----------------------------------------------------------------------------------------------------------------
    --MÉDIA TARIFADA 
    
    BEGIN TRY DROP TABLE #TARIFASINDMETRO END TRY BEGIN CATCH END CATCH
    SELECT 
              
            'IND' AS industria,
             PD.[dtt_data_captura],
             AVG(PD.flt_base) AS flt_base, 
             D.[Flt Type], 
             MT.AD,
    		 D.[dtt_data]
    
    
        INTO #TARIFASINDMETRO
        FROM #Data D
             INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
                                       PD.[dtt_geracao_ql2]     = D.QTS AND
                                       PD.[vch_empresa_editada] = D.[vch_empresa_editada] AND
                                       --PD.[vch_tipo_arquivo]    = D.[vch_tipo_arquivo] AND
                                       PD.[vch_od]              = D.[vch_od] AND
    								   PD.[dtt_data]            = D.[dtt_data] AND
                                       PD.[Flt Type]            = D.[Flt Type]
                                     
    		 INNER JOIN @Metro MT   ON MT.OC                    = D.[vch_od]                                                                      
                                      
    
    WHERE     D.[vch_empresa_editada] IN ('G3','JJ','OC') 
    
    
    GROUP BY PD.[dtt_data_captura],
             D.[Flt Type], MT.AD, D.[dtt_data]

    Obrigada

    segunda-feira, 27 de março de 2017 17:58
  • Excelente Elton,

    Dá para compreeder, mas ao montar na minha query apresenta o erro:

    Msg 102, Level 15, State 1, Line 2075
    Incorrect syntax near 'TAB'.

    Você sabe o que está acontecendo na sintaxe?

    Obrigada

    Thaís, confere se você copiou toda a consulta, criei tabelas no mesmo formato e não apresenta o erro.

    SELECT   'IND' AS industria,
             PD.[dtt_data_captura],
             AVG(PD.flt_base) AS flt_base, 
             D.[Flt Type], 
             MT.AD,
    		 D.[dtt_data], (flt_base1 + flt_base2 + flt_base3) / 3
    
    	FROM (
    
    		SELECT
    				(SELECT 
    						 'IND' AS industria,
    						 PD.[dtt_data_captura],
    						 AVG(PD.flt_base) AS flt_base1, 
    						 D.[Flt Type], 
    						 MT.AD,
    					INTO #TARIFASG3METRO
    					FROM #Data D
    							INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
    													PD.[Flt Type]            = D.[Flt Type]                                                  
    				WHERE     D.[vch_empresa_editada] = 'G3'
    				GROUP BY PD.[dtt_data_captura] ) as flt_base1,
    
    				(  SELECT 
    						 'IND' AS industria,
    						 PD.[dtt_data_captura],
    						 AVG(PD.flt_base) AS flt_base1, 
    						 D.[Flt Type], 
    						 MT.AD,
    					INTO #TARIFASG3METRO
    					FROM #Data D
    							INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
    													PD.[Flt Type]            = D.[Flt Type]                                                  
    				WHERE     D.[vch_empresa_editada] = 'JJ'
    				GROUP BY PD.[dtt_data_captura] ) as flt_base2,
    				
    				(SELECT 
    						 'IND' AS industria,
    						 PD.[dtt_data_captura],
    						 AVG(PD.flt_base) AS flt_base1, 
    						 D.[Flt Type], 
    						 MT.AD,
    					INTO #TARIFASG3METRO
    					FROM #Data D
    							INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
    													PD.[Flt Type]            = D.[Flt Type]                                                  
    				WHERE     D.[vch_empresa_editada] = 'OC'
    				GROUP BY PD.[dtt_data_captura] ) as flt_base3
    	) TAB
    
    Este é o código que testei e apresenta o erro Msg 102, Level 15, State 1, Line 2075
    Incorrect syntax near 'TAB'.

    segunda-feira, 27 de março de 2017 18:44
  • SELECT   'IND' AS industria,
             PD.[dtt_data_captura],
             AVG(PD.flt_base) AS flt_base, 
             D.[Flt Type], 
             MT.AD,
    		 D.[dtt_data], (flt_base1 + flt_base2 + flt_base3) / 3
    		 INTO #TARIFAS_CONSOLIDADO
    
    	FROM (
    
    		SELECT
    				(SELECT 
    						 'IND' AS industria,
    						 PD.[dtt_data_captura],
    						 AVG(PD.flt_base) AS flt_base1, 
    						 D.[Flt Type], 
    						 MT.AD--,
    					--INTO #TARIFASG3METRO
    					FROM #Data D
    							INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
    													PD.[Flt Type]            = D.[Flt Type]                                                  
    				WHERE     D.[vch_empresa_editada] = 'G3'
    				GROUP BY PD.[dtt_data_captura] ) as flt_base1,
    
    				(  SELECT 
    						 'IND' AS industria,
    						 PD.[dtt_data_captura],
    						 AVG(PD.flt_base) AS flt_base1, 
    						 D.[Flt Type], 
    						 MT.AD--,
    					--INTO #TARIFASG3METRO
    					FROM #Data D
    							INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
    													PD.[Flt Type]            = D.[Flt Type]                                                  
    				WHERE     D.[vch_empresa_editada] = 'JJ'
    				GROUP BY PD.[dtt_data_captura] ) as flt_base2,
    				
    				(SELECT 
    						 'IND' AS industria,
    						 PD.[dtt_data_captura],
    						 AVG(PD.flt_base) AS flt_base1, 
    						 D.[Flt Type], 
    						 MT.AD--,
    					--INTO #TARIFASG3METRO
    					FROM #Data D
    							INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
    													PD.[Flt Type]            = D.[Flt Type]                                                  
    				WHERE     D.[vch_empresa_editada] = 'OC'
    				GROUP BY PD.[dtt_data_captura] ) as flt_base3
    	) TAB

    Thais,

    A utilização das tabelas temporárias dentro daquele miolo de código não é possível.

    Tente desta forma que eu montei, extraindo os dados de #TARIFAS_CONSOLIDADO.

    SELECT * FROM #TARIFAS_CONSOLIDADO;


    segunda-feira, 27 de março de 2017 18:53
  • SELECT   'IND' AS industria,
             PD.[dtt_data_captura],
             AVG(PD.flt_base) AS flt_base, 
             D.[Flt Type], 
             MT.AD,
    		 D.[dtt_data], (flt_base1 + flt_base2 + flt_base3) / 3
    		 INTO #TARIFAS_CONSOLIDADO
    
    	FROM (
    
    		SELECT
    				(SELECT 
    						 'IND' AS industria,
    						 PD.[dtt_data_captura],
    						 AVG(PD.flt_base) AS flt_base1, 
    						 D.[Flt Type], 
    						 MT.AD--,
    					--INTO #TARIFASG3METRO
    					FROM #Data D
    							INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
    													PD.[Flt Type]            = D.[Flt Type]                                                  
    				WHERE     D.[vch_empresa_editada] = 'G3'
    				GROUP BY PD.[dtt_data_captura] ) as flt_base1,
    
    				(  SELECT 
    						 'IND' AS industria,
    						 PD.[dtt_data_captura],
    						 AVG(PD.flt_base) AS flt_base1, 
    						 D.[Flt Type], 
    						 MT.AD--,
    					--INTO #TARIFASG3METRO
    					FROM #Data D
    							INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
    													PD.[Flt Type]            = D.[Flt Type]                                                  
    				WHERE     D.[vch_empresa_editada] = 'JJ'
    				GROUP BY PD.[dtt_data_captura] ) as flt_base2,
    				
    				(SELECT 
    						 'IND' AS industria,
    						 PD.[dtt_data_captura],
    						 AVG(PD.flt_base) AS flt_base1, 
    						 D.[Flt Type], 
    						 MT.AD--,
    					--INTO #TARIFASG3METRO
    					FROM #Data D
    							INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
    													PD.[Flt Type]            = D.[Flt Type]                                                  
    				WHERE     D.[vch_empresa_editada] = 'OC'
    				GROUP BY PD.[dtt_data_captura] ) as flt_base3
    	) TAB

    Thais,

    A utilização das tabelas temporárias dentro daquele miolo de código não é possível.

    Tente desta forma que eu montei, extraindo os dados de #TARIFAS_CONSOLIDADO.

    SELECT * FROM #TARIFAS_CONSOLIDADO;


    BEGIN TRY DROP TABLE #TARIFAS_CONSOLIDADO END TRY BEGIN CATCH END CATCH
      SELECT 
              
            'IND' AS industria,
             PD.[dtt_data_captura],
             AVG(PD.flt_base) AS flt_base1, 
             D.[Flt Type], 
             MT.AD,
    		 D.[dtt_data],
    		 (flt_base1 + flt_base2 + flt_base3) / 3
    
    
        INTO #TARIFAS_CONSOLIDADO
    	FROM (
    
    		SELECT 
    
    			(SELECT		
    						'IND' AS industria,
    						 PD.[dtt_data_captura],
    						 AVG(PD.flt_base) AS flt_base1, 
    						 D.[Flt Type], 
    						 MT.AD,
    						 D.[dtt_data]
    
        FROM #Data D
             INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
                                       PD.[dtt_geracao_ql2]     = D.QTS AND
                                       PD.[vch_empresa_editada] = D.[vch_empresa_editada] AND
                                       --PD.[vch_tipo_arquivo]    = D.[vch_tipo_arquivo] AND
                                       PD.[vch_od]              = D.[vch_od] AND
    								   PD.[dtt_data]            = D.[dtt_data] AND
                                       PD.[Flt Type]            = D.[Flt Type]
                                     
    		 INNER JOIN @Metro MT   ON MT.OC                    = D.[vch_od]                                                                      
                                      
    
    WHERE     D.[vch_empresa_editada] = ('G3') 
    
    
    GROUP BY PD.[dtt_data_captura],
             D.[Flt Type], MT.AD, D.[dtt_data], PD.[dtt_data_captura],
    
    		 (SELECT		
    						'IND' AS industria,
    						 PD.[dtt_data_captura],
    						 AVG(PD.flt_base) AS flt_base2, 
    						 D.[Flt Type], 
    						 MT.AD,
    						 D.[dtt_data]
    
        FROM #Data D
             INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
                                       PD.[dtt_geracao_ql2]     = D.QTS AND
                                       PD.[vch_empresa_editada] = D.[vch_empresa_editada] AND
                                       --PD.[vch_tipo_arquivo]    = D.[vch_tipo_arquivo] AND
                                       PD.[vch_od]              = D.[vch_od] AND
    								   PD.[dtt_data]            = D.[dtt_data] AND
                                       PD.[Flt Type]            = D.[Flt Type]
                                     
    		 INNER JOIN @Metro MT   ON MT.OC                    = D.[vch_od]                                                                      
                                      
    
    WHERE     D.[vch_empresa_editada] = ('JJ') 
    
    
    GROUP BY PD.[dtt_data_captura],
             D.[Flt Type], MT.AD, D.[dtt_data], PD.[dtt_data_captura],
    
    			( SELECT		
    						'IND' AS industria,
    						 PD.[dtt_data_captura],
    						 AVG(PD.flt_base) AS flt_base3, 
    						 D.[Flt Type], 
    						 MT.AD,
    						 D.[dtt_data]
    
        FROM #Data D
             INNER JOIN #PreData PD ON PD.GrupoQuery            = D.GrupoQuery AND
                                       PD.[dtt_geracao_ql2]     = D.QTS AND
                                       PD.[vch_empresa_editada] = D.[vch_empresa_editada] AND
                                       --PD.[vch_tipo_arquivo]    = D.[vch_tipo_arquivo] AND
                                       PD.[vch_od]              = D.[vch_od] AND
    								   PD.[dtt_data]            = D.[dtt_data] AND
                                       PD.[Flt Type]            = D.[Flt Type]
                                     
    		 INNER JOIN @Metro MT   ON MT.OC                    = D.[vch_od]                                                                      
                                      
    
    WHERE     D.[vch_empresa_editada] = ('OC') 
    
    
    GROUP BY PD.[dtt_data_captura],
             D.[Flt Type], MT.AD, D.[dtt_data], PD.[dtt_data_captura] 
    
    	) TAB
    
    SELECT * FROM #TARIFAS_CONSOLIDADO

    Eu coloquei o seguinte código acima.

    E ainda me aponta 

    Msg 102, Level 15, State 1, Line 2087
    Incorrect syntax near 'TAB'.

    :/


    segunda-feira, 27 de março de 2017 19:08
  • Thais,

    Você precisa realmente fazer isso tudo no mesmo select, talvez poderia quebrar o seu código em partes, pensei em algo do tipo uma stored procedures.

    Já pensou nisso?


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 28 de março de 2017 00:08
  • Thais,

    Você precisa realmente fazer isso tudo no mesmo select, talvez poderia quebrar o seu código em partes, pensei em algo do tipo uma stored procedures.

    Já pensou nisso?


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    Pedro bom dia,

    Não havia pensado nisso. Pode dar uma ideia de código para começar?

    Estou tentando colocar tudo no mesmo select.

    Obrigada

    terça-feira, 28 de março de 2017 10:49
  • Deleted
    • Marcado como Resposta Robson William Silva quarta-feira, 29 de março de 2017 16:19
    • Não Marcado como Resposta Thaís Sakamoto sexta-feira, 7 de abril de 2017 19:00
    • Marcado como Resposta Thaís Sakamoto sexta-feira, 7 de abril de 2017 19:03
    • Não Marcado como Resposta Thaís Sakamoto segunda-feira, 10 de abril de 2017 13:44
    • Marcado como Resposta Thaís Sakamoto segunda-feira, 10 de abril de 2017 14:41
    terça-feira, 28 de março de 2017 21:29
  • Thaís, me parece que você pode realizar tudo em uma única consulta. Considerando-se o código inicial que postou neste tópico, bem como o modelo de resultado esperado, eis uma sugestão:

    -- código #1
    IF Object_ID('tempDB..#TARIFASP0METRO', 'U') is not null then DROP TABLE #TARIFASP0METRO;

    SELECT [dtt_data_captura], [flt_base P1]= Avg(case when D.[vch_empresa_editada] = 'P1' then PD.[flt_base] else NULL end), [flt_base P2]= Avg(case when D.[vch_empresa_editada] = 'P2' then PD.[flt_base] else NULL end), [flt_base P3]= Avg(case when D.[vch_empresa_editada] = 'P3' then PD.[flt_base] else NULL end), flt_base= Avg(PD.[flt_base]) --into #TARIFASP0METRO from #Data as D inner join #PreData as PD on PD.GrupoQuery = D.GrupoQuery and PD.[Flt Type] = D.[Flt Type]
    where D.[vch_empresa_editada] in ('P1', 'P2', 'P3') group by PD.[dtt_data_captura];

     

    Utilizando o código que transcreveu posteriormente:

    -- código #2 v2
    IF Object_ID('tempDB..#TARIFASINDMETRO', 'U') is not null then DROP TABLE #TARIFASINDMETRO;
              
    SELECT 'IND' AS industria,
           PD.[dtt_data_captura],
           [flt_base G3]= Avg(case when D.[vch_empresa_editada] = 'G3' then PD.[flt_base] else NULL end),  
           [flt_base JJ]= Avg(case when D.[vch_empresa_editada] = 'JJ' then PD.[flt_base] else NULL end),
           [flt_base OC]= Avg(case when D.[vch_empresa_editada] = 'OC' then PD.[flt_base] else NULL end),
           flt_base= Avg(PD.[flt_base]),
           D.[Flt Type], 
           MT.AD,
           D.[dtt_data]
      --into #TARIFASINDMETRO
      from #Data as D
           inner join #PreData as PD on PD.GrupoQuery = D.GrupoQuery and
                                        PD.[dtt_geracao_ql2] = D.QTS and
                                        PD.[vch_empresa_editada] = D.[vch_empresa_editada] and
                                       --PD.[vch_tipo_arquivo] = D.[vch_tipo_arquivo] and
                                        PD.[vch_od] = D.[vch_od] and
                                        PD.[dtt_data] = D.[dtt_data] and
                                        PD.[Flt Type] = D.[Flt Type]
            inner join @Metro as MT on MT.OC = D.[vch_od]
      where D.[vch_empresa_editada] in ('G3', 'JJ', 'OC') 
      group by PD.[dtt_data_captura], D.[Flt Type], MT.AD, D.[dtt_data];

     

    -----
    O Wesley postou uma observação que me pareceu pertinente. As médias que você apresenta na coluna "Valor Correto / AVERAGE" também me parecem incorretas. Por exemplo, na primeira linha, a média para
           (804,9 + 669,9 + NULL)  é 491,6, mas você informa 737,4. Se é para obter a média das 3 colunas, então a divisão tem que ser sempre por 3.

     

    -----
    Cuidado que a média aritmética de médias aritméticas nem sempre representa o mesmo que a média do todo.

    Por exemplo, se temos os seguintes dados:

    G3 JJ OC
    1 10 18 23
    2 20 89
    3 30 22 2

    teremos as seguintes médias aritméticas, por empresa:

    G3 JJ OC
    1 10 18 23
    2 20 89
    3 30 22 2
    média 20 20 38

    Se optamos por calcular qual é o valor usando a média aritmética das médias aritméticas, teremos
       (20 + 20 + 38) / 3 = 26

    Mas a média aritmética correta é
      (10 + 18 + 23 + 20 + 89 + 30 + 22 + 2) / 8 = 26,75.

    Para calcular média de médias, onde o número de casos de cada média varia, sugiro o uso da média aritmética ponderada:
      ((20 * 3) + (20 * 2) + (38 * 3)) / 8 = 26,75.

     

    -----
    Em tempo, a função AVG divide a somatória dos valores pelo número de casos, desconsiderando-se ausência de valores (NULL).


    e-mail       José Diz     Belo Horizonte, MG - Brasil




    Excelente José

    Deu super certo. Obrigada

    industria flt_base G3 flt_base JJ flt_base OC flt_base
    IND 1039.588 NULL NULL 1039.588
    IND 1039.588 NULL NULL 1039.588
    IND 1119.178 NULL NULL 1119.178
    IND 876.2333 597.2 NULL 776.5786
    IND 1119.178 517.7714 NULL 722.0226
    IND 876.2333 NULL NULL 876.2333
    IND 854.9 517.7714 NULL 559.9125
    IND 702.7571 517.7714 NULL 625.6798

    quinta-feira, 30 de março de 2017 11:49
  • Thaís, me parece que você pode realizar tudo em uma única consulta. Considerando-se o código inicial que postou neste tópico, bem como o modelo de resultado esperado, eis uma sugestão:

    -- código #1
    IF Object_ID('tempDB..#TARIFASP0METRO', 'U') is not null then DROP TABLE #TARIFASP0METRO;

    SELECT [dtt_data_captura], [flt_base P1]= Avg(case when D.[vch_empresa_editada] = 'P1' then PD.[flt_base] else NULL end), [flt_base P2]= Avg(case when D.[vch_empresa_editada] = 'P2' then PD.[flt_base] else NULL end), [flt_base P3]= Avg(case when D.[vch_empresa_editada] = 'P3' then PD.[flt_base] else NULL end), flt_base= Avg(PD.[flt_base]) --into #TARIFASP0METRO from #Data as D inner join #PreData as PD on PD.GrupoQuery = D.GrupoQuery and PD.[Flt Type] = D.[Flt Type]
    where D.[vch_empresa_editada] in ('P1', 'P2', 'P3') group by PD.[dtt_data_captura];

     

    Utilizando o código que transcreveu posteriormente:

    -- código #2 v2
    IF Object_ID('tempDB..#TARIFASINDMETRO', 'U') is not null then DROP TABLE #TARIFASINDMETRO;
              
    SELECT 'IND' AS industria,
           PD.[dtt_data_captura],
           [flt_base G3]= Avg(case when D.[vch_empresa_editada] = 'G3' then PD.[flt_base] else NULL end),  
           [flt_base JJ]= Avg(case when D.[vch_empresa_editada] = 'JJ' then PD.[flt_base] else NULL end),
           [flt_base OC]= Avg(case when D.[vch_empresa_editada] = 'OC' then PD.[flt_base] else NULL end),
           flt_base= Avg(PD.[flt_base]),
           D.[Flt Type], 
           MT.AD,
           D.[dtt_data]
      --into #TARIFASINDMETRO
      from #Data as D
           inner join #PreData as PD on PD.GrupoQuery = D.GrupoQuery and
                                        PD.[dtt_geracao_ql2] = D.QTS and
                                        PD.[vch_empresa_editada] = D.[vch_empresa_editada] and
                                       --PD.[vch_tipo_arquivo] = D.[vch_tipo_arquivo] and
                                        PD.[vch_od] = D.[vch_od] and
                                        PD.[dtt_data] = D.[dtt_data] and
                                        PD.[Flt Type] = D.[Flt Type]
            inner join @Metro as MT on MT.OC = D.[vch_od]
      where D.[vch_empresa_editada] in ('G3', 'JJ', 'OC') 
      group by PD.[dtt_data_captura], D.[Flt Type], MT.AD, D.[dtt_data];

     

    -----
    O Wesley postou uma observação que me pareceu pertinente. As médias que você apresenta na coluna "Valor Correto / AVERAGE" também me parecem incorretas. Por exemplo, na primeira linha, a média para
           (804,9 + 669,9 + NULL)  é 491,6, mas você informa 737,4. Se é para obter a média das 3 colunas, então a divisão tem que ser sempre por 3.

     

    -----
    Cuidado que a média aritmética de médias aritméticas nem sempre representa o mesmo que a média do todo.

    Por exemplo, se temos os seguintes dados:

    G3 JJ OC
    1 10 18 23
    2 20 89
    3 30 22 2

    teremos as seguintes médias aritméticas, por empresa:

    G3 JJ OC
    1 10 18 23
    2 20 89
    3 30 22 2
    média 20 20 38

    Se optamos por calcular qual é o valor usando a média aritmética das médias aritméticas, teremos
       (20 + 20 + 38) / 3 = 26

    Mas a média aritmética correta é
      (10 + 18 + 23 + 20 + 89 + 30 + 22 + 2) / 8 = 26,75.

    Para calcular média de médias, onde o número de casos de cada média varia, sugiro o uso da média aritmética ponderada:
      ((20 * 3) + (20 * 2) + (38 * 3)) / 8 = 26,75.

     

    -----
    Em tempo, a função AVG divide a somatória dos valores pelo número de casos, desconsiderando-se ausência de valores (NULL).


    e-mail       José Diz     Belo Horizonte, MG - Brasil




    Olá a todos

    Encontro-me com uma dúvida pertinente ao esse código. Ele funciona.

    Mas gostaria de entender um pouco mais de como está sendo calculado

    Quando rodo o script apresenta-me um resultado muito proximo de um =average (média entre as empresas G3,JJ e OC)

    Sendo que, estou calculando a média das colunas G3, JJ e OC e Tirando a média delas na coluna IND

    O script me dá o resultado da coluna IND - Resultado 699,74

    Mas ao tirar a prova no excel de uma média, tem uma diferença na coluna Avg (utilizei =average()) Resultado 668,70

    É uma diferença mínima, mas não consigo explicar ou identificar o porque

    Se eu comparo com uma média aritmética o resultado é outro muito diferente representado na coluna AvgA (utilizei = averagea()) Resultado 445,80

    Como disse eu gostaria de entender um pouco mais de como está sendo calculado.

    Segue todos os campos que utilizo como Group By

    A diferença chega a 327,30

    Obrigada


    segunda-feira, 10 de abril de 2017 13:44
  • Deleted
    • Marcado como Resposta Thaís Sakamoto segunda-feira, 10 de abril de 2017 14:41
    segunda-feira, 10 de abril de 2017 14:29
  • Deleted
    • Marcado como Resposta Thaís Sakamoto segunda-feira, 10 de abril de 2017 18:54
    segunda-feira, 10 de abril de 2017 14:40
  • Olá José

    Agradeço muito esta sua explicação

    É que eu estava pensando ainda na aritmética, e por isso não entendia os casos

    Mas ao verificar a Ponderada, ficou muito claro. 

    a media ponderada faz muito mais sentido do que media de médias 

    Nao acho correto dar o mesmo peso para todas as empresas sem considerar a oferta de cada uma.

    Então ficou muito bom

    Obrigada

    segunda-feira, 10 de abril de 2017 18:38