vendas_gerais_show

with
classmerc as (
 select distinct(prod_codigo) as codprod,
 (select a.grup_codigo from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 2)) as coddpto,
 (select a.grup_codigo from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 4)) as codsecao,
 (select a.grup_codigo from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 6)) as codcat,
 prod_grup_codigo as codfamilia,
 (select a.grup_descricao from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 2)) as descdpto,
 (select a.grup_descricao from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 4)) as descsecao,
 (select a.grup_descricao from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 6)) as desccat,
 prod_grup_nome as descfamilia
from produtos
left join grupos z on (prod_grup_codigo=z.grup_codigo)
),

vendas as (
select distinct(t1.mprd_prod_codigo) codprod,
 coalesce(round(sum(t1.mprd_valor),2),0.00) as valor
from movprodd17 as t1
where t1.mprd_datamvto between '2017-01-01' and '2017-11-30'
 and t1.mprd_unid_codigo='001'
 and t1.mprd_dcto_codigo IN ('7165','7320','7321','7325','7326','7327','7328','7329','7330','7331','7336','7337','7338','7339','7340')
 and t1.mprd_status='N'
group by t1.mprd_prod_codigo
),

compras as (
select distinct(t1.mprd_prod_codigo) codprod,
 coalesce(round(sum(t1.mprd_valor),2),0.00) as valor
from movprodd17 as t1
where t1.mprd_datamvto between '2017-01-01' and '2017-11-30'
 and t1.mprd_unid_codigo='001'
 and t1.mprd_dcto_codigo='7100'
 and t1.mprd_status='N'
group by t1.mprd_prod_codigo
)

select
 distinct(t1.mprd_prod_codigo),
 prod_descricao,
 prod_complemento,
 prod_marca,
 prod_grup_codigo,

 classmerc.descdpto,
 classmerc.descsecao,
 classmerc.desccat,

 prod_grup_nome,
 prod_comp_codigo,
 comp_nome,

 case when vendas.valor is not null then vendas.valor
 else '0.00'
 end as valor_vendas,

 case when compras.valor is not null then compras.valor
 else '0.00'
 end as valor_compras,

 coalesce(round((vendas.valor / sum(vendas.valor) over ())*100,4),0.0000) as part_vendas,
 coalesce(round((compras.valor / sum(compras.valor) over ())*100,4),0.0000) as part_compras,

 round(coalesce(((vendas.valor - compras.valor) / vendas.valor * 100),0.0000),4) as difpercvdcp,
 (coalesce(vendas.valor,0.00) - coalesce(compras.valor,0.00)) as difvalorvdcp

from movprodd17 as t1
INNER JOIN produtos on (prod_codigo=t1.mprd_prod_codigo)
INNER JOIN produn on (prun_prod_codigo=t1.mprd_prod_codigo and prun_unid_codigo='001')
LEFT JOIN compradores on (comp_codigo=prod_comp_codigo)
LEFT JOIN classmerc on (t1.mprd_prod_codigo=classmerc.codprod)
LEFT JOIN vendas on (t1.mprd_prod_codigo=vendas.codprod)
LEFT JOIN compras on (t1.mprd_prod_codigo=compras.codprod)
where t1.mprd_datamvto between '2017-01-01' and '2017-11-30'
 and t1.mprd_unid_codigo='001'
 and t1.mprd_dcto_tipo IN ('EVD','EAQ')
 and t1.mprd_status='N'
 --and CONDICAOESCOPO
group by
t1.mprd_prod_codigo,
prod_descricao,
prod_complemento,
prod_marca,
prod_grup_codigo,
classmerc.descdpto,
classmerc.descsecao,
classmerc.desccat,
prod_grup_nome,
prod_comp_codigo,
comp_nome,
vendas.valor,
compras.valor,
classmerc.coddpto,
classmerc.codsecao,
classmerc.codcat
order by t1.mprd_prod_codigo