• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1623
  • Last Modified:

Compare two rows in same table

I have a table called Passes. In it we store each time a member purchases a season pass.

What I would like to do is compare and see who bought a pass in 2013, but not yet in 2014.

So entries are like this:

ID    HolderID    Year          Ticket
1          75          2013         Family
2          79          2013         Family
3          80          2013         Family
4          75          2014         Family
5          80          2014         Family

So the query would return that Holder 80 bought in 2013 but hasn't yet in 2014
0
BHUC
Asked:
BHUC
1 Solution
 
Raheman M. AbdulCommented:
From the table, Holder 80 bought in 2013 as well as in 2014. is that correct?
0
 
BHUCAuthor Commented:
Sorry. Family 79 would show as not having bought in 2014 yet.
0
 
Dan CraciunIT ConsultantCommented:
SELECT HolderID, MAX (Year) FROM Passes
WHERE HolderID NOT IN (
SELECT HolderID FROM Passes WHERE Year = '2014'
)
GROUP BY HolderID

This will show the IDs that have not bought a pass in 2014 and the most recent year when they bought a pass.

You can get rid of the GROUP BY if you only have 2013 and 2014 as years.

HTH,
Dan
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BHUCAuthor Commented:
Thank you!
0
 
Dan CraciunIT ConsultantCommented:
You're welcome.

Glad I could help!
0
 
ukerandiCommented:
SELECT HolderID, MAX (Year),Ticket FROM Passes
WHERE Year Not in( '2014')
GROUP BY HolderID,Ticket
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now