SQL_01_CHART_CHAMP_DATA_LOAD_BLEND

/*
 * ========================================================================
 * File Name:     SQL_01_CHART_CHAMP_DATA_LOAD_BLEND
 * Description:   This file has the code to ETL CHARTCHAMP dataset.
 * Author(s):     Shatrunjai Singh
 * Version History:
 * V1:            08/05/2016. THE FIRST VERSION. FINALLY GOT DATA LOADED
 *                            ONTO THE SERVER
 * V2:            08/15/2016. Cleaned up Version
 * V3:		  08/19/2016. Version with Averages ready for export to R
 *
 * ========================================================================
 */

/*
 * LOAD DATA FROM DIFFERENT EXCEL WORKBOOKS USING IMPORT WIZARD
 * COMBINE DATA FROM DIFFERENT DATASETS AND TAKE AVERAGES OF EACH DISTRICT
 */

SELECT
	AVG(CAST(A.[HOUSE PRICE RANK]AS BIGINT)) AS [HOUSE PRICE RANK],
	AVG(CAST(A.[SAT RANK]AS BIGINT)) AS [SAT RANK],
	AVG(CAST(A.[SAT_MINUS_HOUSEPRICE]AS BIGINT)) AS [SAT_MINUS_HOUSEPRICE],
	AVG(CAST(A.[FINAL_RANK]AS BIGINT)) AS [FINAL_RANK],
	AVG(CAST(B.[STUDENTS]AS BIGINT)) AS [STUDENTS],
	AVG(CAST(B.[STUDENTS DISCIPLINED]AS BIGINT)) AS [STUDENTS DISCIPLINED],
	AVG(CAST(B.[% IN-SCHOOL SUSPENSION]AS BIGINT)) AS [% IN-SCHOOL SUSPENSION],
	AVG(CAST(B.[% OUT-OF-SCHOOL SUSPENSION]AS BIGINT)) AS [% OUT-OF-SCHOOL SUSPENSION],
	AVG(CAST(B.[% EXPULSION]AS BIGINT)) AS [% EXPULSION],
	AVG(CAST(B.[% REMOVED TO ALTERNATE SETTING]AS BIGINT)) AS [% REMOVED TO ALTERNATE SETTING],
	AVG(CAST(B.[% EMERGENCY REMOVAL]AS BIGINT)) AS [% EMERGENCY REMOVAL],
	AVG(CAST(C.[# OF EDUCATORS TO BE EVALUATED]AS BIGINT)) AS [# OF EDUCATORS TO BE EVALUATED],
	AVG(CAST(C.[# EVALUATED]AS BIGINT)) AS [# EVALUATED],
	AVG(CAST(C.[% EVALUATED]AS BIGINT)) AS [% EVALUATED],
	AVG(CAST(C.[% EXEMPLARY]AS BIGINT)) AS [% EXEMPLARY],
	AVG(CAST(C.[% PROFICIENT]AS BIGINT)) AS [% PROFICIENT],
	AVG(CAST(C.[% NEEDS IMPROVEMENT]AS BIGINT)) AS [% NEEDS IMPROVEMENT],
	AVG(CAST(C.[% UNSATISFACTORY]AS BIGINT)) AS [% UNSATISFACTORY],
	AVG(CAST(E.[4 YEAR PRIVATE COLLEGE]AS BIGINT)) AS [4 YEAR PRIVATE COLLEGE],
	AVG(CAST(E.[4 YEAR PUBLIC COLLEGE]AS BIGINT)) AS [4 YEAR PUBLIC COLLEGE],
	AVG(CAST(E.[2 YEAR PRIVATE COLLEGE]AS BIGINT)) AS [2 YEAR PRIVATE COLLEGE],
	AVG(CAST(E.[2 YEAR PUBLIC COLLEGE]AS BIGINT)) AS [2 YEAR PUBLIC COLLEGE],
	AVG(CAST(E.[OTHER POST SECONDARY]AS BIGINT)) AS [OTHER POST SECONDARY],
	AVG(CAST(E.[WORK]AS BIGINT)) AS [WORK],
	AVG(CAST(E.[MILITARY]AS BIGINT)) AS [MILITARY],
	AVG(CAST(E.[OTHER]AS BIGINT)) AS [OTHER],
	AVG(CAST(E.[UNKNOWN]AS BIGINT)) AS [UNKNOWN],
	AVG(CAST(F.[TOTAL COUNT]AS BIGINT)) AS [TOTAL COUNT],
	AVG(CAST(F.[TOTAL FTE MEMB]AS BIGINT)) AS [TOTAL FTE MEMB],
	AVG(CAST(F.[GENERAL FUND APPROPRIATIONS]AS BIGINT)) AS [GENERAL FUND APPROPRIATIONS],
	AVG(CAST(F.[GRANTS; REVOLVING & OTHER FUNDS]AS BIGINT)) AS [GRANTS; REVOLVING & OTHER FUNDS],
	AVG(CAST(F.[TOTAL EXPENDITURES]AS BIGINT)) AS [TOTAL EXPENDITURES],
	AVG(CAST(F.[EXPENDITURE PER PUPIL]AS BIGINT)) AS [EXPENDITURE PER PUPIL],
	AVG(CAST(H.[TOTAL # OF TEACHERS]AS BIGINT)) AS [TOTAL # OF TEACHERS],
	AVG(CAST(H.[% OF TEACHERS LICENSED IN TEACHING ASSIGNMENT]AS BIGINT)) AS [% OF TEACHERS LICENSED IN TEACHING ASSIGNMENT],
	AVG(CAST(H.[TOTAL # OF CLASSES IN CORE ACADEMIC AREAS]AS BIGINT)) AS [TOTAL # OF CLASSES IN CORE ACADEMIC AREAS],
	AVG(CAST(H.[% OF CORE ACADEMIC CLASSES TAUGHT BY TEACHERS WHO ARE HIGHLY QUALIFIED]AS BIGINT)) AS [% OF CORE ACADEMIC CLASSES TAUGHT BY TEACHERS WHO ARE HIGHLY QUALIFIED],
	AVG(CAST(H.[STUDENT/TEACHER RATIO]AS BIGINT)) AS [STUDENT/TEACHER RATIO],
	AVG(CAST(I.[AVERAGE SALARY]AS BIGINT)) AS [AVERAGE SALARY],
	AVG(CAST(I.[FTE COUNT]AS BIGINT)) AS [FTE COUNT],
	AVG(CAST(J.[LONGITUDE]AS BIGINT)) AS [LONGITUDE],
	AVG(CAST(J.[DISTNACE_FROM_HARVARD]AS BIGINT)) AS [DISTNACE_FROM_HARVARD],
	AVG(CAST(J.[HOUSE_PRICE]AS BIGINT)) AS [HOUSE_PRICE],
	AVG(CAST(J.[TESTS TAKEN] AS BIGINT)) AS [TESTS TAKEN],
	AVG(CAST(J.[READING] AS BIGINT)) AS [READING],
	AVG(CAST(J.[WRITING] AS BIGINT)) AS [WRITING],
	AVG(CAST(J.[MATH] AS BIGINT)) AS [MATH],
	AVG(CAST(J.[TOTAL_SAT] AS BIGINT)) AS [TOTAL_SAT],
	AVG(CAST(J.[AVG_SAT] AS BIGINT)) AS [AVG_SAT],
	AVG(CAST (J.[PRICE] AS BIGINT)) AS [PRICE],
	AVG(CAST(K.[RETENTION #] AS BIGINT)) AS [RETENTION #],
	AVG(CAST(K.[RETENTION RATE] AS BIGINT)) AS [RETENTION RATE],
	AVG(CAST(K.[ATTENDANCE RATE] AS BIGINT)) AS [ATTENDANCE RATE],
	AVG(CAST(K.[AVERAGE # OF ABSENCES] AS BIGINT)) AS [AVERAGE # OF ABSENCES],
	AVG(CAST(K.[ABSENT 10 OR MORE DAYS] AS BIGINT)) AS [ABSENT 10 OR MORE DAYS],
	AVG(CAST(K.[CHRONICALLY ABSENT (10% OR MORE)] AS BIGINT)) AS [CHRONICALLY ABSENT (10% OR MORE)],
	AVG(CAST(K.[UNEXCUSED > 9 DAYS] AS BIGINT)) AS [UNEXCUSED > 9 DAYS],
	AVG(CAST(I.[AVERAGE SALARY] AS BIGINT)) AS [[AVERAGE SALARY]
INTO
	DBO.CHART_SUMMARY
FROM
								DBO.[CHART_SANKEY] A

                LEFT OUTER JOIN
                DBO.[CHART_DISCIPLINE BY DISTRICT] B
                ON
                A.[DISTRICT ORG CODE (JOIN)]=B.[DISTRICT ORG CODE (JOIN)] AND A.[DISTRICT]=B.[DISTRICT]

                LEFT OUTER JOIN
                DBO.[CHART_EDUCATOR_PERF_BY_DISTRICT] C
                ON
                A.[DISTRICT ORG CODE (JOIN)]=C.[DISTRICT ORG CODE (JOIN)]

               LEFT OUTER JOIN
                DBO.[CHART_GRAD_RATE_BY_DISTRICT] D
                ON
                A.[DISTRICT ORG CODE (JOIN)]=D.[DISTRICT ORG CODE (JOIN)] AND A.[DISTRICT]=D.[DISTRICT]

                LEFT OUTER JOIN
                DBO.[CHART_HIGH_SCHOOL_GRAD_PLANS_BY_DIST] E
                ON
                A.[DISTRICT ORG CODE (JOIN)]=E.[DISTRICT ORG CODE (JOIN)] AND A.[DISTRICT]=E.[DISTRICT]

                LEFT OUTER JOIN
                DBO.[CHART_PUPIL_EXPENDITURE_BY_DISTRICT] F
                ON
                A.[DISTRICT ORG CODE (JOIN)]=F.[DISTRICT ORG CODE (JOIN)] AND A.[DISTRICT]=F.[DISTRICT]

               LEFT OUTER JOIN
                DBO.[CHART_SATS_BY_DISTRICT] G
                ON
                A.[DISTRICT ORG CODE (JOIN)]=G.[DISTRICT ORG CODE (JOIN)] AND A.[DISTRICT]=G.[DISTRICT]

                LEFT OUTER JOIN
                DBO.[CHART_STUDENT_TEACHER_RATIO_BY_DISTRI] H
                ON
                A.[DISTRICT ORG CODE (JOIN)]=H.[DISTRICT ORG CODE (JOIN)] AND A.[DISTRICT]=H.[DISTRICT]

                LEFT OUTER JOIN
                DBO.[CHART_TEACHER_SALARY_BY_DISTRICT] I
                ON
                A.[DISTRICT ORG CODE (JOIN)]=I.[DISTRICT ORG CODE (JOIN)] AND A.[DISTRICT]=I.[DISTRICT]

                LEFT OUTER JOIN
                DBO.[CHART_SCHOOL_DIRECTORY] J
                ON
                A.[DISTRICT ORG CODE (JOIN)]=J.[DISTRICT ORG CODE (JOIN)]

                LEFT OUTER JOIN
                DBO.[CHART_ATTENDANCE_&_RETENTION_BY_DIST] K
                ON
                A.[DISTRICT ORG CODE (JOIN)]=K.[DISTRICT ORG CODE (JOIN)]

GROUP BY
	A.[DISTRICT]



/*
 * END
 */