|
SELECT
-- Prefer the DegreeStudent key found in the aid files
ISNULL(mfp.MD_COLLPERS_SURROGATEKEY, mdd.MD_COLLPERS_SURROGATEKEY) AS MD_COLLPERS_SURROGATEKEY,
CASE
WHEN mdp.MD_PROSP_SURROGATEKEY IS NULL
THEN (SELECT MD_PROSP_SURROGATEKEY FROM MD_PROSPECT WHERE MD_PROSP_RECRUITMENTPLUSPKID = 0)
ELSE mdp.MD_PROSP_SURROGATEKEY
END AS MD_PROSP_SURROGATEKEY,
CASE
WHEN mfp.MD_COLLPERS_SURROGATEKEYFAIDPERS IS NULL
-- Link to the 'Unknown' aid applicant where a given student did not apply for aid
THEN (SELECT MD_COLLPERS_SURROGATEKEYFAIDPERS FROM MD_FINANCIALAIDPERSON WHERE MD_COLPERS_COLLEAGUEID = '0000000')
ELSE mfp.MD_COLLPERS_SURROGATEKEYFAIDPERS
END AS MD_COLLPERS_SURROGATEKEYFAIDPERS,
mdd.AIDYEAR AS MF_YAIDALL_INSTANCE,
mdd.MD_COLPERS_COLLEAGUEID AS MF_YAIDALL_COLLEAGUEID,
-- We call this 'projected expenses' because the are just a guess for folks who didn't apply for aid
CAST(ISNULL(mfp.MF_YEARAID_CARLETONTOTALEXPENSES, mfy.MF_YEARAID_CARLETONTOTALEXPENSES * (TERMCOUNT / 3)) AS MONEY) AS MF_YAIDALL_PROJECTEDEXPENSES,
ISNULL(MF_YEARAID_INSTNEED, 0) AS MF_YAIDALL_INSTNEED,
-- CARLDOLLARS are the sum of all accepted Carleton-Dollar awards to a given student in a given aid year
ISNULL(CARLETONDOLLARS, 0) AS MF_YAIDALL_CARLDOLLARS
FROM
/* This first query below basically finds everyone who was enrolled at some point during the year.
* Does this for each year, starting when we started accumulating student data in the data warehouse.
* We will take this list then left outer join it with 'need' and Carleton expense data found in the
* Colleague aid files.
*/
(SELECT
MD_COLPERS_COLLEAGUEID,
-- Aid years go by the beginning year (i.e., 2011-12 is aid year 2011, but fiscal 2012)
MD_DATE_FISCALYEAR - 1 AS AIDYEAR,
COUNT(MD_DATE_TERMNAME) AS TERMCOUNT,
MAX(MD_DSTU_ROWSTART) AS MD_DSTU_ROWSTART,
MAX(MD_DSTU_RECRUITMENTPLUSPKID) AS MD_DSTU_RECRUITMENTPLUSPKID,
MAX(MD_COLLPERS_SURROGATEKEY) AS MD_COLLPERS_SURROGATEKEY
FROM (
/* Take list of all students who were FLORXGC at one time during a given year and then
* take the most recent row for all terms for that year (stu x year x term). We'll later
* count the terms, then throw rows for all but the most recent one away.
*/
SELECT
d1.MD_COLPERS_COLLEAGUEID,
dt.MD_DATE_FISCALYEAR,
dt.MD_DATE_TERMNAME,
MAX(d1.MD_DSTU_ROWSTART) AS MD_DSTU_ROWSTART,
MAX(d1.MD_DSTU_RECRUITMENTPLUSPKID) AS MD_DSTU_RECRUITMENTPLUSPKID,
MAX(d1.MD_COLLPERS_SURROGATEKEY) AS MD_COLLPERS_SURROGATEKEY
FROM
MD_DEGREESTUDENT d1
INNER JOIN MD_DATE dt
ON MD_DATE_SQLDATE <= MD_DSTU_ROWEND
AND dt.MD_DATE_SQLDATE >= d1.MD_DSTU_ROWSTART
INNER JOIN (
-- Get per-year list of every student who was FLORXC at one time during the year
SELECT DISTINCT
MD_COLPERS_COLLEAGUEID,
MD_DATE_FISCALYEAR
FROM
MD_DEGREESTUDENT
INNER JOIN MD_DATE
ON MD_DATE_SQLDATE <= MD_DSTU_ROWEND
AND MD_DATE_SQLDATE >= MD_DSTU_ROWSTART
WHERE
-- Don't go out too far into the future; rows are not needed later on
MD_DATE_SQLDATE <= DATEADD(yy, 2, GETDATE())
-- Don't have full year's data in DW for degree students bef. FY 2010 (= aid year 2009)
AND MD_DATE_FISCALYEAR >= 2010
AND SUBSTRING(MD_DATE_TERMNAME, 4, 2) IN ('FA', 'WI', 'SP', 'SU')
AND MD_DSTU_ENROLLMENTSTATUS IN (
'Full-time',
'Gone, not graduated',
'Off-campus',
'On Leave',
'Required Leave Due to OCP',
'Waiver Senior Residency')
) d2
ON d1.MD_COLPERS_COLLEAGUEID = d2.MD_COLPERS_COLLEAGUEID
AND dt.MD_DATE_FISCALYEAR = d2.MD_DATE_FISCALYEAR
WHERE
-- We want to know, for aid purposes, how may terms the student was enrolled
SUBSTRING(dt.MD_DATE_TERMNAME, 4, 2) IN ('FA', 'WI', 'SP', 'SU')
GROUP BY
d1.MD_COLPERS_COLLEAGUEID,
dt.MD_DATE_FISCALYEAR,
MD_DATE_TERMNAME
) t
GROUP BY
MD_COLPERS_COLLEAGUEID,
MD_DATE_FISCALYEAR
) mdd
-- Get most recent prospect key for each student
LEFT OUTER JOIN MD_PROSPECT mdp
ON mdd.MD_DSTU_RECRUITMENTPLUSPKID = mdp.MD_PROSP_RECRUITMENTPLUSPKID
AND mdp.MD_PROSP_ROWISCURRENT = 1
-- Here's where we fetch by-year aid data
LEFT OUTER JOIN (
SELECT
t1.MD_COLLPERS_SURROGATEKEY,
t1.MD_COLLPERS_SURROGATEKEYFAIDPERS,
MF_YEARAID_COLLEAGUEID,
MF_YEARAID_INSTANCE,
MF_YEARAID_CARLETONTOTALEXPENSES,
MF_YEARAID_INSTNEED
FROM
MF_PERYEARAIDFACTS t1
INNER JOIN MD_FINANCIALAIDPERSON t2
ON t1.MD_COLLPERS_SURROGATEKEYFAIDPERS = t2.MD_COLLPERS_SURROGATEKEYFAIDPERS
) mfp
ON mdd.MD_COLPERS_COLLEAGUEID = mfp.MF_YEARAID_COLLEAGUEID
AND mdd.AIDYEAR = mfp.MF_YEARAID_INSTANCE
-- And here's where we fetch information on how much was actually awarded
LEFT OUTER JOIN (
SELECT
MF_AWAM_COLLEAGUEID,
MF_AWAM_INSTANCE,
MAX(MD_COLLPERS_SURROGATEKEYFAIDPERS) AS MD_COLLPERS_SURROGATEKEYFAIDPERS,
SUM(MF_AWAM_AMOUNT) AS CARLETONDOLLARS
FROM
VMF_AWARDAMOUNTCURRENT a1
INNER JOIN VMD_AWARDSTATUS a2
ON a1.MD_AWSTAT_SURROGATEKEY = a2.MD_AWSTAT_SURROGATEKEY
INNER JOIN VMD_AWARD a3
ON a1.MD_AWARD_SURROGATEKEY = a3.MD_AWARD_SURROGATEKEY
WHERE
MD_AWARD_REPORTINGCLASS = 'Carleton Dollars'
AND a2.MD_AWSTAT_IsAccepted = 'Yes'
GROUP BY
MF_AWAM_COLLEAGUEID,
MF_AWAM_INSTANCE
) mda
ON mfp.MF_YEARAID_COLLEAGUEID = mda.MF_AWAM_COLLEAGUEID
AND mfp.MF_YEARAID_INSTANCE = mda.MF_AWAM_INSTANCE
-- This final query helps us fill in missing Carleton expense data above
INNER JOIN (
-- Find most common Carleton expense amount for each year (the mode, that is, at the year grain)
SELECT
MF_YEARAID_INSTANCE,
MAX(MF_YEARAID_CARLETONTOTALEXPENSES) AS MF_YEARAID_CARLETONTOTALEXPENSES
FROM (
SELECT
MF_YEARAID_INSTANCE,
MF_YEARAID_CARLETONTOTALEXPENSES,
-- Ranks rows by how often the CarletonTotalExpenses value occurs that year
RANK() OVER (PARTITION BY MF_YEARAID_INSTANCE ORDER BY COUNT(*) DESC) AS ERank
FROM
MF_PERYEARAIDFACTS
WHERE
MF_YEARAID_CARLETONTOTALEXPENSES > 0
GROUP BY
MF_YEARAID_INSTANCE,
MF_YEARAID_CARLETONTOTALEXPENSES
) rfy
WHERE
-- We only want the most commonly appearing Carleton expense figure for a given year
ERank = 1
GROUP BY
MF_YEARAID_INSTANCE
) mfy
ON mdd.AIDYEAR = mfy.MF_YEARAID_INSTANCE
|