SQL 2000 - consulta demasiado lenta!

[Nevins02]

Boa noite a todos

Precisava da vossa ajuda, pois já derreti tutoriais de SQL e não consigo resolver o meu problema.

Tenho uma consulta que realizo em SQL Server 2005 e demora 5 segundos a devolver os registos da BD.

A mesma BD e a mesma consulta no SQL Server 2000 demora 50 minutos!!! Já tentei verificar logs, alerts, TUDO... e não consigo resolver o problema.

Segue-se abaixo a consulta:

declare @Mes as int
declare @Ano as int
select @Mes = 5
select @Ano = 2012


select (cast(l.chi_leil as varchar(5)))+(cast(l.chi_lote as varchar(20))) as Iden,
l.chi_leil as Leilao, l.chi_lote as Lote, c.chi_desc as DescLote,


(select top 1 l1.tdo_docu from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) as NVenda,
(select top 1 l1.mvd_ndoc from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) as NVendaNum,
(select top 1 convert(datetime, convert(varchar(10), l1.mvd_data, 105), 105) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) as NVendaData,
(select top 1 l1.mvd_enti from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) as NVendaCliente,

-- Valor da Compra
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
)*(-1) as ValorCompra,

-- Dados da Venda-> Tipo Doc., Nº, Data e Cliente
(select top 1 l1.tdo_docu from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) as Fact,
(select top 1 l1.mvd_ndoc from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) as FactNum,
(select top 1 convert(datetime, convert(varchar(10), l1.mvd_data, 105), 105) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) as FactData,
(select top 1 l1.mvd_enti from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) as FactCliente,

-- Valor da Venda
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
)*(-1) as ValorVenda,

-- Valor da Margem-> Valor da Venda - Valor da Compra
(
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi )
-
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi )
)*(-1) as Margem,

-- Valor do IVA-> Valor da Margem - (Valor da Margem/(1+Taxa IVA/100))
(((select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi )
-
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ))
-
round((
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi )
-
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi )
)/(1+(l.mvd_piva/100)),2))*(-1)
as VIVA

from gls_mvdl l, gls_chit c

where l.chi_iden = c.chi_iden and month(l.mvd_data) = @Mes and l.exe_ano = @Ano and l.sec_codi = 1
and (   (l.mvd_ctxt = 5 and l.mvd_dbcr = 'C') or (l.mvd_ctxt = 2 and l.mvd_dbcr = 'D')  )
and
-- deve existir documento a débito de compra
(select top 1 l1.mvd_iden from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) is not null
and
-- deve existir documento a crédito de venda
(select top 1 l1.mvd_iden from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) is not null
and
(
-- caso 1-> houve documentos a anular compra e venda anulando-se o negócio
(
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) >
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
),0)
and
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) >
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
),0)
)
or
-- caso 2-> para ir buscar os documentos de anulação caso haja documentos de venda e compra com data posterior no mesmo mes
-- e haja documentos de compra e venda com data em mes anterior, para se poder re-apurar o iva
(
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) <
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
),0)
and
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
) >
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) < month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
),0)
and
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) <
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
),0)
and
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) >
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) < month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
),0)

)
)

group by l.chi_leil, l.chi_lote, c.chi_desc, l.mvd_piva, month(l.mvd_data), l.exe_ano, l.sec_codi

UNION

-- Devolve os lotes em que houve anulação da venda no mês indicado.
select (cast(l.chi_leil as varchar(5)))+(cast(l.chi_lote as varchar(20))) as Iden,
l.chi_leil as Leilao, l.chi_lote as Lote, c.chi_desc as DescLote,

-- Dados de Compra-> Tipo Doc., Nº, Data e Fornecedor
(select top 1 l1.tdo_docu from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi ) as NVenda,
(select top 1 l1.mvd_ndoc from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi ) as NVendaNum,
(select top 1 convert(datetime, convert(varchar(10), l1.mvd_data, 105), 105) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi ) as NVendaData,
(select top 1 l1.mvd_enti from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) <= month(l.mvd_data)and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi ) as NVendaCliente,

-- Valor da Compra
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi ) as ValorCompra,

