Return most recent membership based on year stored in joined table

[Ken Post]

Hello-

I'm fairly new to writing SQL statements and would greatly appreciate
some help on this one.

I'm working on a project for a non-profit that I volunteer for. Part
of the database tracks membership using tables like this:

PersonInfo
-------------------
PersonID (primary key)
FirstName
LastName
etc..
PeopleMembershi ps
-------------------
PPLMembershipIP (primary key)
PersonID
MembershipTypeI D
FeePaid

MembershipTypes
--------------------
MembershipTypeI D (primary key)
MembershipYear
StandardFee
MembershipDescr iption (varchar)
Just because a person is in PersonInfo, doesn't mean they have anything
in PeopleMembershi ps (they can be in the databse for other reasons and
not have or have ever had a membership).

Membership fees vary by year and type of membership and they want to
retain a history of a person's memberships.

What I'm looking to do here is write a query (a view in SQL Server)
that will return the following Info

PersonID, MostRecentMembe rshipYear, FeePaidForThatM embership,
DescriptionOfTh atMembership

I'm thinking that I'd use max(MembershipY ear), but that requires group
by for the other columns, so I'm getting all of the people's
memberships returned.

I'm pretty sure this can be best done with a subquery, but I'm not sure
how.

Can someone please point me in the right direction or provide a sample
that I can learn from?

Kindly,
Ken

[Ryan]

Try the following (untested)...

SELECT
PI.PersonID,
SUM(PM.FeePaid) AS FeePaidForThatM embership,
MAX(MT.Membersh ipYear) AS MostRecentMembe rshipYear,
MT.MembershipDe scription AS DescriptionOfTh atMembership

FROM
PersonInfo PI
LEFT JOIN PeopleMembershi ps PM
ON PI.PersonId = PM.PersonId
LEFT JOIN MembershipTypes MT
ON PM.MembershipTy peId = MT.MembershipTy peId

GROUP BY
PI.PersonId,
MT.MembershipDe scription

I'm not sure if you wanted the total of the fee paid, but I've done
this for you as it would make more sense (to me at least).

Hope this helps

Ryan

Ken Post wrote:

Hello-

I'm fairly new to writing SQL statements and would greatly appreciate
some help on this one.

I'm working on a project for a non-profit that I volunteer for. Part
of the database tracks membership using tables like this:

PersonInfo
-------------------
PersonID (primary key)
FirstName
LastName
etc..
PeopleMembershi ps
-------------------
PPLMembershipIP (primary key)
PersonID
MembershipTypeI D
FeePaid

MembershipTypes
--------------------
MembershipTypeI D (primary key)
MembershipYear
StandardFee
MembershipDescr iption (varchar)
Just because a person is in PersonInfo, doesn't mean they have anything in PeopleMembershi ps (they can be in the databse for other reasons and not have or have ever had a membership).

Membership fees vary by year and type of membership and they want to
retain a history of a person's memberships.

What I'm looking to do here is write a query (a view in SQL Server)
that will return the following Info

PersonID, MostRecentMembe rshipYear, FeePaidForThatM embership,
DescriptionOfTh atMembership

I'm thinking that I'd use max(MembershipY ear), but that requires group by for the other columns, so I'm getting all of the people's
memberships returned.

I'm pretty sure this can be best done with a subquery, but I'm not sure how.

Can someone please point me in the right direction or provide a sample that I can learn from?

Kindly,
Ken

[Erland Sommarskog]

Ken Post (nn*******@gmai l.com) writes:

Just because a person is in PersonInfo, doesn't mean they have anything
in PeopleMembershi ps (they can be in the databse for other reasons and
not have or have ever had a membership).

Membership fees vary by year and type of membership and they want to
retain a history of a person's memberships.

What I'm looking to do here is write a query (a view in SQL Server)
that will return the following Info

PersonID, MostRecentMembe rshipYear, FeePaidForThatM embership,
DescriptionOfTh atMembership

I'm thinking that I'd use max(MembershipY ear), but that requires group
by for the other columns, so I'm getting all of the people's
memberships returned.

