I'm trying to found in DB SQL 2005 customers names that doesn't order or ask for quotation from one year. I'm trying this but it doesn't work, because it show me only documents older than one year, but show them also if in the current year other documents of the same customers are presents.
SELECT DISTINCT ANAGRAFICACF.CODCONTO,
DSCCONTO1,
'€ '+LEFT(TOTDOCUMENTO,LEN(TOTDOCUMENTO)-2),
CONVERT(VARCHAR(11),DATADOC,6),
TESTEDOCUMENTI.TIPODOC,
ANAGRAFICACF.PARTITAIVA,
ANAGRAFICACF.CODFISCALE,
ANAGRAFICARISERVATICF.NOTE1,
TESTEDOCUMENTI.DATADOC,
TESTEDOCUMENTI.CODAGENTE1,
ANAGRAFICAAGENTI.DSCAGENTE
FROM dbo.TESTEDOCUMENTI
INNER JOIN dbo.ANAGRAFICACF
ON CODCLIFOR=CODCONTO
INNER JOIN dbo.RIGHEDOCUMENTI
ON PROGRESSIVO=IDTESTA AND TOTNETTORIGA <>'0'
INNER JOIN dbo.ANAGRAFICARISERVATICF
ON CODCLIFOR=ANAGRAFICARISERVATICF.CODCONTO
LEFT JOIN dbo.ANAGRAFICAAGENTI
ON CODAGENTE=TESTEDOCUMENTI.CODAGENTE1
WHERE (dbo.TESTEDOCUMENTI.TIPODOC = 'ORDER' or dbo.TESTEDOCUMENTI.TIPODOC = 'QUOTATION') AND (dbo.TESTEDOCUMENTI.DATADOC < DATEADD(DAY, -365, GETDATE() ) AND NOT dbo.TESTEDOCUMENTI.DATADOC BETWEEN DATEADD(DAY, -365, GETDATE() ) AND GETDATE())
ORDER BY TESTEDOCUMENTI.DATADOC DESC
Thank you guys!
Aucun commentaire:
Enregistrer un commentaire