SELECT id,
                         grupo,
                         leaf,
                         preco_medioa preco_medio, preco_mediob, ROUND(100*(preco_mediob/preco_medioa-1),2) AS preco_mediox,
                         mba mb, mbb, ROUND(100*(mbb/mba-1),2) AS mbx,
                         roba rob, robb, ROUND(100*(robb/roba-1),2) AS robx,
                         qtdea qtde, qtdeb, ROUND(100*(qtdeb/qtdea-1),2) AS qtdex,
                         rola rol, rolb, ROUND(100*(rolb/rola-1),2) AS rolx,
                         cmva cmv, cmvb, ROUND(100*(cmvb/cmva-1),2) AS cmvx,
                         lba lb, lbb, ROUND(100*(lbb/lba-1),2) AS lbx
                    FROM (
                        SELECT 'REDE|REDE|'||'MARCA'||'|'||ID_MARCA AS id,
                            MARCA AS grupo,
                            'false' AS leaf,
                            ROUND(SUM(CASE WHEN data >= '01/01/2021' AND data <= '31/01/2021' THEN rob END)/SUM(CASE WHEN data >= '01/01/2021' AND data <= '31/01/2021' THEN qtde END),2) AS preco_medioa,
                            ROUND((CASE WHEN SUM(CASE WHEN data >= '01/01/2021' AND data <= '31/01/2021' THEN qtde END) > 0 THEN (SUM(NVL(CASE WHEN data >= '01/01/2021' AND data <= '31/01/2021' THEN rol END,0)-NVL(CASE WHEN data >= '01/01/2021' AND data <= '31/01/2021' THEN cmv END,0))/SUM(CASE WHEN data >= '01/01/2021' AND data <= '31/01/2021' THEN rol END))*100 END),2) AS mba,
                            SUM(CASE WHEN data >= '01/01/2021' AND data <= '31/01/2021' THEN rob END) AS roba,
                            SUM(CASE WHEN data >= '01/01/2021' AND data <= '31/01/2021' THEN qtde END) AS qtdea,
                            SUM(CASE WHEN data >= '01/01/2021' AND data <= '31/01/2021' THEN rol END) AS rola,
                            SUM(CASE WHEN data >= '01/01/2021' AND data <= '31/01/2021' THEN cmv END) AS cmva,
                            SUM(CASE WHEN data >= '01/01/2021' AND data <= '31/01/2021' THEN lb END) AS lba,
                            ROUND(SUM(CASE WHEN data >= '01/02/2021' AND data <= '28/02/2021' THEN rob END)/SUM(CASE WHEN data >= '01/02/2021' AND data <= '28/02/2021' THEN qtde END),2) AS preco_mediob,
                            ROUND((CASE WHEN SUM(CASE WHEN data >= '01/02/2021' AND data <= '28/02/2021' THEN qtde END) > 0 THEN (SUM(NVL(CASE WHEN data >= '01/02/2021' AND data <= '28/02/2021' THEN rol END,0)-NVL(CASE WHEN data >= '01/02/2021' AND data <= '28/02/2021' THEN cmv END,0))/SUM(CASE WHEN data >= '01/02/2021' AND data <= '28/02/2021' THEN rol END))*100 END),2) AS mbb,
                            SUM(CASE WHEN data >= '01/02/2021' AND data <= '28/02/2021' THEN rob END) AS robb,
                            SUM(CASE WHEN data >= '01/02/2021' AND data <= '28/02/2021' THEN qtde END) AS qtdeb,
                            SUM(CASE WHEN data >= '01/02/2021' AND data <= '28/02/2021' THEN rol END) AS rolb,
                            SUM(CASE WHEN data >= '01/02/2021' AND data <= '28/02/2021' THEN cmv END) AS cmvb,
                            SUM(CASE WHEN data >= '01/02/2021' AND data <= '28/02/2021' THEN lb END) AS lbb
                        FROM (SELECT 'REDE' AS id_rede, 'REDE' AS rede, vi.id_empresa, em.apelido AS empresa, ic.id_marca, m.descricao AS marca, NVL(mg.gestor,'G5 N/D') id_gestor, NVL(mg.gestor,'G5 N/D') AS gestor,
                                    --vi.id_empresa, em.apelido as empresa, ic.id_marca, m.descricao as marca,
                                    TRUNC(vi.data_emissao,'MM') AS data,
                                    SUM(vi.rob) AS rob,
                                    SUM(vi.qtde) AS qtde,
                                    SUM(vi.rol) AS rol,
                                    SUM(vi.custo) AS cmv,
                                    SUM(NVL(vi.rol,0)-NVL(vi.custo,0)) AS lb
                                FROM pricing.vm_ie_ve_venda_item vi,
                                    ms.tb_item_categoria ic,
                                    ms.tb_item i,
                                    ms.tb_categoria c,
                                    ms.empresa em,
                                    ms.tb_marca m,
                                    pricing.tb_marca_gestor mg
                                WHERE vi.id_item = ic.id_item
                                AND vi.id_categoria = ic.id_categoria
                                AND vi.id_item = i.id_item
                                AND vi.id_categoria = c.id_categoria
                                AND vi.id_empresa = em.id_empresa
                                AND ic.id_marca = m.id_marca
                                AND ic.id_marca = mg.id_marca(+)
                                AND vi.id_operacao IN (4,7)
                                AND vi.status_venda = 'A'
                                AND TRUNC(vi.data_emissao,'MM') <= '01/02/2021'
                                -- Data inicial B
                                AND TRUNC(vi.data_emissao,'MM') >= '01/01/2021'
                                -- Data final A
                                AND TRUNC(vi.data_emissao,'MM') <= '28/02/2021'
                                --and i.cod_item||c.descricao = 'JS00506.0'
                            GROUP BY TRUNC(vi.data_emissao,'MM'), vi.id_empresa, em.apelido, ic.id_marca, m.descricao, mg.gestor)
                        WHERE 1=1
                         AND ID_REDE = 'REDE'
                        GROUP BY REDE, MARCA, 'REDE|REDE|'||'MARCA'||'|'||ID_MARCA)
                    WHERE 1=1
                    ORDER BY GRUPO