I have a relational db. Two pertinent tables for this question are the Account table and Membership table (1 to many). Each record in Membership is for a membership year. President wants to know who has not renewed for current year. My query is:
SELECT DISTINCT TOP (100) PERCENT YEAR(dbo.Membership.MemYear) AS MemYear, dbo.Users.NameLastForAlpha, dbo.Users.MailTo, dbo.Users.StreetAddress, dbo.Users.Address2, dbo.Users.City, dbo.StateLookup.State, dbo.Users.Zip, dbo.Users.PhoneHome, dbo.Membership.EmailNwsltr FROM dbo.Membership LEFT OUTER JOIN dbo.Users INNER JOIN dbo.StateLookup ON dbo.Users.StateID = dbo.StateLookup.StateID ON dbo.Membership.UserID = dbo.Users.UserID WHERE (dbo.Users.UserID NOT IN (SELECT UserID FROM dbo.Membership AS Membership_1 WHERE (YEAR(MemYear) = YEAR(GETDATE())))) ORDER BY dbo.Users.NameLastForAlpha
The problem is the result contains EVERY year the person was a member. This is 2016, so for example if David Bowie was a member in 2013 and 2014 he is listed twice. My question is how to have ONLY THE LAST YEAR he was a member?