Student Cohort Tracking

[NomadicJosh]

I have a small database snippet over at SQLFiddle: http://sqlfiddle.com/#!2/9ff722/55

 

I need help with writing a query that will allow me to track students in a certain cohort from beginning to graduation. I have two tables: application and stu_acad_cred. The application has the start term and the stu_acad_cred are course registrations. If I want to track students in a fall 2012 (12/FA) cohort, then the query must join the two tables; if the student has a start term of 12/FA *and* has registered for 12/FA courses, the student should be counted. I then want to track those student from year to year or term to term to see if the number of students that started in the 12/FA cohort actually decrease overtime. So, the results should return something similar to below.

| # Students (12/FA Cohort) | Year  |

-------------------------------------

|  2                        |  2012 |

|  2                        |  2013 |

|  2                        |  2014 |

I am not sure if this can be done straight forward or with a stored procedure but any help or direction is greatly appreciated.

[Barand]

 So, the results should return something similar to below.

| # Students (12/FA Cohort) | Year  |

-------------------------------------

|  2                        |  2012 |

|  2                        |  2013 |

|  2                        |  2014 |

 

Are those the actual results that you expect from the SQLFiddle data?

[NomadicJosh]

Are those the actual results that you expect from the SQLFiddle data?

 

Sorry, that probably was confusing. I was using that as an example of how I want the data to return. Using SQLFiddle to return real data, the result should look like this:

| # Students (12/FA Cohort) | Year  |

-------------------------------------

|  1                        |  2012 |

|  1                        |  2013 |

|  0                        |  2014 |

There is only one student (student #12) in the 12/FA cohort that has registrations for 2012 and 2013. The student does not have registrations for 2014 so it should return 0 or NULL.

[Barand]

Is this what you are looking for?

SELECT 
    YEAR(addDate) as yr
  , COUNT(DISTINCT a.personID) as tot
FROM stu_acad_cred s
LEFT JOIN application a
    ON s.stuID = a.personID AND a.startTerm = '12/FA'
GROUP BY yr;

[NomadicJosh]

 

Is this what you are looking for?

SELECT 
    YEAR(s.addDate) as yr
  , COUNT(DISTINCT a.personID) as tot
FROM stu_acad_cred s
LEFT JOIN application a
    ON s.stuID = a.personID AND a.startTerm = '12/FA'
GROUP BY yr;

 

Thank you very much @Barand. I can definitely work with that.