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)