Central Union High School District

 

The following query is in process, but ready to go at the same time.  It's to be ran on SQL for an Aeries SIS database.

 

You might rather want to get the file \Departments\Educational Services\Collab\National Student Clearinghouse.sql.

 

 

 

--NOTE: Requires a header row:
-- PH3,10000876,Central Union High School District,P,20170613,2016-2017
--On Excel, make sure School ID have a leading zero. Check for no duplicates
--It will require a finish row: PT3    ### last row with student data, before this indicator


SELECT DISTINCT

    'PD3'                            'Required header column',
     'NO SSN'                        'Social Number',
     STU.FN                            'First Name',
     REPLACE(STU.MN,'.','')            'Middle Name',                --<--Replace function to delete dots if any
     STU.LN                            'Last Name',        
     REPLACE(STU.SF,'.','')            'Suffix',                    --<--Replace function to delete dots if any
     STU.LNA                        'Previuos Last Name',
     STU.FNA                        'Previous First Name',
     CONVERT(VARCHAR(8),STU.BD,112)    'Date of Birth',            --<--Formatted to their standard
     STU.ID                            'Student ID Number',

    CASE STU.HSG
        WHEN '100' THEN 'Did not Graduate from HS'
        WHEN '101' THEN 'Regular Diploma'
        WHEN '102' THEN 'Endorsed/Advanced Diploma'
        WHEN '103' THEN 'Vocational Certificate'
        WHEN '104' THEN 'Regular Diploma'
        WHEN '106' THEN 'Regular Diploma'
        WHEN '110' THEN 'Regular Diploma'
        WHEN '111' THEN 'Regular Diploma'
        WHEN '120' THEN 'Endorsed/Advanced Diploma'
        WHEN '130' THEN 'Regular Diploma'
        WHEN '170' THEN 'General Education Development (GED) Credential'
        WHEN '190' THEN 'International Baccalaureate'
        WHEN '210' THEN 'Other'
        WHEN '220' THEN 'Modified Diploma'
        WHEN '230' THEN 'Modified Diploma'
        ELSE ''
        END                            'Diploma Type',            --<-- Descriptions arbitrarily changed to their standard depending on each code

        CONVERT(varchar(8),STU.DG,112)    'High School Graduation Date',
        
    CASE STU.DNR
        WHEN '2' THEN 'Y'
        ELSE 'N'
        END                            'FERPA Block',
            
    CASE STU.SC
        WHEN 1 THEN 'Central Union High School'
        WHEN 10 THEN 'Southwest High School'
        END                            'High School Name',

    CASE STU.SC
        WHEN 1 THEN '050810'
        WHEN 10 THEN '050816'
        END                            'ACT Code',

        STU.GN                        'Gender',
        
    CASE
        WHEN LEFT(STU.ETH + STU.RC1,1) = 'Y' THEN 'HI'
        WHEN LEFT(STU.ETH + STU.RC1,2) = 'N1' THEN 'AM'
        WHEN LEFT(STU.ETH + STU.RC1,2) = 'N2' THEN 'AS'
        WHEN LEFT(STU.ETH + STU.RC1,2) = 'N3' THEN 'PI'
        WHEN LEFT(STU.ETH + STU.RC1,2) = 'N4' THEN 'PI'
        WHEN LEFT(STU.ETH + STU.RC1,2) = 'N6' THEN 'BL'
        WHEN LEFT(STU.ETH + STU.RC1,2) = 'N7' THEN 'WH'
        END                            'Ethnicity',                --<-- Descriptions arbitrarily changed to their standard depending on each code
            
     CASE FRE.CD
        WHEN 'F' THEN 'Y'
        WHEN 'R' THEN 'Y'
        ELSE 'N'
        END                            'Economically Disadvantaged Indicator',
    
      CASE SBACMAT.PL
        WHEN '1' THEN '1'
        WHEN '2' THEN '1'
        WHEN '3' THEN '2'
        WHEN '4' THEN '3'
        ELSE ''
        END as                    '8th Grade Math SBAC',    --    8th Grade State Assessment Result, Math (1 = Does Not Meet Standards, 2 = Proficient/Meets Standards, 3 = Advanced/Exceeds Standards)

      CASE SBACENG.PL
        WHEN '1' THEN '1'
        WHEN '2' THEN '1'
        WHEN '3' THEN '2'
        WHEN '4' THEN '3'
        ELSE ''
        END as                    '8th Grade Eng SBAC',    --    8th Grade State Assessment Result, ELA/Reading (1 = Does Not Meet Standards, 2 = Proficient/Meets Standards, 3 = Advanced/Exceeds Standards)

    CASE    --    High School Assessment Result, Math    (1 = Does Not Meet Standards, 2 = Proficient/Meets Standards, 3 = Advanced/Exceeds Standards)
        WHEN SBACM.PL = '1' THEN '1'
        WHEN SBACM.PL = '2' THEN '2'
        WHEN SBACM.PL > '2' THEN '3'
        ELSE ''
        END as 'Math SBAC',

    CASE    --High School Assessment Result, ELA/Reading (1 = Does Not Meet Standards, 2 = Proficient/Meets Standards, 3 = Advanced/Exceeds Standards)
        WHEN SBACE.PL = '1' THEN '1'
        WHEN SBACE.PL = '2' THEN '2'
        WHEN SBACE.PL > '2' THEN '3'
        ELSE ''
        END as 'Eng SBAC',
      
     CASE STU.LF
        WHEN 'L' THEN 'Y'
        ELSE 'N'
        END                            'English Learner Indicator',
    
     MCount.MathCount                'Number of Math Semesters Completed',                --    Number of Semesters of Math Completed
    
     CASE DualE.CN                    --    Dual Enrollment Indicator
        WHEN '2909' THEN 'Y'
        ELSE 'N'
        END                            'DE Indicator',    
    
     CASE
        WHEN CSE.DI <> '' THEN 'Y'
        ELSE 'N'
        END                            'Disability Code',

    
    ''                                'Program Code',    --    Program Code
     'ED'                            'END Column Indicator'

