May 16, 2005 at 4:47 pm
Hi. I'm new so be gentle.
I have the following tables (Simplified for ease):
People:
PeopleID int
FirstName nvarchar(50)
LastName nvarchar(50)
BirthDate date
InternationalStudents:
InterantionalStudentsID int
PeopleID int
ArrivalDate date
HomeCountry int
PeerProgramStudents:
PeerProgramStudentsID int
PeopleID int
ActivityLevel int
PeerProgramVolunteerID int
PeerProgramVolunteers:
PeerProgramVolunteerID int
PeopleID int
StartDate date
EndDate date
MaxPeers int
ActivityLevel int
Approved bit
Interests:
InterestID int
PeopleID int
InterestListID int
InterestList:
InterestListID int
InterestName nvarchar(50)
CountryList:
CountryListID int
CountryName nvarchar(50)
Phew! ok that took too long to write...but I know how useful detail is. So...Here's my problem: I need to produce a matching system for peer program students.
I needed to select all volunteers where approved = true and Student.ArrivalDate between Volunteer.StartDate and Volunteer.EndDate and Volunteer.BirthDate within two years of Student.BirthDate and Volunteer.ActivityLevel within 1 of Student.ActivityLevel AND Volunteer.StudentCount < Volunteer.MaxPeers.
That part has been done and works. I split it into two queries to make it easier to read in my access project:
CurrentPeerStudents:
SELECT dbo.People.PeopleID, dbo.People.FirstName, dbo.People.LastName, dbo.People.BirthDate, dbo.PeerProgramStudents.ActivityLevel, dbo.InternationalStudents.ArrivalDate, dbo.InternationalStudents.HomeCountry, dbo.PeerProgramStudents.PeerProgramVolunteerID
FROM dbo.People INNER JOIN dbo.PeerProgramStudents ON dbo.People.PeopleID = dbo.PeerProgramStudents.PeopleID INNER JOIN dbo.InternationalStudents ON dbo.People.PeopleID = dbo.InternationalStudents.PeopleID
WHERE (dbo.PeerProgramStudents.PeerProgramVolunteerID IS NULL)
CurrentPeerVolunteers:
SELECT dbo.PeerProgramVolunteers.PeerProgramVolunteerID, dbo.People.PeopleID, dbo.People.FirstName, dbo.People.LastName, dbo.People.BirthDate, dbo.PeerProgramVolunteers.StartDate, dbo.PeerProgramVolunteers.EndDate, dbo.PeerProgramVolunteers.MaxPeers, COUNT(dbo.PeerProgramStudents.PeerProgramStudentID) AS StudentCount, dbo.PeerProgramVolunteers.ActivityLevel, dbo.PeerProgramVolunteers.Approved
FROM dbo.PeerProgramStudents RIGHT OUTER JOIN dbo.People INNER JOIN dbo.PeerProgramVolunteers ON dbo.People.PeopleID = dbo.PeerProgramVolunteers.PeopleID ON dbo.PeerProgramStudents.PeerProgramVolunteerID = dbo.PeerProgramVolunteers.PeerProgramVolunteerID GROUP BY dbo.People.FirstName, dbo.People.LastName, dbo.People.BirthDate, dbo.PeerProgramVolunteers.StartDate, dbo.PeerProgramVolunteers.EndDate, dbo.PeerProgramVolunteers.MaxPeers, dbo.PeerProgramVolunteers.ActivityLevel, dbo.PeerProgramVolunteers.Approved, dbo.People.PeopleID, dbo.PeerProgramVolunteers.PeerProgramVolunteerID
HAVING (dbo.PeerProgramVolunteers.Approved = 1) AND (COUNT(dbo.PeerProgramStudents.PeerProgramStudentID) < dbo.PeerProgramVolunteers.MaxPeers)
Then I made a third query to compare the two and get my list of matches for each student:
PeerMatch:
SELECT dbo.CurrentPeerStudents.PeopleID, dbo.CurrentPeerStudents.FirstName, dbo.CurrentPeerStudents.LastName, dbo.CurrentPeerVolunteers.PeopleID AS VPeopleID, dbo.CurrentPeerVolunteers.FirstName AS VFirstName, dbo.CurrentPeerVolunteers.LastName AS VLastName, COUNT(*) AS CommonInterests
FROM dbo.CurrentPeerStudents INNER JOIN dbo.Interests StudentInterests ON dbo.CurrentPeerStudents.PeopleID = StudentInterests.PeopleID INNER JOIN dbo.Interests VolunteerInterests INNER JOIN dbo.CurrentPeerVolunteers ON VolunteerInterests.PeopleID = dbo.CurrentPeerVolunteers.PeopleID ON StudentInterests.InterestListID = VolunteerInterests.InterestListID
WHERE (ABS(DATEDIFF(year, dbo.CurrentPeerStudents.BirthDate, dbo.CurrentPeerVolunteers.BirthDate)) <= 2) AND (ABS(dbo.CurrentPeerStudents.ActivityLevel - dbo.CurrentPeerVolunteers.ActivityLevel) <= 1) AND (dbo.CurrentPeerStudents.ArrivalDate BETWEEN dbo.CurrentPeerVolunteers.StartDate AND dbo.CurrentPeerVolunteers.EndDate)
GROUP BY dbo.CurrentPeerStudents.PeopleID, dbo.CurrentPeerStudents.FirstName, dbo.CurrentPeerStudents.LastName,
dbo.CurrentPeerVolunteers.PeopleID, dbo.CurrentPeerVolunteers.FirstName,
dbo.CurrentPeerVolunteers.LastName
HAVING (COUNT(*) > 0)
Now they are asking me to make the selections culturally diverse so that if a volunteer already has 50% of his maxpeers from CountryA that he won't get matched with any new students from CountryA.
HELP! How do I do this? Sorry for the long post.
B
May 16, 2005 at 5:06 pm
First:
1) Get in the habit of using aliases. This is really anoying trying to wade through unformatted statements.
2) format the statements for better reading
Probably would have been better to have you're country info in the same table (internationalstudents columns in people table)
Anyway,
1) add an outer join to IS table any time you're querying for people info to do your matching. Use isnull to default a country to your home country.
2) when joins start to break down or get tricky, embed them in sub queries:
SELECT mainresultset....
FROM ( SELECT ...
FROM people p
LEFT OUTER JOIN internationalstudents IS ON is.key = p.key
) a
JOIN ( next result set to join
) b ON a.key = b.key
Or do it the old fashioned way and do each subselect into a temp table first then create one simple final select statement that joins these temp tables...
You should be able to figure it out from there.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply