query do milagre haha

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

with IcmsEntrada(mes, ano, Entrada, cnpj,chave) as 
(
select Month(DataSped), YEAR(DataSped), sum(cast(vl_icms as money)), CNPJ, CONCAT(YEAR(DataSped),Month(DataSped),CNPJ)
from Notas_Contribuicoes_Detalhes 
where COD_OPER = '+' and DataSped between '01/04/2013 00:00:00' and '31/01/2020 00:00:00' 
group by YEAR(DataSped), MONTH(DataSped), CNPJ),
IcmsSaida(mes, ano, Saida, cnpj,chave) as 
(
select Month(DataSped), YEAR(DataSped), sum(cast(vl_icms as money)),CNPJ, CONCAT(YEAR(DataSped),Month(DataSped),CNPJ)
from Notas_Contribuicoes_Detalhes 
where COD_OPER = '-'and DataSped between '01/04/2013 00:00:00' and '31/01/2020 00:00:00' 
group by YEAR(DataSped), MONTH(DataSped),CNPJ),
Total(cnpj,Entrada,Saida,BaseCalc,Pis,Cofins,TotalNaoAtualizado,ano,mes) as
(
select 
e.cnpj,
isnull(e.entrada,0) Entrada,
isnull(s.saida,0) Saida,
isnull(s.saida,0) - isnull(e.entrada,0) BaseCalc,
((isnull(s.Saida, 0) - isnull(e.Entrada, 0)) * 0.0165) as Pis,
(isnull(s.Saida, 0) - isnull(e.Entrada, 0)) * 0.076 as Cofins,
replace(((isnull(s.Saida, 0) - isnull(e.Entrada, 0)) * 0.0165) + (isnull(s.Saida, 0) - isnull(e.Entrada, 0)) * 0.076,'.',',')as TotalNaoAtualizado,
case 
when isnull(e.ano,0)=0
then s.ano
else e.ano
end as ano,
case
when isnull(e.mes,0)=0
then s.mes
else e.mes
end as mes
from IcmsEntrada e full outer join IcmsSaida s 
on 
e.ano = s.ano and
e.mes = s.mes and 
e.cnpj = s.cnpj
)
select
Entrada,
Saida,
BaseCalc,
replace(Pis,'.',',') as PIS,
replace(Cofins,'.',',') as Cofins,
TotalNaoAtualizado,
ano,
mes,
s.valor as selic,
Replace(((isnull(Saida, 0) - isnull(Entrada, 0)) * 0.0165) * ((s.valor / 100) + 1),'.',',') as PisCorrigido,
replace(((isnull(Saida, 0) - isnull(Entrada, 0)) * 0.076) * ((s.valor / 100) + 1),'.',',') as CofinsCorrigido,
replace(((isnull(Saida, 0) - isnull(Entrada, 0)) * 0.0165) * ((s.valor / 100) + 1) + ((isnull(Saida, 0) - isnull(Entrada, 0)) * 0.076) * ((s.valor / 100) + 1),'.',',')as TotalAtualizado
from Total t left join PisCofins_Base..Selic s 
on YEAR(s.data_inicial) = t.ano
and MONTH (s.data_inicial) = t.mes