January 29, 2008 at 10:21 am
I am writing a query to pull gpa data for our students. We use a transcript sequence of 001 for most students data. BUT there are students who have a trans seq of 002 which we should use IF they have it. Is it possible to incorporate this in my code? If so, where/how?
January 29, 2008 at 7:14 pm
I am assuming several things here can you use MAX
DECLARE @myTable TABLE
(
Student CHAR(5),
[sequence] VARCHAR(3),
GPA NUMERIC(5, 2)
)
INSERT @myTable
SELECT 'ST1', '001', 45 UNION
SELECT 'ST2', '001', 99UNION
SELECT 'ST2', '002', 23UNION
SELECT 'ST3', '002', 39
SELECTA.Student,
A.GPA
FROM
@myTable A
JOIN
(
SELECTStudent, MAX([sequence]) [sequence]
FROM
@myTable
GROUP BY
Student
) B
ON
A.Student = B.Student
ANDA.[sequence] = B.[sequence]
Regards,
gova
January 29, 2008 at 7:26 pm
Another way
DECLARE @myTable TABLE
(
Student CHAR(5),
[sequence] VARCHAR(3),
GPA NUMERIC(5, 2)
)
INSERT @myTable
SELECT 'ST1', '001', 45 UNION
SELECT 'ST2', '001', 99UNION
SELECT 'ST2', '002', 23UNION
SELECT 'ST3', '002', 39
SELECTCOALESCE(A.Student, B.Student) Student,
COALESCE(A.GPA, B.GPA) GPA
FROM
(
SELECT Student,GPA FROM @MyTable WHERE [sequence] = '002'
) A
FULL OUTER JOIN
(
SELECT Student,GPA FROM @MyTable WHERE [sequence] = '001'
) B
ON
A.Student = B.Student
Regards,
gova
January 29, 2008 at 8:19 pm
So the key is when joining the table containing the transcript seq to use the max clause? I.e. this will pull trans 2 IF avaialable, if not trans 1?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply