Ajuda Consulta Clientes Sem Movimentações.

[Vid. 2009]

Estou com está consulta que retorna os clientes sem movimentações num período de 6 meses , queria que a consulta retorna -se somente o CODCFO.

Desde já obrigado pela ajuda.

SELECT CODCFO, DATEDIFF(DAY,DATA, GETDATE())TEMPO FROM

(SELECT A.CODCFO, NOME, MAX(A.DATAEMISSAO)DATA

FROM TMOV A (NOLOCK) INNER JOIN FCFO B ON A.CODCFO = B.CODCFO AND

A.CODCOLIGADA = B.CODCOLIGADA INNER JOIN

GMUNICIPIO G ON G.CODMUNICIPIO = B.CODMUNICIPIO AND

G.CODETDMUNICIPIO = B.CODETD

WHERE A.CODCOLIGADA = '1' AND CODTMV IN ('2.2.01','2.2.02','2.2.07','2.2.30','2.2.37','2.2.38','2.2.40') AND

A.CODCFO NOT LIKE 'f_%' AND B.CFOIMOB ='0' AND G.NOMEMUNICIPIO LIKE 'dores_%' AND

STATUS <> 'C' GROUP BY A.CODCFO, NOME)A

WHERE DATEDIFF(DAY,DATA, GETDATE()) >= 180

AND NOT EXISTS

(SELECT CODCFO, DATAEXTRA1, STATUS, CODTMV FROM TMOV (NOLOCK)

WHERE CODCOLIGADA ='1' AND CODTMV IN ('2.2.01','2.2.02','2.2.07','2.2.30','2.2.37','2.2.38','2.2.40') AND STATUS <> 'C'AND

TMOV.DATAEMISSAO >= GETDATE() AND CODCFO = A.CODCFO)

Editado 27 de Dezembro de 2012 por Vid. 2009

[João Rodrigo]

Bom dia,

Talvez uma pesquisa mais simples resolva o seu caso:

SELECT CODCFO FROM FCFO
WHERE CODCFO NOT IN
(SELECT CODCFO FROM TMOV
WHERE DATAEMISSAO>=dateadd(month,(-6),getdate())
AND CODTMV IN ('2.2.01','2.2.02','2.2.07','2.2.30','2.2.37','2.2.38','2.2.40') AND STATUS <> 'C'
)
AND fcfo.ATIVO = 1

ou:

SELECT DISTINCT C.CODCFO FROM FCFO C
	 LEFT OUTER JOIN
(SELECT CODCOLCFO, CODCFO FROM TMOV
WHERE DATAEMISSAO>=dateadd(month,(-6),getdate())
AND CODTMV IN ('2.2.01','2.2.02','2.2.07','2.2.30','2.2.37','2.2.38','2.2.40') AND STATUS <> 'C'
) M
 ON M.CODCOLCFO = C.CODCOLIGADA AND M.CODCFO = C.CODCFO
WHERE C.ATIVO = 1

[Vid. 2009]

Muito Obrigado. Era isso mesmo que eu precisava.