473,325 Members | 2,816 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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 3504
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: matt | last post by:
hello, im working on my first public-facing ASP.NET 2 website, and i have a question/concern about authentication integration. in ASP.NET 1.1, one would typically go w/ a "role yer own"...
9
by: serge | last post by:
/* Subject: How to build a procedure that returns different numbers of columns as a result based on a parameter. You can copy/paste this whole post in SQL Query Analyzer or Management Studio...
1
by: Rusty Hill | last post by:
I am creating a web app to be shared amongst companies. Each user's credentials will be in the common ASP.NET membership database. Each company will have their own data base. I need to be able...
3
by: Glenn | last post by:
My current classic-ASP site has users, projects, roles and the 2.0 membership looks like a perfect fit, but I'm having trouble finding examples of how to have users that belong to different...
5
by: DotNetNewbie | last post by:
Hi, I am developing an application that has to scale and be very efficient, and I am using asp.net membership in my application. I set things up in my Users table (it has extra columns that I...
11
by: unionhorse | last post by:
Hello, We use an access database for the membership administration of thousands of musicians. Membership status varies from "active" to "suspended" to "expelled". Musicians go in and out of...
12
kcdoell
by: kcdoell | last post by:
Hello: I just learned how to put crosstabs queries together but this one in particular is adding a new dimension in which I was hoping someone could give me some direction. I have the following...
1
by: Jeff | last post by:
Hey ASP.NET 2.0 At work my boss have given me the task of developing a new website. Users will be able to register at the website and gain exclusive access to some information etc... Some...
1
by: =?Utf-8?B?Z29sZGVucmF0ZQ==?= | last post by:
Hi everyone, I have a client who wants a solution for the following problem. I'm not sure that its possible but I'll ask anyway. The client has joined godaddy's reseller plan. They've provided...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.