Hi people

Is there a way to put all this stuff into onr query eliminating the temp table?

There is a join from three tables::

ChequesCaixa- table that records checks from clients
CaixaMovimento- table that records the movimentation of the checks(banks,...)
Clientes-Clients

First I select the last movimentation of the cheque, discovering where the cheque is.
After I sum the value of the the cheques filtering some conditions

WITH ChequesCliente (ID,valor,vencimento,status,emissao,clienteID)
AS
(	
SELECT chequescaixa.id,chequescaixa.valor,data,chequescaixa.status,emissao,chequescaixa.donoID from chequescaixa
	where donoID in (select ID from creditocliente where databasecreditoauto IS NULL) and chequescaixa.status in (1,4)
)
SELECT distinct mv.id,mv.transID,mv.es,mv.docid,mv.caixaid,cxE.tipo as caixatipo,chCli.clienteID,
cxE.sigla,mv.caddate as UltimaMovimentacao,mv.status as statusmov,chcli.Id as chequeID,
chcli.valor,chcli.vencimento,chcli.status into #aux
FROM caixamovimento mv
	inner join caixaempresa cxE on mv.caixaid=cxE.id
	right join ChequesCliente chCli on chCli.id=mv.docID
	WHERE mv.id = (Select MAX(cast (id as numeric(10,0))) from caixamovimento where docid = mv.docid)
	
select clienteID,ISNULL(sum(valor),0) as ChCompensar from #aux
where
(UltimaMovimentacao IS NULL) or
(es='E' and caixatipo='EMP' and status<>4 and statusmov in (0,7,4)) or
(es='E' and caixatipo='EMP' and status=4 and statusmov=4) or
((es='S' or (es='E' and caixatipo='BCO')) and ((UltimaMovimentacao>=vencimento and UltimaMovimentacao>=@lddiaUtilCompensar) 
group by clienteID