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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s