Find customers who ordered in 2015 but not 2016

BHUC
BHUC used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

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

Commented:
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
Commented:
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

Commented:
...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