-- Dados da Venda-> Tipo Doc., Nº, Data e Cliente
(select top 1 l1.tdo_docu from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) as Fact,
(select top 1 l1.mvd_ndoc from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) as FactNum,
(select top 1 convert(datetime, convert(varchar(10), l1.mvd_data, 105), 105) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) as FactData,
(select top 1 l1.mvd_enti from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) as FactCliente,

-- Valor da Venda
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
)*(-1) as ValorVenda,

-- Valor da Margem-> Valor da Venda - Valor da Compra
((select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi )
-
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) <= month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi )) *(-1)
as Margem,

-- Valor do IVA-> Valor da Margem - (Valor da Margem/(1+Taxa IVA/100))
(
((select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi)
-
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) <= month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ))
)
-
round((
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi)
-
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) <= month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi)
)/(1+(l.mvd_piva/100)),2) as VIVA

from gls_mvdl l, gls_chit c

where l.chi_iden = c.chi_iden and month(l.mvd_data) = @Mes and l.exe_ano = @Ano and l.sec_codi = 1

-- contexto de vendas e que seja um documento a crédito
-- contexto de compras e que seja um documento a crédito
and (   (l.mvd_ctxt = 5 and l.mvd_dbcr = 'C') or (l.mvd_ctxt = 2 and (l.mvd_dbcr = 'C' or l.mvd_dbcr = 'A' or l.mvd_dbcr is null))  )
and
--deve existir documento de compra
(select top 1 l1.mvd_iden from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano) ) and l1.sec_codi = l.sec_codi ) is not null
and
-- deve existir documento a crédito de venda
(select top 1 l1.mvd_iden from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and
( (month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano) )
and l1.sec_codi = l.sec_codi ) is not null
and
-- documento de compra deve ser posterior ao documento de crédito de compra
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
(  (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
) >
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
),0)
and

(
-- caso 1-> o ultimo documento de venda é uma nota de crédito
((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) >
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano)  or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
),0))
or
-- caso 2-> o ultimo documento de venda é uma factura, mas houve antes uma nota de crédito
-- atenção que pode-se verificar 4 documentos no mesmo mes-> NCRE-FACT, NCRE-FACT, sempre com valores diferentes
-- neste caso deve-se ir buscar a 1ª NCRE, e a última FACT
(
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) <
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
),0)
and (
(
-- nesta condição tem um OR, para que vá buscar as notas de crédito de venda do mês caso tenha havido
-- documento de venda no mês anterior, e para que não vá buscar as notas de crédito do mês caso tenha
-- havido documentos de venda no mesmo mês, porque nesta ultima situação apenas conta a ultima factura/nota de venda
-- não sendo necessário regularizar o iva
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) >
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) < month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
),0)
and
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
(  (month(l1.mvd_data) < month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi) is not null
)
or
(
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) >
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
(  (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
),0)
and
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
(  (month(l1.mvd_data) < month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi) is null
)
) and
(select top 1 l1.mvd_iden from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) is null
)
)

group by l.chi_leil, l.chi_lote, c.chi_desc, l.mvd_piva, month(l.mvd_data), l.exe_ano, l.sec_codi


UNION

-- Devolve os lotes em que houve anulação da compra no mês indicado.
select (cast(l.chi_leil as varchar(5)))+(cast(l.chi_lote as varchar(20))) as Iden,
l.chi_leil as Leilao, l.chi_lote as Lote, c.chi_desc as DescLote,

-- Dados de Compra-> Tipo Doc., Nº, Data e Fornecedor
(select top 1 l1.tdo_docu from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) as NVenda,
(select top 1 l1.mvd_ndoc from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) as NVendaNum,
(select top 1 convert(datetime, convert(varchar(10), l1.mvd_data, 105), 105) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) as NVendaData,
(select top 1 l1.mvd_enti from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) as NVendaCliente,

-- Valor da Compra
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi )*(-1) as ValorCompra,

-- Dados da Venda-> Tipo Doc., Nº, Data e Cliente
(select top 1 l1.tdo_docu from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) as Fact,
(select top 1 l1.mvd_ndoc from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) as FactNum,
(select top 1 convert(datetime, convert(varchar(10), l1.mvd_data, 105), 105) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) as FactData,
(select top 1 l1.mvd_enti from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) as FactCliente,

