Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- s.last_name || ’,
- ’ || ’ ’ || s.first_name || ’ ’ || s.middle_name AS "student name",
- CASE WHEN se.syear = {SYEAR}
- AND se.end_date IS NULL THEN ’1’ ELSE ’2’ END AS Stat,
- s.custom_200000003 AS "Soc Sec",
- sgl.short_name AS Gr,
- to_char(
- CAST(s.custom_200000004 AS DATE),
- ’MM - DD - YY’
- ) AS DOB,
- CASE WHEN s.custom_200000000 = ’[M] Male’ THEN ’M’ WHEN s.custom_200000000 = ’[F] Female’ THEN ’F’ END AS Sex,
- CASE WHEN custom_100000105 = ’Hispanic / Latino’ THEN ’H’ WHEN custom_100000100 = ’Yes’
- AND custom_100000101 = ’No’
- AND custom_100000102 = ’No’
- AND custom_100000103 = ’No’
- AND custom_100000104 = ’No’ THEN ’I’ WHEN custom_100000100 = ’No’
- AND custom_100000101 = ’Yes’
- AND custom_100000102 = ’No’
- AND custom_100000103 = ’No’
- AND custom_100000104 = ’No’ THEN ’A’ WHEN custom_100000100 = ’No’
- AND custom_100000101 = ’No’
- AND custom_100000102 = ’Yes’
- AND custom_100000103 = ’No’
- AND custom_100000104 = ’No’ THEN ’B’ WHEN custom_100000100 = ’No’
- AND custom_100000101 = ’No’
- AND custom_100000102 = ’No’
- AND custom_100000103 = ’Yes’
- AND custom_100000104 = ’No’ THEN ’P’ WHEN custom_100000100 = ’No’
- AND custom_100000101 = ’No’
- AND custom_100000102 = ’No’
- AND custom_100000103 = ’No’
- AND custom_100000104 = ’Yes’ THEN ’W’ ELSE ’T’ END AS "Race",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field2 IS NOT NULL THEN sle.log_field4 ELSE ’NA’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000024
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’NA’
- ) AS "Last Grd Fail",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field2 IS NOT NULL THEN sle.log_field5 ELSE ’NA’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000024
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’NA’
- ) AS "Last Yr Failed",
- --Failed Readiness Test
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field3 IS NULL
- AND sle.log_field4 IS NOT NULL THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000020
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "01",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field3 IS NULL
- AND sle.log_field4 IS NOT NULL
- AND sle.log_field2 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000020
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "01_d",
- --Below 70
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field5 IS NULL
- AND sle.log_field2 IS NOT NULL THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000022
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "02",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field5 IS NULL
- AND sle.log_field2 IS NOT NULL
- AND sle.log_field3 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000022
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "02_d",
- --Not Promoted
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field2 IS NOT NULL THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000022
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "03",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field5 IS NULL
- AND sle.log_field2 IS NOT NULL
- AND sle.log_field3 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000024
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "03_d",
- --Failed TAAS or TAKS
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field2 IS NOT NULL
- AND sle.log_field5 IS NULL THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000026
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "04",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field5 IS NULL
- AND sle.log_field2 IS NOT NULL
- AND sle.log_field3 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000020
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "04_d",
- --Pregnant Parent
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field4 IS NULL
- AND sle.log_field6 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000024
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "05",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field4 IS NULL
- AND sle.log_field3 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000028
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "05_d",
- --DAEP
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field5 IS NULL
- AND sle.log_field2 IS NOT NULL THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000030
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "06",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field5 IS NULL
- AND sle.log_field2 IS NOT NULL
- AND sle.log_field3 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000030
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "06_d",
- --Expelled
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field5 IS NULL
- AND sle.log_field2 IS NOT NULL THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000032
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "07",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field5 IS NULL
- AND sle.log_field2 IS NOT NULL
- AND sle.log_field3 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000032
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "07_d",
- --Judicial Release
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field4 IS NULL
- AND sle.log_field2 IS NOT NULL THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000034
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "08",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field4 IS NULL
- AND sle.log_field2 IS NOT NULL
- AND sle.log_field3 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000034
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "08_d",
- --Dropout
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field2 IS NOT NULL THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000036
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "09",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field2 IS NOT NULL
- AND sle.log_field3 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000036
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "09_d",
- --LEP
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field2 IS NULL
- AND sle.log_field4 = ’1 - IDENTIFIED AS Limited English Proficient (LEP) ’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- student_field_id = 400010549
- AND sle.log_field2 IS NULL
- ORDER BY
- to_char(
- CAST(log_field1 AS DATE),
- ’YYYYMMDD’
- ) DESC
- LIMIT
- 1
- ),
- ’No’
- ) AS "10",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field4 IS NULL
- AND sle.log_field2 IS NOT NULL
- AND sle.log_field3 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- student_field_id = 500000038
- AND sle.log_field2 IS NULL
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ),
- ’No’
- ) AS "10_d",
- --Protective Services
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field4 IS NULL
- AND sle.log_field2 IS NOT NULL THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000040
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "11",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field4 IS NULL
- AND sle.log_field2 IS NOT NULL
- AND sle.log_field3 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000040
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "11_d",
- --Homeless
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field4 IS NULL
- AND sle.log_field2 IS NOT NULL THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000042
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "12",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field4 IS NULL
- AND sle.log_field2 IS NOT NULL
- AND sle.log_field3 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 500000042
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "12_d",
- --Residential Placement
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field4 IS NULL
- AND sle.log_field2 IS NOT NULL THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 5000044
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "13",
- COALESCE(
- (
- SELECT
- CASE WHEN sle.log_field4 IS NULL
- AND sle.log_field2 IS NOT NULL
- AND sle.log_field3 = ’Y’ THEN ’Y’ ELSE ’N’ END
- FROM
- student_log_entries sle
- WHERE
- s.student_id = sle.student_id
- AND student_field_id = 5000044
- ORDER BY
- sle.log_field1 DESC
- LIMIT
- 1
- ), ’N’
- ) AS "13_d"
- FROM
- students s,
- student_enrollment se,
- school_gradelevels sgl
- WHERE
- s.student_id = se.student_id
- AND se.grade_id = sgl.id
- AND se.syear = {SYEAR}
- AND s.custom_400000068 = ’[1] Yes’
- AND se.school_id = {SCHOOL_ID}
- ORDER BY
- "student name";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement