Solved

Compare two rows in same table

Posted on 2014-04-27
6
1,142 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 19

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 35

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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

Author Closing Comment

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

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 76
Concat multiple records into one line 3 45
sql select total by week ending 3 26
Help with Progress 4gl Rounding Function 6 28
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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