/*
* Offers a class level (Freshman, Sophomore, Junior, Senior) for aid applicants.
* Also, tells us if a freshman is (in SFS's terminology) a 'true freshman,' i.e.,
* someone who has not yet matriculated (i.e., an IPEDS 'returning with first-year
* status' student).
*
* Does this at the grain of one student per aid year (instance).
*/
select
   mdp.MD_ColPers_ColleagueID,
mdp.MD_FAidPers_InstanceName,
   CASE
      -- If we have no graduating cohort year on this person, their class year level is necessarily 'Unknown'
      WHEN mdp.VMD_ClassYearLevel_GraduatingCohortYear IS NULL                           THEN 'Unknown'
      -- Unknown graduating cohort years are recorded in the DW as 9999, so we really shouldn't see the NULL above
      WHEN mdp.VMD_ClassYearLevel_GraduatingCohortYear = 9999                              THEN 'Unknown'
      WHEN (mdp.VMD_ClassYearLevel_GraduatingCohortYear - mdp.MD_FAidPers_InstanceName) <= 0      THEN 'Post-Graduation'
      ELSE
         CASE mdp.VMD_ClassYearLevel_GraduatingCohortYear - mdp.MD_FAidPers_InstanceName
            WHEN 1 THEN 'Senior'
            WHEN 2 THEN 'Junior'
            WHEN 3 THEN 'Sophomore'
            ELSE 'Freshman' -- Kids w/ unknown graduating cohort years end up here, too
         END
   END AS VMD_ClassYLevel_StudentYear,
   -- If student was a freshman this aid year, was he or she a *true* freshman?
   CASE
      WHEN mdp.VMD_ClassYearLevel_GraduatingCohortYear IS NULL                           THEN 'Unknown'
      -- Unknown graduating cohort years are recorded in the DW as 9999, so we really shouldn't see the NULL above
      WHEN mdp.VMD_ClassYearLevel_GraduatingCohortYear = 9999                              THEN 'Unknown'
      WHEN (mdp.VMD_ClassYearLevel_GraduatingCohortYear - mdp.MD_FAidPers_InstanceName) < 4      THEN 'Not Applicable'
      -- If we have partial info on a student, he/she was probably a freshman w/ an incomplete app
      WHEN mdp.VMD_ClassYearLevel_EnteringCohortYear IS NULL                              THEN 'Unknown'
      WHEN mdp.VMD_ClassYearLevel_EnteringCohortYear = 9999                              THEN 'Probably'
      ELSE
         CASE
            -- If aid year (instance) = the entering year, then yes, we have a true freshman
            WHEN mdp.VMD_ClassYearLevel_EnteringCohortYear >= mdp.MD_FAidPers_InstanceName      THEN 'Yes'
            -- ELSE: If aid year (instance) comes after entering year, then we do NOT have a true freshman
            ELSE 'No'
         END
   END AS VMD_ClassYLevel_IsTrueFreshman
FROM
   (SELECT
      mdf.MD_COLPERS_COLLEAGUEID,
      mdf.MD_FAIDPERS_INSTANCENAME,
      ROW_NUMBER() OVER (Partition by mdf.MD_COLPERS_COLLEAGUEID, mdf.MD_FAIDPERS_INSTANCENAME Order by mdf.MD_FAIDPERS_ROWSTART DESC) AS RowNumber,
      ISNULL(mdd.MD_DSTU_ENTERINGCOHORTYEAR, ISNULL(mdf.MD_FAIDPERS_ENTERINGCOHORTYEAR, mdr.MD_PROSP_PROJECTEDENTRYTERMYEAR)) AS VMD_ClassYearLevel_EnteringCohortYear,
      ISNULL(mdd.MD_DSTU_GRADUATINGCOHORTYEAR, mdf.MD_FAIDPERS_GRADUATINGCOHORTYEAR) AS VMD_ClassYearLevel_GraduatingCohortYear
    FROM
      MD_FINANCIALAIDPERSON mdf
      LEFT OUTER JOIN MD_DEGREESTUDENT mdd
      ON mdf.MD_COLPERS_COLLEAGUEID = mdd.MD_COLPERS_COLLEAGUEID
         AND CAST(mdd.MD_COLPERS_COLLEAGUEID AS INT) <> 0
         AND mdf.MD_FAIDPERS_ROWEND >= mdd.MD_DSTU_ROWSTART
         AND mdf.MD_FAIDPERS_ROWEND <= mdd.MD_DSTU_ROWEND
      LEFT OUTER JOIN MD_PROSPECT mdr
      ON mdf.MD_FAIDPERS_RECRUITMENTPLUSPKID = mdr.MD_PROSP_SURROGATEKEY
         AND CAST(mdr.MD_PROSP_RECRUITMENTPLUSPKID AS INT) <> 0
         AND mdf.MD_FAIDPERS_ROWEND >= mdr.MD_PROSP_ROWSTART
         AND mdf.MD_FAIDPERS_ROWEND <= mdr.MD_PROSP_ROWEND
   ) mdp
WHERE
   mdp.RowNumber = 1 OR mdp.RowNumber IS NULL