[Resolvido] consulta

Pessoal,

Na query abaixo, preciso retornar a penúltima compra dos clientes. Porém não está correto, pois a consulta retorna a primeira compra ao invés da penúltima.

De forma isolada consigo retornar a ultima a penultima compra assim:

select CODCFO,max(DATAEMISSAO) as ultcompra
from TMOV where CODCFO = ''000675'' and CODTMV = ''2.2.01''

group by CODCFO

select top 1 CODCFO,DATAEMISSAO as penultcompra
from tmov
where CODCFO = ''000675''
and CODTMV = ''2.2.01''

and dataemissao < (select max(DATAEMISSAO)
 from TMOV
 where CODCFO = ''000675''
 and CODTMV = ''2.2.01'')

order by DATAEMISSAO desc

Essa é a consulta:

select * from (
select *, datediff(day,penultcomp,ultcomp) as Dias from (
select *,

(select top 1 dataemissao from tmov t WITH (NOLOCK) where codtmv = ''2.2.01'' and

t.codcfo = TGERAL.codcfo and t.codven1 =''0001'' and dataemissao < ultcomp) as penultcomp

from (select * from (select f.codcfo,nome,
(select max(dataemissao) as data from tmov t WITH (NOLOCK) where codtmv = ''2.2.01'' and

t.codcfo = f.codcfo and

t.codven1 =''0001''

group by t.codcfo) as ultcomp
,(select max(valorliquido) from tmov t WITH (NOLOCK) where codtmv = ''2.2.01'' and t.status <> ''C''
and t.codcfo = f.codcfo and

t.codven1 =''0001''

group by t.codcfo) as valor
,(select max(t.codven1) from tmov t WITH (NOLOCK) where codtmv = ''2.2.01'' and t.status <> ''C''
and t.codcfo = f.codcfo and

t.codven1 =''0001''

group by t.codcfo) as codven
from fcfo f
left join fcfodef fd (NOLOCK) on (f.codcfo = fd.codcfo)
left join fcfocompl fc (NOLOCK) on (f.codcfo = fc.codcfo)
where nome is not null and ativo = 1 and fc.cliforne = ''001''
)T1)TGERAL)TAUX where datediff(day,penultcomp,ultcomp) >= 180)
TULT where

codven =''0001''

and ultcomp >= ''01/08/2012''
and ultcomp <= ''31/08/2012''

order by nome