Certica Products

Fort Bend requests report of all students who exited the previous school year

Updated Mar 24, 2021

In Certify go to SQL Queries and copy the RPT_STUDENT_LEAVERS query. 

Replace %varNCESLEAID% with the district's NCESLEAID.

Comment out or remove this line.

 AND (SCHOOL.School_ID = '%varSchool%'  OR '%varSchool%' = 'ALL')

Change this line:

  AND ENR.School_Year = DIST.Current_School_Year

To:

  AND ENR.School_Year = '2020' (enter whatever school year they want the data for).

Run it and export results to Excel:

It should look something like this:

use [4819650_CRL_SUMMARY]

SELECT DISTINCT

     ENR.School_Year

    ,ENR.School_Enrollment_ID AS School_ID

    ,SCHOOL.School_Name

    ,ENR.Grade_Level_Code as Grade

    ,ENR.Local_Student_ID

    ,CASE WHEN DEM.Local_Student_ID IS NULL THEN DEM2.First_Name ELSE DEM.First_Name END AS First_Name

    ,CASE WHEN DEM.Local_Student_ID IS NULL THEN DEM2.Last_Name ELSE DEM.Last_Name END AS Last_Name

    ,CASE WHEN DEM.Local_Student_ID IS NULL THEN DEM2.Special_Ed_Flag ELSE DEM.Special_Ed_Flag END AS Special_Ed_Flag

    ,CASE WHEN DEM.Local_Student_ID IS NULL THEN DEM2.ELL_Flag ELSE DEM.ELL_Flag END AS ELL_Flag

    ,ENR.Exit_Date

    ,ENR.Exit_Code

    ,ENR.Exit_Code_Desc

    ,ENR.Current_Record_Flag

    ,CASE WHEN MONTH(ENR.ETL_LOAD_DATE) = MONTH(Exit_Date) AND YEAR(ENR.ETL_LOAD_DATE) = YEAR(Exit_Date) THEN 'Y' else 'N' END AS Current_Month

    ,UPPER(CAST(DATENAME(MONTH,CAST(Exit_Date AS DATETIME)) AS CHAR(3)))  as Exit_Month

    ,MONTH(Exit_Date) as Exit_Month_num

    ,CASE WHEN

     ( ENR.Exit_Code_Desc LIKE '%NO SHOW%' OR

      ENR.Exit_Code_Desc LIKE '%NOSHOW%' OR

      ENR.Exit_Code_Desc LIKE '%NO-SHOW%' OR

ENR.Exit_Code_Desc = 'NS')

      THEN 'Y'

      ELSE 'N'

      END AS NO_SHOW_FLAG,

CASE WHEN DEM.Local_Student_ID IS NULL THEN SP2.Service_Start_Date ELSE SP.Service_Start_Date END as Service_Start_Date, 

CASE WHEN DEM.Local_Student_ID IS NULL THEN SP2.Service_End_Date ELSE SP.Service_End_Date END as Service_End_Date,

DIST.Current_School_Year_Start_Date,

DIST.Current_School_Year_End_Date,

DEM.State_Student_ID

FROM [4819650_CRL_SUMMARY].[DBO].[STUDENT_ENROLLMENT] ENR

LEFT JOIN  [4819650_CRL_SUMMARY].[DBO].[STUDENT_DEMOGRAPHIC] DEM

    ON (DEM.NCESLEAID= ENR.NCESLEAID

    AND DEM.Local_Student_ID = ENR.Local_Student_ID

    AND DEM.ETL_LOAD_STATUS = 'NEW')

LEFT JOIN  [4819650_CRL_SUMMARY].[DBO].[STUDENT_DEMOGRAPHIC_INACTIVE] DEM2

    ON (DEM2.NCESLEAID= ENR.NCESLEAID

    AND DEM2.Local_Student_ID = ENR.Local_Student_ID

    AND DEM2.ETL_LOAD_STATUS = 'NEW')

LEFT JOIN [4819650_CRL_SUMMARY].[DBO].[SCHOOL]  SCHOOL

    ON (SCHOOL.NCESLEAID= ENR.NCESLEAID

    AND SCHOOL.School_ID = ENR.School_Enrollment_ID

    AND SCHOOL.ETL_LOAD_STATUS = 'NEW')

LEFT JOIN dbo.CRL_CODE_COMBINATION CCC

    ON (CCC.NCESLEAID= ENR.NCESLEAID

    AND CCC.PARENT_CODE = ENR.Grade_Level_Code

    AND CCC.CHILD_CODE = ENR.Exit_Code

    AND CCC.IS_CURRENT = 1

    AND CCC.CODE_COMBINATION = 'Exclude Exit Codes'

)

 LEFT JOIN [4819650_CRL_SUMMARY].[DBO].[DISTRICT]  DIST

       ON DIST.NCESLEAID = ENR.NCESLEAID

       AND DIST.ETL_LOAD_STATUS = 'NEW'