FROM STU  
    LEFT JOIN FRE on STU.ID = FRE.ID
    LEFT JOIN CSE ON STU.ID = CSE.ID
    
    LEFT JOIN (SELECT PID, SS, PL, TD FROM TST WHERE TST.ID = 'SBAC' and TST.GR = '80' AND TST.PT = '1') as SBACENG on SBACENG.PID = STU.ID
    LEFT JOIN (SELECT PID, SS, PL, TD FROM TST WHERE TST.ID = 'SBAC' and TST.GR = '80' AND TST.PT = '2') as SBACMAT on SBACMAT.PID = STU.ID

    LEFT JOIN    (SELECT CGroup.PID, count(CGroup.HClasses) as 'MathCount'
                FROM (SELECT HIS.PID, CRS.CO, count(CRS.CO) as 'HClasses' FROM HIS INNER JOIN CRS ON HIS.CN = CRS.CN
                WHERE CRS.S1 in ('I','J') AND HIS.CC > '0' GROUP BY HIS.PID, CRS.CO ) as CGroup GROUP BY CGroup.PID) as MCount ON STU.ID = MCOunt.pid

    LEFT JOIN    (SELECT DISTINCT STU.ID, HIS.CN FROM STU LEFT JOIN HIS ON STU.ID = HIS.PID WHERE HIS.CN = '2909') as DualE ON STU.ID = DualE.id
    LEFT JOIN    (SELECT DISTINCT STU.ID as 'StudentID', TST.ID, TST.PT, TST.PL FROM STU LEFT JOIN TST ON STU.ID = TST.PID WHERE TST.ID = 'SBAC' AND TST.PT = 1 AND TST.TD > '08/01/2018') as SBACE ON STU.ID = SBACE.StudentID    
    LEFT JOIN    (SELECT DISTINCT STU.ID as 'StudentID', TST.ID, TST.PT, TST.PL FROM STU LEFT JOIN TST ON STU.ID = TST.PID WHERE TST.ID = 'SBAC' AND TST.PT = 2 AND TST.TD > '08/01/2018') as SBACM ON STU.ID = SBACM.StudentID    

    
     WHERE STU.GR = '12' AND STU.HSG <> '' AND STU.SC IN ('1','10') AND STU.TG NOT IN ('S','C') AND STU.HSG <> '100'


     /* PREVIOUS ERORS TO CHECK

     Our system is unable to process the file for the following reason:
1.    Student Detail Rows: Full ACT Code is needed in column O (050810 instead of 50810)
2.    Student Detail Rows: The High School Name is not recognized, please enter the name of the school exactly as listed on the account reference sheet (should be submitted as CENTRAL UNION HIGH SCHOOL)
3.    Student Detail Rows: File cannot contain any special characters. Please remove “.” from all fields in the file.
4.    Student Detail Rows: 104 records have an unrecognized diploma type.  While it is okay to submit district specific diploma types, if possible, we encourage you to use a diploma type defined by IPEDS. (see attached graduate file formatting guide).
5.    Student Detail Rows: 25 records have been found as duplicates in this file. Please ensure that each record is only entered once and that all duplicates are removed from the file.
6.    Student Detail Rows: Account number is not valid for this account. Please make sure you enter 10000876 as your account number in the header row.
7.    Student Detail Rows: Please make sure the trailer row equals the number of rows in the file.


*/