Given this table:
Purchase
========
PurchaseID (autoincrement int PK)
ClientID (int FK)
Year (int)
Amount (float)
Sample data:
insert into Purchase (ClientID, Year, Amount) values (1, 2009, 123)
insert into Purchase (ClientID, Year, Amount) values (2, 2009, 123)
insert into Purchase (ClientID, Year, Amount) values (2, 2010, 123)
insert into Purchase (ClientID, Year, Amount) values (3, 2010, 123)
insert into Purchase (ClientID, Year, Amount) values (3, 2007, 123)
insert into Purchase (ClientID, Year, Amount) values (4, 2010, 123)
insert into Purchase (ClientID, Year, Amount) values (4, 2008, 123)
Made a purchase in 2009 but not the following year (2010):
select p1.*
from Purchase p1
left outer join Purchase p2 on p1.ClientID = p2.ClientID and p1.Year = p2.Year - 1
where p2.ClientID is null
and p1.Year = 2009
Results:
PurchaseID Year ClientID Amount
----------- ----------- ----------- ---------------------
1 2009 1 123.00
Made a purchase in 2010, but not the two previous years (2008 or 2009):
select p3.*
from
Purchase p3
left outer join Purchase p2 on p3.ClientID = p2.ClientID and p3.Year = p2.Year + 1
left outer join Purchase p1 on p3.ClientID = p1.ClientID and p3.Year = p1.Year + 2
where p2.ClientID is null
and p1.ClientID is null
and p3.Year = 2010
Results:
PurchaseID Year ClientID Amount
----------- ----------- ----------- ---------------------
4 2010 3 123.00
