Dict-Physical

View VMF_AdvisementCurrent

Card of view VMF_AdvisementCurrent

Name	VMF_AdvisementCurrent
Comment	See documentation on MF_Advisement in the StarsAcademicRecord package. Links the most current row in VMD_Instructor to all applicable rows in MD_DegreeStudent, i.e., all rows active for students currently being advised by a given instructor. The same instructor may advise the same student in multiple capacities (MD_AdvisementType), so the grain here is per instructor, per student (surrogate key), per advisement type. The underlying SQL has a 'select distinct' in it because sometimes the same instructor is assigned multiple times to the same student, but for different periods that may overlap with the present (probably due to data entry errors).
Dimensional Type	Fact

SQL query of the view VMF_AdvisementCurrent

select distinct
   mdd.MD_CollPers_SurrogateKey AS MD_CollPers_SurrogateKeyDStu
   , vmi.MD_CollPers_SurrogateKey AS MD_CollPers_SurrogateKeyInst
   , utn.TB_NetEmp_NetID AS MF_Advise_InstructorNetID
   , mfa.MF_Advise_CollIDDStu
   , mfa.MD_AdviseType_SurrogateKey
   , mfa.MD_Program_SurrogateKey
   , 1 AS MF_Advise_Count
from
MF_Advisement mfa
INNER JOIN MD_DegreeStudent mdd
ON mfa.MF_Advise_CollIDDStu = mdd.MD_COLPERS_COLLEAGUEID
INNER JOIN UtilityTables.TB_NETIDEMPID utn
ON mfa.MF_Advise_CollIDEmp = utn.TB_NETEMP_COLLEAGUEID
INNER JOIN VMD_INSTRUCTOR vmi
ON mfa.MF_Advise_CollIDEmp = vmi.MD_Colpers_ColleagueID
AND vmi.MD_Emp_RowIsCurrent = 1
where
   MD_Date_EndDate >= (YEAR(GETDATE()) * 10000) + (MONTH(GETDATE()) * 100) + DAY(GETDATE())
   AND MD_Date_StartDate <= (YEAR(GETDATE()) * 10000) + (MONTH(GETDATE()) * 100) + DAY(GETDATE())