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
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