if statement - SQL Server 2005 : find customers that doesn't order from one year -

i'm trying find customers names in sql server 2005 database don't have orders, or ask quotation 1 year. i'm trying doesn't work, because show me documents older 1 year, show them if in current year other documents of same customers 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      dbo.testedocumenti  inner join      dbo.anagraficacf on codclifor = codconto inner join      dbo.righedocumenti on progressivo = idtesta , totnettoriga <> '0' inner join      dbo.anagraficariservaticf on codclifor = anagraficariservaticf.codconto left join      dbo.anagraficaagenti on codagente = testedocumenti.codagente1       (dbo.testedocumenti.tipodoc = 'order' or      dbo.testedocumenti.tipodoc = 'quotation')      , (dbo.testedocumenti.datadoc < dateadd(day, -365, getdate())   order      testedocumenti.datadoc desc 

this

codconto | dscconto1 | totdocumento| datadoc | tipodoc| codagente| dscagente| ---------+-----------+-------------+---------+--------+----------+----------+ c 10395  |caio|€ 1048.22    |03 aug 15  |orc    |2015-08-03 00:00:00.000    |a     4    |f c 51282 |sempronio|€ 2217.41    |03 aug 15  |pcl    |2015-08-03 00:00:00.000        |null c 10120 |pinco |€ 689.54    |03 aug 15  |pcl    |2015-08-03 00:00:00.000    |a     4    |f  c   746 |tizio  |€ 205.45   |03 aug 15  |orc    |2015-08-03 00:00:00.000    |a     4    |f 

solution (thank @scsimon) add condition, not in:

         dbo.testedocumenti.tipodoc = 'fvc' , dbo.testedocumenti.datadoc < dateadd(day, -365, getdate())                 , testedocumenti.codclifor not in (select distinct testedocumenti.codclifor                     dbo.testedocumenti                       dbo.testedocumenti.tipodoc = 'fvc' , dbo.testedocumenti.datadoc > dateadd(day, -365, getdate())                     )  hope help, if else have request.  

you want use not exists. if anagraficacf.codconto customernumber below should work you. since column names aren't self-explanatory, change them need if incorrect. but, logic of not exists want.

remove:

and not dbo.testedocumenti.datadoc between dateadd(day, -365, getdate() ) , getdate()) 

add:

and anagraficacf.codconto not in                  (select distinctanagraficacf.codconto                  dbo.anagraficacf                 inner join                   dbo.testedocumenti on codclifor = codconto                                    dbo.testedocumenti.datadoc > dateadd(day, -365, getdate())) 

sample data

click here run code

if object_id('tempdb..#agents') not null drop table #agents if object_id('tempdb..#items') not null drop table #items  create table #agents (agentid varchar(2), name varchar(50)) insert #agents values ('a1','julius cesar'), ('b2','albert einstien'), ('c3','frank thomas')   create table #items (agentid varchar(2), itemid int, itemname varchar(50), dt datetime) insert #items (agentid, itemid, itemname, dt) values --notice juliues cesar has items in current year, , older year ('a1',1,'apple','7/1/2015'), ('a1',2,'pear','7/1/2016'), ('a1',3,'watermelon','12/1/2015'), ('a1',4,'grape','1/1/2015'),  --notice albert einstien has items older year  ('b2',5,'car','7/1/2015'), ('b2',6,'truck','5/1/2015'), ('b2',7,'van','3/1/2015'),  --notice frank thomas has items older year ('c3',8,'car','7/1/2014'), ('c3',9,'truck','5/1/2014'), ('c3',10,'van','3/1/2014')   select      a.name     ,i.*       #items     inner join #agents on      a.agentid = i.agentid     --items older year     dt < dateadd(day, -365, getdate())     , i.agentid not in (select distinct agentid #items dt > dateadd(day, -365, getdate()))