DECLARE @GR INT = 10;
DECLARE @SC INT = 30;

WITH    cteSource ( NAME, ID, GR, SC, Ethnicity, Gate, SpecialED, LF, [A-G C- or Better], Affliation, Tardies, Referrals, Absences, [A-G Schedule] )
          AS ( SELECT   s.ID ,
                        s.GR ,
                        s.SC ,
                        s.LN + ', ' + s.FN AS NAME ,
                        ( CASE WHEN s.RC1 = 700
                                    AND ETH = 'Y' THEN 'Hispanic'
                               WHEN s.RC1 = 600
                                    AND ETH = 'N' THEN 'African American'
                               ELSE 'Other'
                          END ) AS Ethnicity ,
                        ( CASE WHEN s.U7 = 'G' THEN 'Yes'
                               ELSE 'No'
                          END ) AS Gate ,
                        ( CASE WHEN s.U9 IN ( 'R', 'D', 'S' ) THEN 'Yes'
                               ELSE 'No'
                          END ) AS SpecialEd ,
                        ( CASE WHEN s.LF = '1' THEN 'English Only'
                               WHEN s.LF = '2'
                               THEN 'Initially Fluent English Proficient'
                               WHEN s.LF = '3' THEN 'English Learner'
                               WHEN s.LF = '4'
                               THEN 'Redesignated Fluent English Proficient'
                               WHEN s.LF = '5' THEN 'TBD'
                               ELSE '?'
                          END ) AS LF ,
                        ( SELECT    CASE WHEN HISTORY >= 0
                                              AND ELA >= 1
                                              AND MATH >= 1
                                              AND SCIENCE >= 1
                                              AND FL >= 1
                                              AND VA >= 0
                                              AND Prep >= 0 THEN 'Yes'
                                         ELSE 'No'
                                    END AS [A-G C- or Better]
                          FROM      ( SELECT    SUM(HISTORY) AS HISTORY ,
                                                SUM(ELA) AS ELA ,
                                                SUM(MATH) AS MATH ,
                                                SUM(SCIENCE) AS SCIENCE ,
                                                SUM(FL) AS FL ,
                                                SUM(VA) AS VA ,
                                                SUM(Prep) AS Prep ,
                                                COUNT(*) AS Items
                                      FROM      ( SELECT    SUM(CASE
                                                              WHEN c.U1 = 'A'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS HISTORY ,
                                                            SUM(CASE
                                                              WHEN c.U1 = 'B'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS ELA ,
                                                            SUM(CASE
                                                              WHEN c.U1 = 'C'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS MATH ,
                                                            SUM(CASE
                                                              WHEN c.U1 = 'D'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS SCIENCE ,
                                                            SUM(CASE
                                                              WHEN c.U1 = 'E'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS FL ,
                                                            SUM(CASE
                                                              WHEN c.U1 = 'F'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS VA ,
                                                            SUM(CASE
                                                              WHEN c.U1 = 'G'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS Prep
                                                  FROM      CRS AS c
                                                            INNER JOIN HIS AS h ON h.CN = c.CN
                                                              AND ( h.MK NOT IN (
                                                              'F', 'F+', 'F-',
                                                              'D', 'D-', 'D+',
                                                              'NM', 'NC' ) )
                                                            INNER JOIN STU AS s ON s.ID = h.PID
                                                              AND s.GR = @GR
                                                              AND s.SC = @SC
                                                              AND s.TG NOT IN (
                                                              'N', '*', 'I' )
                                                  WHERE     c.U1 IN ( 'A', 'B',
                                                              'C', 'D', 'E',
                                                              'F', 'G' )
                                                  UNION ALL
                                                  SELECT    SUM(CASE
                                                              WHEN c.U1 = 'A'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS HISTORY ,
                                                            SUM(CASE
                                                              WHEN c.U1 = 'B'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS ELA ,
                                                            SUM(CASE
                                                              WHEN c.U1 = 'C'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS MATH ,
                                                            SUM(CASE
                                                              WHEN c.U1 = 'D'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS SCIENCE ,
                                                            SUM(CASE
                                                              WHEN c.U1 = 'E'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS FL ,
                                                            SUM(CASE
                                                              WHEN c.U1 = 'F'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS VA ,
                                                            SUM(CASE
                                                              WHEN c.U1 = 'G'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS Prep
                                                  FROM      dbo.CRS AS c
                                                            INNER JOIN GRD AS g ON g.CN = c.CN
                                                              AND ( g.M1 NOT IN (
                                                              'F', 'F+', 'F-',
                                                              'D', 'D-', 'D+',
                                                              'NM', 'NC' ) )
                                                            INNER JOIN STU AS s ON s.SN = g.SN
                                                              AND s.GR = @GR
                                                              AND s.SC = @SC
                                                              AND s.TG NOT IN (
                                                              'N', '*', 'I' )
                                                  WHERE     c.U1 IN ( 'A', 'B',
                                                              'C', 'D', 'E',
                                                              'F', 'G' )
                                                ) AS d
                                    ) AS d
                          UNION ALL
                          SELECT    COALESCE(MIN(CASE WHEN a.SD IS NOT NULL
                                                           AND a.ED IS NULL
                                                           AND a.CD NOT BETWEEN 600 AND 699
                                                      THEN 'Yes'
                                                      ELSE 'No'
                                                 END), 'No') AS Affliation
                          FROM      STU AS s
                                    LEFT JOIN ACT AS a ON a.PID = s.ID
                          WHERE     s.TG NOT IN ( 'N', '*', 'I' )
                                    AND s.GR = @GR
                                    AND s.SC = @SC
                          UNION ALL
                          SELECT    CASE WHEN COALESCE([Total Tardies], 0) <= 6
                                         THEN 'Yes'
                                         ELSE 'No'
                                    END AS Tardies
                          FROM      ( SELECT    SUM(CASE WHEN a.[A1] IN ( 'T',
                                                              'Z', 'B' )
                                                         THEN 1
                                                         ELSE 0
                                                    END)
                                                + SUM(CASE WHEN a.[A2] IN (
                                                              'T', 'Z', 'B' )
                                                           THEN 1
                                                           ELSE 0
                                                      END)
                                                + SUM(CASE WHEN a.[A3] IN (
                                                              'T', 'Z', 'B' )
                                                           THEN 1
                                                           ELSE 0
                                                      END)
                                                + SUM(CASE WHEN a.[A4] IN (
                                                              'T', 'Z', 'B' )
                                                           THEN 1
                                                           ELSE 0
                                                      END)
                                                + SUM(CASE WHEN a.[A5] IN (
                                                              'T', 'Z', 'B' )
                                                           THEN 1
                                                           ELSE 0
                                                      END)
                                                + SUM(CASE WHEN a.[A6] IN (
                                                              'T', 'Z', 'B' )
                                                           THEN 1
                                                           ELSE 0
                                                      END) AS [Total Tardies]
                                      FROM      ATT AS a
                                                INNER JOIN STU AS s ON s.SN = a.SN
                                                              AND s.TG NOT IN (
                                                              'N', '*', 'I' )
                                                              AND s.GR = @GR
                                                              AND s.SC = @SC
                                    ) AS d
                          UNION ALL
                          SELECT    CASE WHEN COUNT(a.PID) >= 2
                                              AND SUM(CASE WHEN a.DS = 'SUS'
                                                           THEN 1
                                                           ELSE 0
                                                      END) >= 1 THEN 'No'
                                         ELSE 'Yes'
                                    END AS Referrals
                          FROM      STU AS s
                                    LEFT JOIN dbo.ADS AS a ON a.PID = s.ID
                          WHERE     s.TG NOT IN ( 'N', '*', 'I' )
                                    AND s.GR = @GR
                                    AND s.SC = @SC
                          UNION ALL
                          SELECT    CASE WHEN [Total Absenecs] <= 12
                                         THEN 'Yes'
                                         ELSE 'No'
                                    END AS Absences
                          FROM      ( SELECT    SUM(CASE WHEN a.[A1] IN ( 'A',
                                                              'E', 'I', 'K',
                                                              'L', 'S', 'U',
                                                              'X' ) THEN 1
                                                         ELSE 0
                                                    END)
                                                + SUM(CASE WHEN a.[A2] IN (
                                                              'A', 'E', 'I',
                                                              'K', 'L', 'S',
                                                              'U', 'X' )
                                                           THEN 1
                                                           ELSE 0
                                                      END)
                                                + SUM(CASE WHEN a.[A3] IN (
                                                              'A', 'E', 'I',
                                                              'K', 'L', 'S',
                                                              'U', 'X' )
                                                           THEN 1
                                                           ELSE 0
                                                      END)
                                                + SUM(CASE WHEN a.[A4] IN (
                                                              'A', 'E', 'I',
                                                              'K', 'L', 'S',
                                                              'U', 'X' )
                                                           THEN 1
                                                           ELSE 0
                                                      END)
                                                + SUM(CASE WHEN a.[A5] IN (
                                                              'A', 'E', 'I',
                                                              'K', 'L', 'S',
                                                              'U', 'X' )
                                                           THEN 1
                                                           ELSE 0
                                                      END)
                                                + SUM(CASE WHEN a.[A6] IN (
                                                              'A', 'E', 'I',
                                                              'K', 'L', 'S',
                                                              'U', 'X' )
                                                           THEN 1
                                                           ELSE 0
                                                      END) AS [Total Absenecs]
                                      FROM      ATT AS a
                                                INNER JOIN dbo.STU AS s ON s.SN = a.SN
                                                              AND s.TG NOT IN (
                                                              'N', '*', 'I' )
                                                              AND s.SC = @SC
                                                              AND s.GR = @GR
                                    ) AS d
                          UNION ALL
                          SELECT    CASE WHEN A >= 0
                                              AND B >= 1
                                              AND C >= 1
                                              AND D >= 1
                                              AND E >= 1
                                              AND F >= 0
                                              AND G >= 0 THEN 'Yes'
                                         ELSE 'No'
                                    END AS [A-G Schedule]
                          FROM      ( SELECT    SUM(A) AS A ,
                                                SUM(B) AS B ,
                                                SUM(C) AS C ,
                                                SUM(D) AS D ,
                                                SUM(E) AS E ,
                                                SUM(F) AS F ,
                                                SUM(G) AS G
                                      FROM      ( SELECT    SUM(CASE
                                                              WHEN r.U1 = 'A'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS A ,
                                                            SUM(CASE
                                                              WHEN r.U1 = 'B'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS B ,
                                                            SUM(CASE
                                                              WHEN r.U1 = 'C'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS C ,
                                                            SUM(CASE
                                                              WHEN r.U1 = 'D'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS D ,
                                                            SUM(CASE
                                                              WHEN r.U1 = 'E'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS E ,
                                                            SUM(CASE
                                                              WHEN r.U1 = 'F'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS F ,
                                                            SUM(CASE
                                                              WHEN r.U1 = 'G'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS G
                                                  FROM      STU AS t
                                                            LEFT JOIN dbo.SSS
                                                            AS s ON s.SN = t.SN
                                                            LEFT JOIN dbo.CRS
                                                            AS r ON r.CN = s.CN
                                                              AND r.U1 IN (
                                                              'A', 'B', 'C',
                                                              'D', 'E', 'F',
                                                              'G' )
                                                  WHERE     t.TG NOT IN ( 'N',
                                                              '*', 'I' )
                                                            AND t.GR = @GR
                                                            AND t.SC = @SC
                                                  UNION ALL
                                                  SELECT    SUM(CASE
                                                              WHEN r.U1 = 'A'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS A ,
                                                            SUM(CASE
                                                              WHEN r.U1 = 'B'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS B ,
                                                            SUM(CASE
                                                              WHEN r.U1 = 'C'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS C ,
                                                            SUM(CASE
                                                              WHEN r.U1 = 'D'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS D ,
                                                            SUM(CASE
                                                              WHEN r.U1 = 'E'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS E ,
                                                            SUM(CASE
                                                              WHEN r.U1 = 'F'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS F ,
                                                            SUM(CASE
                                                              WHEN r.U1 = 'G'
                                                              THEN 1
                                                              ELSE 0
                                                              END) AS G
                                                  FROM      STU AS s
                                                            LEFT JOIN dbo.HIS
                                                            AS i ON i.PID = s.ID
                                                            LEFT JOIN dbo.CRS
                                                            AS r ON r.CN = i.CN
                                                              AND r.U1 IN (
                                                              'A', 'B', 'C',
                                                              'D', 'E', 'F',
                                                              'G' )
                                                  WHERE     s.TG NOT IN ( 'N',
                                                              '*', 'I' )
                                                            AND s.GR = @GR
                                                            AND s.SC = @SC
                                                ) AS d
                                    ) AS d
                        ) AS dervied
               FROM     STU AS s
             )
    SELECT  *
    FROM    cteSource
    GROUP BY NAME ,
            ID ,
            GR ,
            SC ,
            Ethnicity ,
            Gate ,
            SpecialED ,
            LF ,
            [A-G C- or Better] ,
            Affliation ,
            Tardies ,
            Referrals ,
            Absences ,
            [A-G Schedule]