SQLTeam.com | Weblogs | Forums

Where "not in (select..." question - need latest year, not all

#1

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?

#3

my bad I accidental click delete

sample data?

instead
(dbo.Users.UserID NOT IN
(SELECT UserID
FROM dbo.Membership AS Membership_1
WHERE (YEAR(MemYear) = YEAR(GETDATE()))))

try
Membership_1.memyear=(select max(memyear) from dbo.membership m from Membership_1."yourkey"=m."yourkey")

#4

The technique (row_number ...) used below, comes in handy in many situations, so if you're going to work alot with sql, you should learn this.

It makes a temporary column (rn) with a sequence for each user starting from 1 and counts up. As you want the newest memyear, we sort in reverse order.

Ex. if you have user table like this:

userid memyear
1      2014-01-14
1      2015-01-05
1      2016-01-07
2      2014-02-04
2      2015-02-23

and you apply the temporary column (row_number() over(partition by userid order by memyear desc) as rn), it makes sequence like this:

userid memyear    rn
1      2014-01-14 3
1      2015-01-05 2
1      2016-01-07 1
2      2014-02-04 2
2      2015-02-23 1

Now it's only a matter of getting rows where rn=1, and the memyear is less than current year.

For better readability for others (and yourself later on), please get a habbit of formating you queries. Here's a cool nice-ifier.

select m.memyear
      ,u.namelastforalpha
      ,u.mailto
      ,u.streetaddress
      ,u.address2
      ,u.city
      ,s.state
      ,u.zip
      ,u.phonehome
      ,m.emailnwsltr
  from (select userid
              ,year(memyear) as memyear
              ,emailnwsltr
              ,row_number() over(partition by userid order by memyear desc) as rn
          from dbo.membership
       ) as m
       left outer join dbo.users as u
                    on u.userid=m.userid
       inner join dbo.statelookup as s
               on s.stateid=u.stateid
 where m.memyear<year(current_timestamp)
   and m.rn=1
 order by u.namelastforalpha
;
#5

Much better.. what am i thinking?

#6

Thank you anyway!

#7

Thank you so much! I did not know about either of these capabilities " (row_number() over(partition by" so this was very helpful. I had researched msdn and other places before posting and never saw anything about this. I just tried it on my data and it worked perfectly.