It is possible that Ryan's query answers your question, but it case it
didn't, I suggest that you follow the standard advice for this type of
question, and that is to include:

o CREATE TABLE command for your tables.
o INSERT statements with sample data.
o The desired output give the sample data.

One reason for this is that gives you good chances to get a tested
solution. Another reason is that the sample data and the desired output
can help to clarify ambiguities in the narrative. There are several
things in your post that I am not sure how to interpret. And since
I don't like to write queries bases on guesses, I refrain for now.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Andy O'Neill]

"Ken Post" <nn*******@gmai l.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

Hello-

I'm fairly new to writing SQL statements and would greatly appreciate
some help on this one.

I'm working on a project for a non-profit that I volunteer for. Part
of the database tracks membership using tables like this:

PersonInfo
-------------------
PersonID (primary key)
FirstName
LastName
etc..
PeopleMembershi ps
-------------------
PPLMembershipIP (primary key)
PersonID
MembershipTypeI D
FeePaid

MembershipTypes
--------------------
MembershipTypeI D (primary key)
MembershipYear
StandardFee
MembershipDescr iption (varchar)
Just because a person is in PersonInfo, doesn't mean they have anything
in PeopleMembershi ps (they can be in the databse for other reasons and
not have or have ever had a membership).

Membership fees vary by year and type of membership and they want to
retain a history of a person's memberships.

What I'm looking to do here is write a query (a view in SQL Server)
that will return the following Info

PersonID, MostRecentMembe rshipYear, FeePaidForThatM embership,
DescriptionOfTh atMembership

I'm thinking that I'd use max(MembershipY ear), but that requires group
by for the other columns, so I'm getting all of the people's
memberships returned.

I'm pretty sure this can be best done with a subquery, but I'm not sure
how.

Can someone please point me in the right direction or provide a sample
that I can learn from?

Kindly,
Ken

I'm somewhat curious as to why the last year someone was a member is that
significant.
Would have thought it's more significant if they paid this year.
Anyhow.

My guess is you'd need....

A subquery returns people and the max year they was a member.
Then in or join this to a main query getting the other data you want.
It's saturday and I don't really feel like getting my head round the actual
sql at the moment when I'm just guessing what you want...
Look into subqueries.

I also think it'd be easier if you added a column "LastMembership " or
something to PeopleMembershi ps and updated this with a trigger or whatever
when someone pays their fee.
I would guess that you might be able to have a zero in FeePaid and so that
row doesn't count as being a member.
Anyhow, that way the view'd be easy.

--
Regards,
Andy O'Neill

[Ken Post]

Thanks all for getting back to me!

First, that's an excellent idea about the trigger setting
LastMembership in the people table. That'll make quick work of this
query.

We need to know the current membership status AND the last time they
were a member (if someone was a member in 1999 only, we're not going to
send a renewal letter).

I wound up using a subquery. Suggestions for improvement are QUITE
welcome! Here's what I did, partly based on the info that I got from
you who responded, part from reading some more posts:

SELECT * FROM (
(SELECT PersonInfo.Pers onID, MembershipTypes .MembershipDesc r,
MembershipTypes .MembershipYear , PeopleMembershi ps.FeePaid
FROM PersonInfo LEFT OUTER JOIN
PeopleMembershi ps ON PersonInfo.Pers onID =
PeopleMembershi ps.PersonID LEFT OUTER JOIN
MembershipTypes ON PeopleMembershi ps.MembershipTy peID =
MembershipTypes .MembershipType ID
GROUP BY PersonInfo.Pers onID, MembershipTypes .MembershipDesc r,
MembershipTypes .MembershipYear , PeopleMembershi ps.FeePaid) AS AllInfo
WHERE (MembershipYear = (SELECT MAX(MembershipT ypes.Membership Year) AS
Expr1
FROM MembershipTypes INNER
JOIN

PeopleMembershi ps ON MembershipTypes .MembershipType ID =
PeopleMembershi ps.MembershipTy peID
WHERE PersonID =
AllInfo.PersonI D)) OR
MembershipYear IS NULL)
Thank you all for your help on this one!