CREATE TABLE #temp1 (autoid int, numero int, cfop int) insert into #temp1 select NfeEF.AutoID, nfeef.numero, MAX(CFOp.Codigo) from TributacaoNFE inner join NfeEF on NfeEF.AutoID = TributacaoNFE.NFETributada inner join CFOp on CFOp.AutoId = TributacaoNFE.CFOPTributado where NfeEF.SituacaoNota = 'E' and NfeEF.DataEntrada >= '2009-01-01' AND TributacaoNFE.ImpostoTributado = 1 and NfeEF.FiltroEmpresa = (select Empresa.AutoId from Empresa where Empresa.Codigo = 'MT') group by NfeEF.AutoID, nfeef.numero HAVING COUNT(DISTINCT TributacaoNFE.CFOPTributado)=1 order by NfeEF.AutoID

CREATE TABLE #temp2 (autoid int, numero int, cfop int) insert into #temp2 select NfeEF.AutoID, NfeEF.Numero, MAX(CFOp.Codigo)  from ItemNFEProdutoEF inner join NfeEF on NfeEF.AutoID = ItemNFEProdutoEF.NFEItem inner join CFOp on CFOp.AutoId = ItemNFEProdutoEF.CFOPItem where NfeEF.SituacaoNota = 'E' and NfeEF.DataEntrada >= '2009-01-01' and NfeEF.FiltroEmpresa = (select Empresa.AutoId from Empresa where Empresa.Codigo = 'MT') group by NfeEF.AutoID, NfeEF.Numero HAVING COUNT(DISTINCT ItemNFEProdutoEF.CFOPItem)=1 order by NfeEF.AutoID

select numero from #temp2
where not autoid in (select autoid from #temp1)

select numero from #temp1
where not autoid in (select autoid from #temp2)

select #temp1.numero from #temp1
inner join #temp2 on #temp1.numero = #temp2.numero where #temp1.cfop <> #temp2.cfop

-- 2 CFOP informados

CREATE TABLE #temp3 (autoid int, numero int, cfop1 int, cfop2 int) insert into #temp3 select NfeEF.AutoID, NfeEF.Numero, MAX(cfop.Codigo), MIN(cfop.Codigo) from TributacaoNFE inner join NfeEF on NfeEF.AutoID = TributacaoNFE.NFETributada inner join CFOp on CFOp.AutoId = TributacaoNFE.CFOPTributado where NfeEF.SituacaoNota = 'E' and NfeEF.DataEntrada >= '2009-01-01' AND TributacaoNFE.ImpostoTributado = 1 and NfeEF.FiltroEmpresa = (select Empresa.AutoId from Empresa where Empresa.Codigo = 'MT') group by NfeEF.AutoID, NfeEF.Numero HAVING COUNT(DISTINCT TributacaoNFE.CFOPTributado)=2 order by NfeEF.AutoID

CREATE TABLE #temp4 (autoid int, numero int, cfop1 int, cfop2 int) insert into #temp4 select NfeEF.AutoID, NfeEF.Numero, MAX(CFOp.Codigo), MIN(CFOp.Codigo) from ItemNFEProdutoEF inner join NfeEF on NfeEF.AutoID = ItemNFEProdutoEF.NFEItem inner join CFOp on CFOp.AutoId = ItemNFEProdutoEF.CFOPItem where NfeEF.SituacaoNota = 'E' and NfeEF.DataEntrada >= '2009-01-01' and NfeEF.FiltroEmpresa = (select Empresa.AutoId from Empresa where Empresa.Codigo = 'MT') group by NfeEF.AutoID, NfeEF.Numero HAVING COUNT(DISTINCT ItemNFEProdutoEF.CFOPItem)=2 order by NfeEF.AutoID

select numero from #temp3
where not autoid in (select autoid from #temp4)

select numero from #temp4
where not autoid in (select autoid from #temp3)

select #temp3.numero from #temp3
inner join #temp4 on #temp3.numero = #temp4.numero where #temp3.cfop1 <> #temp4.cfop1 or #temp3.cfop2 <> #temp4.cfop2

-- + 2 CFOP informados

select NfeEF.Numero from TributacaoNFE
inner join NfeEF on NfeEF.AutoID = TributacaoNFE.NFETributada inner join CFOp on CFOp.AutoId = TributacaoNFE.CFOPTributado where NfeEF.SituacaoNota = 'E' and NfeEF.DataEntrada >= '2009-01-01' AND TributacaoNFE.ImpostoTributado = 1 and NfeEF.FiltroEmpresa = (select Empresa.AutoId from Empresa where Empresa.Codigo = 'MT') group by NfeEF.AutoID, NfeEF.Numero HAVING COUNT(DISTINCT TributacaoNFE.CFOPTributado)>2 order by NfeEF.AutoID

select NfeEF.Numero from ItemNFEProdutoEF inner join NfeEF on NfeEF.AutoID = ItemNFEProdutoEF.NFEItem inner join CFOp on CFOp.AutoId = ItemNFEProdutoEF.CFOPItem where NfeEF.SituacaoNota = 'E' and NfeEF.DataEntrada >= '2009-01-01' and NfeEF.FiltroEmpresa = (select Empresa.AutoId from Empresa where Empresa.Codigo = 'MT') group by NfeEF.AutoID, NfeEF.Numero HAVING COUNT(DISTINCT ItemNFEProdutoEF.CFOPItem)>2 order by NfeEF.AutoID

drop table #temp1
drop table #temp2
drop table #temp3
drop table #temp4