-- Valor da Venda
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
)*(-1) as ValorVenda,

-- Valor da Margem-> Valor da Venda - Valor da Compra
((select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi )
-
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi )) * (-1)
as Margem,

-- Valor do IVA-> Valor da Margem - (Valor da Margem/(1+Taxa IVA/100))
(
((select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi)
-
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ))
)
-
round((
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi)
-
(select top 1 round(l1.mvd_valo,2) from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi)
)/(1+(l.mvd_piva/100)),2) as VIVA

from gls_mvdl l, gls_chit c

where l.chi_iden = c.chi_iden and month(l.mvd_data) = @Mes and l.exe_ano = @Ano and l.sec_codi = 1
-- contexto de vendas e que seja um documento a débito
-- contexto de compras e que seja um documento a débito
and (   (l.mvd_ctxt = 5 and (l.mvd_dbcr = 'D' or l.mvd_dbcr = 'A' or l.mvd_dbcr is null)) or (l.mvd_ctxt = 2 and l.mvd_dbcr = 'D')  )
and
--deve existir documento de venda
(select top 1 l1.mvd_iden from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi ) is not null
and
-- deve existir documento a débito de compra (nota de lançamento)
(select top 1 l1.mvd_iden from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) is not null
and
-- documento de venda deve ser posterior ao documento de crédito de venda
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'D' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
) >
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano)or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
),0)
and

(
-- caso 1-> o ultimo documento de compra é uma nota de lançamento
((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) >
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
),0))
or
-- caso 2-> o ultimo documento de compra é uma nota de venda, mas houve antes uma nota de lançamento
-- atenção que pode-se verificar 4 documentos no mesmo mes-> NTLC-NTVD, NTLC-NTVD, sempre com valores diferentes
-- neste caso deve-se ir buscar a 1ª NTLC, e a última NTVD
(
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) <
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
),0)
and (
(
-- nesta condição tem um OR, para que vá buscar as notas de lançamento de compra do mês caso tenha havido
-- documento de compra no mês anterior, e para que não vá buscar as notas de lançamento do mês caso tenha
-- havido documentos de compra no mesmo mês, porque nesta ultima situação apenas conta a ultima factura/nota de venda
-- não sendo necessário regularizar o iva
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) >
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) < month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
),0)
and
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) < month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi) is not null
)
or
(
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'D' and month(l1.mvd_data) = month(l.mvd_data) and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi
) >
isnull((select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) <= month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi
),0)
and
(select top 1 l1.mvd_iden
from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 2 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and (l1.mvd_dbcr = 'C' or l1.mvd_dbcr = 'A' or l1.mvd_dbcr is null) and
( (month(l1.mvd_data) < month(l.mvd_data) and l1.exe_ano = l.exe_ano) or (l1.exe_ano < l.exe_ano))
and l1.sec_codi = l.sec_codi) is null
)
) and
(select top 1 l1.mvd_iden from PCV_IVA_Lotes l1 where l1.mvd_ctxt = 5 and l1.chi_leil = l.chi_leil and l1.chi_lote = l.chi_lote
and l1.mvd_dbcr = 'C' and month(l1.mvd_data) = month(l.mvd_data)
and l1.exe_ano = l.exe_ano and l1.sec_codi = l.sec_codi ) is null
)
)

group by l.chi_leil, l.chi_lote, c.chi_desc, l.mvd_piva, month(l.mvd_data), l.exe_ano, l.sec_codi

order by l.chi_leil, l.chi_lote

[pmg]

Xiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii

Nao consegues separar isso numa carrada de views e usar as views no select final?

[Rui Carlos]

Suspeito que isso seja um problema de optimização da query. O SQL Server 2005 deve ter um motor de optimização de queries mais avançado, que consegue simplificar essa query para algo muito mais simples, e o SQL Server 2000 não consegue.

Penso que o SQL Server 2005 permite ver o plano de execução da query. Podes começar por aí, e ver se consegues optimizar a tua query.