Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- SUM(QTDEPEDIDOS) AS QTDEPEDIDOS,
- DASH.DESCRICAO AS CATEGORIA,
- CASE WHEN ROUND(SUM(DASH.CXS), 0) > 0 THEN SUM(QTDEPEDIDOS) / ROUND(SUM(DASH.CXS), 0) ELSE 0 END AS HISTQTDECAIXAS,
- CASE WHEN ROUND(SUM(DASH.ROL), 2) > 0 THEN SUM(QTDEPEDIDOS) / ROUND(SUM(DASH.ROL), 2 )ELSE 0 END AS HISTROL,
- CASE WHEN ROUND(SUM(DASH.ROB), 2) > 0 THEN SUM(QTDEPEDIDOS) / ROUND(SUM(DASH.ROB), 2) ELSE 0 END AS HISTROB,
- CASE WHEN ROUND(SUM(DASH.MB), 2) > 0 THEN SUM(QTDEPEDIDOS) / ROUND(SUM(DASH.MB), 2)ELSE 0 END AS HISTVALORMARGEMBRUTA,
- CASE WHEN SUM(DASH.MB) > 0 THEN SUM(QTDEPEDIDOS) / (ROUND((SUM(DASH.ROL) / SUM(DASH.MB)), 2)) ELSE 0 END AS HISTPERCMARGEMBRUTA,
- CASE WHEN ROUND(SUM(DASH.MC), 2) > 0 THEN SUM(QTDEPEDIDOS) / ROUND(SUM(DASH.MC), 2) ELSE 0 END AS HISTVALORMARGEMCONTRIBUICAO,
- CASE WHEN SUM(DASH.MC) > 0 THEN SUM(QTDEPEDIDOS) / (ROUND((SUM(DASH.ROL) / SUM(DASH.MC)), 2)) ELSE 0 END AS HISTPERCMARGEMCONTRIBUICAO,
- 0 AS HISTROLPARTICIPACAO,
- ROUND(SUM(DASH.IC), 2) AS HISTIC
- FROM (SELECT
- COUNT(NF.NUMNOTAFISCAL) AS QTDEPEDIDOS,
- CATEGORIAFOCO.DESCRICAO,
- SUM(COALESCE(NFI.QTDE, 0 )) AS CXS,
- MCM.RECEITABRUTA AS ROB,
- MCM.RECEITALIQUIDA AS ROL,
- MCM.MARGEMBRUTA AS MB,
- MCM.MARGEMCONTRIBUICAO AS MC,
- SUM(COALESCE(MOV.VALOR, 0)) AS IC
- FROM
- TE_NOTAFISCAL NF
- INNER JOIN TE_NOTAFISCALITEM NFI ON (NFI.NUMNOTAFISCAL = NF.NUMNOTAFISCAL AND NFI.NUMPEDIDOEMP = NF.NUMEROPEDIDOEMP)
- INNER JOIN TESP_CATEGFOCOPROD CATEGFOCOPRODUTO ON (CATEGFOCOPRODUTO.CODIGOPRODUTO = NFI.CODIGOPRODUTO)
- INNER JOIN TESP_CATEGORIASFOCO CATEGORIAFOCO ON (CATEGORIAFOCO.CODIGO = CATEGFOCOPRODUTO.CODIGOCATEGFOCO)
- LEFT JOIN T_PEDIDO PEDIDO ON (PEDIDO.NUMPEDIDOAFV = NF.CESP_NUMPEDAFV)
- INNER JOIN TESP_MARGEMCLIENTEMES MCM ON (MCM.MES = CONVERT(VARCHAR, DATEPART(MM, NF.DATAEMISSAO)) AND
- MCM.ANO = CONVERT(VARCHAR, SUBSTRING(CONVERT(VARCHAR, DATEPART(YYYY, NF.DATAEMISSAO)), 0, 5)) AND
- MCM.CODIGOCLIENTE = NF.CODIGOCLIENTE AND
- MCM.CODIGOPRODUTO = CATEGFOCOPRODUTO.CODIGOPRODUTO)
- LEFT JOIN TESP_ESCRITURACAO ESC ON (PEDIDO.NUMPEDIDOAFV = ESC.NUMPEDIDOAFV)
- LEFT JOIN TESP_MOVIMENTACAO MOV ON (ESC.CODIGO = MOV.CODIGO)
- WHERE
- NF.CODIGOCLIENTE = '88882' AND
- CONVERT(DATETIME, NF.DATAEMISSAO, 103) BETWEEN
- CONVERT(DATETIME, '01/1/2017', 103) AND
- CONVERT(DATETIME, '30/6/2017', 103)
- GROUP BY MCM.RECEITABRUTA, MCM.RECEITALIQUIDA, MCM.MARGEMBRUTA, MCM.MARGEMCONTRIBUICAO, CATEGORIAFOCO.DESCRICAO) AS DASH
- GROUP BY DASH.DESCRICAO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement