SQL - AVG with vendor alias and grouping

Asked

Viewed 94 times

0

I need to create a query that calculates the average of a vendor delivery "score". It follows query, and in sequence, explanation of the fields:

SELECT distinct C7_FILIAL,
     CASE WHEN C7_FILIAL = '0201' THEN '0201 - METAIS'  
        WHEN C7_FILIAL = '0301' THEN '0301 - COMPONENTES'  
        WHEN C7_FILIAL = '0401' THEN '0401 - SISTEMAS'  
       END AS C7_FILIAL_DESC, 
            C7_FORNECE,
             C7_FORNECE +' - '+ A2_NOME as C7_DESC_FORNECE, 
            C7_NUM, 
            C7_PRODUTO,
             RTRIM(C7_PRODUTO) +' - '+ B1_DESC AS C7_DESC_PRODUTO, 
            B1_TIPO,
            C7_UM,
            C7_ITEM,
            C7_QUANT,
            C7_RESIDUO,
            CONVERT(DATETIME,C7_EMISSAO, 103) AS C7_EMISSAO,
            MONTH(C7_EMISSAO) C7_EMS_MES,
            CONVERT(DATETIME,C7_DATPRF, 103) AS C7_DATPRF,
            MONTH(C7_DATPRF) C7_MES,

            (select MAX(D1_DOC) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*') as D1_DOC,

           (select MAX( CONVERT(DATETIME,D1_DTDIGIT, 103)) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*') AS D1_DTDIGIT,

            (select SUM(D1_QUANT) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*') as D1_QUANT,
            (select SUM(D1_QTDEDEV) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*') as D1_DEV,

            ISNULL (DATEDIFF(D, C7_DATPRF, ISNULL((select MAX(D1_DTDIGIT) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*'), CONVERT(VARCHAR(8), GETDATE(), 112)) ), 0) AS D1_ATRASO,



   CASE WHEN (select MAX(D1_DTDIGIT) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*') <> ''
        THEN 
          CASE 
               WHEN DATEDIFF(D, C7_DATPRF, (select MAX(D1_DTDIGIT) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*')) IN (0) and C7_QUANT = (select SUM(D1_QUANT) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*') THEN 'NO PRAZO'
               WHEN DATEDIFF(D, C7_DATPRF, (select MAX(D1_DTDIGIT) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*')) IN (0) and C7_QUANT > (select SUM(D1_QUANT) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*') THEN 'ENTREGA PARCIAL'
               WHEN DATEDIFF(D, C7_DATPRF, (select MAX(D1_DTDIGIT) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*')) >= 1 THEN 'ATRASADO' 
               WHEN DATEDIFF(D, C7_DATPRF, (select MAX(D1_DTDIGIT) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*')) <= -1 THEN 'ADIANTADO'
          END

        WHEN ISNULL((select MAX(D1_DTDIGIT) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*'),0) = 0  AND  DATEDIFF(D, C7_DATPRF, CONVERT(VARCHAR(8), GETDATE(), 112)) >= 1  THEN 'ATRASADO' 
        WHEN ISNULL((select MAX(D1_DTDIGIT) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*'),0) = 0 OR ISNULL((select MAX(D1_DTDIGIT) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*'),0)IN (0)   AND  DATEDIFF(D, C7_DATPRF, CONVERT(VARCHAR(8), GETDATE(), 112)) < 0 AND DATEDIFF(D, C7_DATPRF, CONVERT(VARCHAR(8), GETDATE(), 112)) IN (0)   THEN 'NO PRAZO'

   END AS D1_STATUS_ENT,



   CASE 
    WHEN (SELECT ISNULL (DATEDIFF(D, C7_DATPRF, ISNULL((select MAX(D1_DTDIGIT) from SD1010 where D1_FILIAL = C7_FILIAL and D1_PEDIDO = C7_NUM and D1_COD = C7_PRODUTO and D1_ITEMPC = C7_ITEM AND D_E_L_E_T_ <> '*'), CONVERT(VARCHAR(8), GETDATE(), 112)) ), 0) AS D1_PERCENTUAL) BETWEEN -2 and 2
        THEN
            100     
        ELSE  0 


   END AS D1_PERC_ASSERT




   FROM SC7010 AS OC INNER JOIN SB1010 AS PR ON C7_PRODUTO = B1_COD AND PR.D_E_L_E_T_<> '*' and OC.D_E_L_E_T_ <> '*'


   AND OC.D_E_L_E_T_ <> '*' 

   INNER JOIN SA2010 AS FC ON C7_FORNECE = A2_COD and C7_LOJA = A2_LOJA
 --  and OC.C7_NUM = '005974'
    and C7_FILIAL <> '0501'
   --and C7_PRODUTO = '0030.0908'
   --WHERE C7_FORNECE = '000020'
   GROUP BY C7_FILIAL,C7_PRODUTO, C7_FORNECE, A2_NOME, C7_NUM,B1_DESC, B1_TIPO,C7_RESIDUO, C7_UM,C7_ITEM, C7_QUANT,C7_EMISSAO, C7_DATPRF

The alias D1_PERC_ASSERT is the percentage of assertiveness. If the field D1_PERC_ASSERT is between 2 and -2 (alias results D1_ATRASO), it gets value 100, otherwise it gets 0.

With this query, I need to add a column that shows the assertiveness average per vendor (field D1_PERC_ASSERT ), but I’m not succeeding, because the command AVG does not accept alias operations.

Could you help me, please?

sql database sql-server query

  • Have you ever tried to make a scalar function?

  • No. How could I do this function?

  • Take a look at this link: https://msdn.microsoft.com/pt-br/library/ms131043(v=sql.120). aspx

  • When your select gets too big, the ideal is to break it into smaller selects or into a table Function

  • One way out is to use your sql as a virtual table, then avg in the alias column , something like select avg(assertiveness) from (select ...)

No answers

Browser other questions tagged sql database sql-server query

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