LEFT JOIN (SELECT SP.NCESLEAID, SP.Service_Start_Date, SP.Service_End_Date,SP.Local_Student_ID,  

                   ROW_NUMBER() over (partition by SP.Local_Student_ID order by ISNULL(SP.Service_Start_Date,'01/01/1900') desc,School_ID) as Last_Rec

                   FROM [4819650_CRL_SUMMARY].[DBO].[STUDENT_PROGRAM] SP where SP.ETL_LOAD_STATUS = 'NEW' and SP.State_Code = '144' and SP.NCESLEAID = '4819650') AS SP

                   ON DEM.Local_Student_ID = SP.Local_Student_ID 

                   AND SP.Last_Rec = 1

LEFT JOIN (SELECT SP.NCESLEAID, SP.Service_Start_Date, SP.Service_End_Date,SP.Local_Student_ID,  

                   ROW_NUMBER() over (partition by SP.Local_Student_ID order by ISNULL(SP.Service_Start_Date,'01/01/1900') desc,School_ID) as Last_Rec

                   FROM [4819650_CRL_SUMMARY].[DBO].[STUDENT_PROGRAM] SP where SP.ETL_LOAD_STATUS = 'NEW' and SP.State_Code = '144' and SP.NCESLEAID = '4819650') AS SP2

                   ON DEM2.Local_Student_ID = SP2.Local_Student_ID

                   AND SP2.Last_Rec = 1

LEFT JOIN ( 

    SELECT NCESLEAID, StudentNumber, SchoolNumber  FROM dbo.CERTIFY_RULE_EXCEPTION 

    WHERE IsExcluded = 1 

    AND CertifyRule ='ALL'

    AND datediff(dd,Startdate,getdate()) >= 0

    AND datediff(dd,getdate(),isnull(Enddate,getdate())) > = 0

    AND StudentNumber<>'ALL'

    AND SchoolNumber <>'ALL'

  ) rExcSingleStu

ON 

 ENR.NCESLEAID = rExcSingleStu.NCESLEAID 

  AND ENR.Local_Student_ID =rExcSingleStu.StudentNumber 

  AND ENR.School_Enrollment_ID  =rExcSingleStu.SchoolNumber

LEFT JOIN ( 

    SELECT NCESLEAID, StudentNumber  FROM dbo.CERTIFY_RULE_EXCEPTION 

    WHERE IsExcluded = 1 

    AND CertifyRule ='ALL'

    AND datediff(dd,Startdate,getdate()) >= 0

    AND datediff(dd,getdate(),isnull(Enddate,getdate())) > = 0

    AND StudentNumber<>'ALL'

    AND SchoolNumber ='ALL'

  ) rExcSingleStuAllSchools

ON 

  ENR.NCESLEAID = rExcSingleStuAllSchools.NCESLEAID 

  AND ENR.Local_Student_ID=rExcSingleStuAllSchools.StudentNumber

LEFT JOIN ( 

    SELECT NCESLEAID, SchoolNumber FROM dbo.CERTIFY_RULE_EXCEPTION 

    WHERE IsExcluded = 1 

    AND CertifyRule ='ALL'

    AND datediff(dd,Startdate,getdate()) >= 0

    AND datediff(dd,getdate(),isnull(Enddate,getdate())) > = 0

    AND StudentNumber='ALL'

    AND SchoolNumber <>'ALL'

  ) rExcSingleSchool

ON 

  ENR.NCESLEAID = rExcSingleSchool.NCESLEAID 

  AND ENR.School_Enrollment_ID   =rExcSingleSchool.SchoolNumber

WHERE

      ENR.NCESLEAID= '4819650'

      AND ENR.ETL_LOAD_STATUS= 'NEW'

      AND ENR.School_Year = '2021'

      AND ENR.Exit_Date is not null

          AND ENR.Exit_Code IS NOT NULL

      AND ENR.Exit_Code  not in (

            select CHILD_CODE

            from CRL_CODE_COMBINATION

            where

            IS_CURRENT = 1

            and NCESLEAID = '4819650'

            and parent_code = 'IGNORE_EXIT_CODE'

            and datediff(dd,START_DATE,getdate()) >= 0

            and datediff(dd,getdate(),isnull(END_DATE,getdate())) > = 0 

            )

AND CCC.CHILD_CODE IS NULL

             --       AND (SCHOOL.School_ID = '%varSchool%'  OR '%varSchool%' = 'ALL')

 AND ( rExcSingleStu.StudentNumber is NULL

            AND rExcSingleStuAllSchools.NCESLEAID is null

            AND  rExcSingleSchool.SchoolNumber  IS NULL)

ORDER BY ENR.Local_Student_ID

Previous Article Escalation process - April 2020
Next Article Grant CertifySEIS File Upload Permission