Find customers who ordered in 2015 but not 2016

BHUC used Ask the Experts™
I have  a table called Passes1 that shows every ticket sale we have ever made.
I want to run a query on customers that bought a ticket in 2015 but have not bought yet in 2016

I have this query -
SELECT * FROM Passes1 as pa WHERE NOT EXISTS (Select * from Passes1 as pa1 WHERE pa.THID=pa1.THID AND pa1.Year = '2016') AND Year ='2015'

Where am I off?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

or you do a LEFT JOIN:

select *
from Passes1 as P2015
left join Passes as P2016
on P2016.Year = '2016'
and P2016.THID = P2015.THID
where P2015.Year = '2015'
and P2016.THID is null

Open in new window

Software Developer
Your code should run, but would list customers of 2015 with each ticket. If you only want customers then only pick out the DISTINCT custoemrid instead of * or group by customerid

Eg you could also query

Select customerid from passes1 where Max(Year)=2015 group by customerid

Open in new window

Bye, Olaf.
Olaf DoschkeSoftware Developer

...more likely than a Year column you would have an orderdate column and would pick MAX(YEAR(orderdate)) = 2015

Bye, Olaf.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial