https://web.alsde.edu/PublicDataReports/Default.aspx
Download Excel File: Data Center à Graduation/Dropout Reports à All Students Rate and Group Breakdown
The downloaded file is Read Only. Therefore, you need to copy the data and paste it to a new Workbook named: AL SE 2015 Grad Dropout.xlsx.
Insert 2 columns to the left of Column A. Named these columns ImpMunID and ImpSadID, Column C: ImpSadYear, Column D; ImpMunDOECode, Column E: ImpScbCode, Column F: SystemName, Column G: SchoolName, Column H: SubPopulation, Column I: ImpSadGraduation, Column K: TotalCount, Column Q: DropoutCount.
Import into MySQL table: aa_AL_Grad_Dropout.
Updated the ImpMunID: UPDATE aa_al_grad_dropout JOIN m_municipalities ON MunDOECode = ImpMunDOECode SET ImpMunID = MunID WHERE MunState = 'AL' AND MunModID = 2 AND MunType = 'District'
Updated the State of Alabama records: UPDATE aa_al_grad_dropout SET ImpMunID = 30569 WHERE ImpMunID IS NULL AND `System Name` = 'State of Alabama';
Check for missing records: SELECT * FROM aa_al_grad_dropout WHERE ImpMunID IS NULL;
Update the SadID: UPDATE aa_al_grad_dropout JOIN m_schoolannualdata ON ImpMunID = SadScdID AND SadYear = ImpSadYear SET ImpSadID = SadID;
Check for missing records: SELECT * FROM aa_al_grad_dropout WHERE ImpSadID IS NULL;
Updated the MySQL database: UPDATE m_schoolannualdata JOIN (SELECT ImpSadID, ROUND((SadGraduation * 100),1) AS GraduationRate, CASE WHEN DropoutCount LIKE '*' THEN NULL ELSE ROUND((DropoutCount / TotalCount) * 100,1) END AS DropoutRate FROM aa_al_grad_dropout) AS Rates ON SadID = Rates.ImpSadID SET SadGraduation = Rates.GraduationRate, SadDropout = Rates.DropoutRate;
NEED TO TEST THIS ONE: UPDATE m_schoolannualdata JOIN (SELECT ImpSadID, ROUND((aa_al_grad_dropout.SadGraduation * 100),1) AS GraduationRate, CASE WHEN DropoutCount LIKE '*' THEN NULL ELSE ROUND((DropoutCount / m_schoolannualdata.SadEnrollment) * 100,1) END AS DropoutRate FROM aa_al_grad_dropout JOIN m_schoolannualdata ON SadID = ImpSadID) AS Rates ON SadID = Rates.ImpSadID JOIN m_schoolannualdata ON SadID = ImpSadID SET SadGraduation = Rates.GraduationRate, SadDropout = Rates.DropoutRate WHERE SubPopulation = 'All Students' AND SystemName = SchoolName