I need to create a query that calculates the average of a vendor "score". Here is the query, and then the 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 D1_PERC_ASSERT field is between 2 and -2 (results of the D1_ATRASO ), it receives value 100, otherwise, it receives 0.
With this query, I need to add a column that shows the average assertiveness per provider (field D1_PERC_ASSERT ), but I'm not able to, since the AVG command does not accept operations with alias.
Could you help me, please?