Solved

Compare two rows in same table

Posted on 2014-04-27
6
975 Views
Last Modified: 2014-04-27
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
Comment
Question by:BHUC
6 Comments
 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 40026167
From the table, Holder 80 bought in 2013 as well as in 2014. is that correct?
0
 

Author Comment

by:BHUC
ID: 40026178
Sorry. Family 79 would show as not having bought in 2014 yet.
0
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 40026196
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Closing Comment

by:BHUC
ID: 40026227
Thank you!
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40026229
You're welcome.

Glad I could help!
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 40026234
SELECT HolderID, MAX (Year),Ticket FROM Passes
WHERE Year Not in( '2014')
GROUP BY HolderID,Ticket
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now