469,106 Members | 2,313 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

Return most recent membership based on year stored in joined table


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..
PeopleMemberships
-------------------
PPLMembershipIP (primary key)
PersonID
MembershipTypeID
FeePaid

MembershipTypes
--------------------
MembershipTypeID (primary key)
MembershipYear
StandardFee
MembershipDescription (varchar)
Just because a person is in PersonInfo, doesn't mean they have anything
in PeopleMemberships (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, MostRecentMembershipYear, FeePaidForThatMembership,
DescriptionOfThatMembership

I'm thinking that I'd use max(MembershipYear), 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

Jul 23 '05 #1
4 1157
Try the following (untested)...

SELECT
PI.PersonID,
SUM(PM.FeePaid) AS FeePaidForThatMembership,
MAX(MT.MembershipYear) AS MostRecentMembershipYear,
MT.MembershipDescription AS DescriptionOfThatMembership

FROM
PersonInfo PI
LEFT JOIN PeopleMemberships PM
ON PI.PersonId = PM.PersonId
LEFT JOIN MembershipTypes MT
ON PM.MembershipTypeId = MT.MembershipTypeId

GROUP BY
PI.PersonId,
MT.MembershipDescription

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..
PeopleMemberships
-------------------
PPLMembershipIP (primary key)
PersonID
MembershipTypeID
FeePaid

MembershipTypes
--------------------
MembershipTypeID (primary key)
MembershipYear
StandardFee
MembershipDescription (varchar)
Just because a person is in PersonInfo, doesn't mean they have anything in PeopleMemberships (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, MostRecentMembershipYear, FeePaidForThatMembership,
DescriptionOfThatMembership

I'm thinking that I'd use max(MembershipYear), 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


Jul 23 '05 #2
Ken Post (nn*******@gmail.com) writes:
Just because a person is in PersonInfo, doesn't mean they have anything
in PeopleMemberships (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, MostRecentMembershipYear, FeePaidForThatMembership,
DescriptionOfThatMembership

I'm thinking that I'd use max(MembershipYear), 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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
"Ken Post" <nn*******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.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..
PeopleMemberships
-------------------
PPLMembershipIP (primary key)
PersonID
MembershipTypeID
FeePaid

MembershipTypes
--------------------
MembershipTypeID (primary key)
MembershipYear
StandardFee
MembershipDescription (varchar)
Just because a person is in PersonInfo, doesn't mean they have anything
in PeopleMemberships (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, MostRecentMembershipYear, FeePaidForThatMembership,
DescriptionOfThatMembership

I'm thinking that I'd use max(MembershipYear), 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 PeopleMemberships 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
Jul 23 '05 #4
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.PersonID, MembershipTypes.MembershipDescr,
MembershipTypes.MembershipYear, PeopleMemberships.FeePaid
FROM PersonInfo LEFT OUTER JOIN
PeopleMemberships ON PersonInfo.PersonID =
PeopleMemberships.PersonID LEFT OUTER JOIN
MembershipTypes ON PeopleMemberships.MembershipTypeID =
MembershipTypes.MembershipTypeID
GROUP BY PersonInfo.PersonID, MembershipTypes.MembershipDescr,
MembershipTypes.MembershipYear, PeopleMemberships.FeePaid) AS AllInfo
WHERE (MembershipYear = (SELECT MAX(MembershipTypes.MembershipYear) AS
Expr1
FROM MembershipTypes INNER
JOIN

PeopleMemberships ON MembershipTypes.MembershipTypeID =
PeopleMemberships.MembershipTypeID
WHERE PersonID =
AllInfo.PersonID)) OR
MembershipYear IS NULL)
Thank you all for your help on this one!

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Rusty Hill | last post: by
1 post views Thread by =?Utf-8?B?Z29sZGVucmF0ZQ==?= | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.