Tuesday, 10 July 2012

SQL Query: Join Query from same table with different where condition 2

Scenario: In college, a student has taken 3 years to complete a course and since each year student has to enroll. Student_id, Enrolled_on_date and Drop_out date field are kept in the same table. So how to find student's starting and ending year

Solution:
SELECT S.STYR_Student_ID, S.StartDate, E.EndDate, S.FirstYear, E.LastYear

From

(SELECT                STYR_Student_ID, MIN(STYR_Enrolled_On) AS StartDate, MIN(STYR_Year) AS FirstYear
FROM                   NG.dbo.STYRstudentYR
GROUP BY           STYR_Student_ID) AS S

INNER JOIN

(SELECT                STYR_Student_ID, MAX(STYR_Drop_Out_Date) AS EndDate, MAX(STYR_Year) AS LastYear
FROM                   NG.dbo.STYRstudentYR
GROUP BY           STYR_Student_ID) AS E

ON S.STYR_Student_ID = E.STYR_Student_ID

ORDER BY S.STYR_Student_ID

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home