ieef-students.sql

SELECT
	MSS_PERSON_ADDRESS.ADR_OID,
	MSS_PERSON_ADDRESS.ADR_CITY,
	MSS_PERSON_ADDRESS.ADR_STATE,
	MSS_PERSON_ADDRESS.ADR_ADDRESS_LINE_01
INTO #LSKYSD202_TEMP_MSS_ADDRESS
FROM
	MSS_PERSON_ADDRESS
WHERE
	NOT (
		MSS_PERSON_ADDRESS.ADR_CITY IS NULL
		AND MSS_PERSON_ADDRESS.ADR_STATE IS NULL
		AND MSS_PERSON_ADDRESS.ADR_ADDRESS_LINE_01 IS NULL
		);

SELECT
	MSS_PERSON.PSN_OID,
	TEMP_ADDRESSES.ADR_CITY as 'City', -- City in which the student resides
	MSS_PERSON.PSN_EMAIL_01 as 'Email',
	MSS_PERSON.PSN_PHONE_01 as 'Phone',
	TEMP_ADDRESSES.ADR_STATE as 'State',
	TEMP_ADDRESSES.ADR_ADDRESS_LINE_01 as 'Street',
	MSS_PERSON.PSN_FIELDC_003 as 'LastName',
	MSS_PERSON.PSN_FIELDC_001 as 'FirstName',
	MSS_PERSON.PSN_DOB as 'DateOfBirth'
INTO #LSKYSD202_Temp_MSS_PERSON
FROM
	MSS_PERSON
	LEFT OUTER JOIN #LSKYSD202_TEMP_MSS_ADDRESS as TEMP_ADDRESSES ON MSS_PERSON.PSN_ADR_OID_PHYSICAL=TEMP_ADDRESSES.ADR_OID;

SELECT	
	TEMP_PERSON.City as 'City', -- City in which the student resides
	TEMP_PERSON.Email as 'Email',
	REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(STD_GRADE_LEVEL,'0K','K'),'01','1'),'02','2'),'03','3'),'04','4'),'05','5'),'06','6'),'07','7'),'08','8'),'09','9') as 'Grade',
	TEMP_PERSON.Phone as 'Phone',
	TEMP_PERSON.State as 'State',
	TEMP_PERSON.Street as 'Street',
	TEMP_PERSON.LastName as 'LastName',
	MSS_SCHOOL.SKL_SCHOOL_ID as 'SchoolId',
	TEMP_PERSON.FirstName as 'FirstName',
	'Primary' as 'PhoneType',
	TEMP_PERSON.DateOfBirth as 'DateOfBirth',
	STD_ID_LOCAL as 'StudentNumber', -- Internal division student number
	STD_ID_LOCAL as 'StudentRegionId' -- Provincial student number
FROM
	MSS_STUDENT
	LEFT OUTER JOIN MSS_SCHOOL ON MSS_STUDENT.STD_SKL_OID=MSS_SCHOOL.SKL_OID
	LEFT OUTER JOIN #LSKYSD202_Temp_MSS_PERSON as TEMP_PERSON ON MSS_STUDENT.STD_PSN_OID=TEMP_PERSON.PSN_OID
WHERE
	STD_ENROLLMENT_STATUS IN ('Active', 'Active No Primary')
	AND MSS_SCHOOL.SKL_OID IS NOT NULL
	
DROP TABLE IF EXISTS #LSKYSD202_Temp_MSS_PERSON;
DROP TABLE IF EXISTS #LSKYSD202_TEMP_MSS_ADDRESS;