April 7, 2009 at 2:27 pm
Hello:
I'm trying to get a count of commuter students by zip code. I have a matrix in which the column group, ColumnGroup_Campus, is grouped on =Fields!CampusName.Value; the row group, RowGroup_Zip, is grouped on =Fields!Zip.Value; and the Detail is =CountDistinct(Fields!StudentID.Value). Here's a pared-down version of the dataset query:
SELECT Campus.CampusName, Student.StudentID, StudentAddress.Zip, StudentAddress.AddressType
FROM Campus INNER JOIN Student
ON Campus.CampusID = Student.CampusID
INNER JOIN StudentAddress
ON Student.StudentID = StudentAddress.StudentID
WHERE Student.StudentType = 'Commuter'
The problem is that in the StudentAddress table, most students have a home address (designated by an AddressType of "H"), but if they're taking classes through the company for which they work, they may have only a company address (AddressType "C"). Some students have both.
I need to make sure that I'm only counting one zip code per student. I only want to count the company address if a student doesn't have a home address. Does anyone know how to do this?
Thanks in advance!
April 7, 2009 at 2:43 pm
1. What version of SQL Server are you using?
2. Please provide us with the DDL for the tables involved in the query. You can
reduce the columns to just those needed for the query and the primary key(s).
3. Some sample data for the tables. Please provide it as a series of insert statements
the we can cut, paste, and execute in SSMS to load your tables.
4. Expected results based on the sample data.
5. What you have done so far code wise.
Any questions, please read the first article i reference below in my signature block regarding asking for assistance.
April 7, 2009 at 2:52 pm
Try changing the query to something like this:
SELECT
Campus.CampusName,
Student.StudentID,
ISNULL(home.zip, company.zip) AS Zip
FROM
Campus
INNER JOIN Student
ON Campus.CampusID = Student.CampusID
LEFT OUTER JOIN (SELECT
studentID,
Zip
FROM
dbo.StudentAddress
WHERE
addresstype = 'h') Home
ON student.studentid = home.studentid
LEFT OUTER JOIN (SELECT
studentID,
Zip
FROM
dbo.StudentAddress
WHERE
addresstype = 'c') Company
ON student.studentid = company.studentid
WHERE
Student.StudentType = 'Commuter';
See if that helps.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 3:05 pm
If you are using SQL Server 2005, something like this may also work:
WITH StudentAddr (
RowNum,
StudentID,
Zip,
AddressType
) as (
select
row_number() over (partition by StudentID order by AddressType desc),
StudentID,
Zip,
AddressType
from
StudentAddress
)
SELECT
Campus.CampusName,
Student.StudentID,
StudentAddr.Zip,
StudentAddr.AddressType
FROM
Campus
INNER JOIN Student
ON Campus.CampusID = Student.CampusID
INNER JOIN StudentAddr
ON (Student.StudentID = StudentAddr.StudentID
and StudentAddr.RowNum = 1)
WHERE
Student.StudentType = 'Commuter'
This is untested, as I didn't wait for anything I had requested.
April 8, 2009 at 9:14 am
Thanks to both of you for your replies! This database is actually stored in some weird proprietary platform and the data is imported into SQL Server every week through an SSIS package. I actually found a PreferredAddress field that was not being pulled over with the Student table that solved my problem. Sorry to waste your time--thanks again!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply