DECLARE @ID INT = 4053717; 

SELECT  CASE WHEN HISTORY >= 1
                  AND ELA >= 3
                  AND MATH >= 1
                  AND SCIENCE >= 2
                  AND FL >= 1
                  AND VA >= 0
                  AND Prep >= 0 THEN 'Yes'
             ELSE 'No'
        END AS [On Target?]
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
          FROM      ( SELECT    COUNT(CASE WHEN CRS.U1 = 'A'
                                                AND ( HIS.MK NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS HISTORY ,
                                COUNT(CASE WHEN CRS.U1 = 'B'
                                                AND ( HIS.MK NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS ELA ,
                                COUNT(CASE WHEN CRS.U1 = 'C'
                                                AND ( HIS.MK NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS MATH ,
                                COUNT(CASE WHEN CRS.U1 = 'D'
                                                AND ( HIS.MK NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS SCIENCE ,
                                COUNT(CASE WHEN CRS.U1 = 'E'
                                                AND ( HIS.MK NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS FL ,
                                COUNT(CASE WHEN CRS.U1 = 'F'
                                                AND ( HIS.MK NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS VA ,
                                COUNT(CASE WHEN CRS.U1 = 'G'
                                                AND ( HIS.MK NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS Prep
                      FROM      CRS AS CRS
                                INNER JOIN HIS ON CRS.CN = HIS.CN
                                INNER JOIN STU ON HIS.PID = STU.ID
                      WHERE     ( STU.ID = @ID )
                      UNION ALL
                      SELECT    COUNT(CASE WHEN CRS.U1 = 'A'
                                                AND ( GRD.M1 NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS HISTORY ,
                                COUNT(CASE WHEN CRS.U1 = 'B'
                                                AND ( GRD.M1 NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS ELA ,
                                COUNT(CASE WHEN CRS.U1 = 'C'
                                                AND ( GRD.M1 NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS MATH ,
                                COUNT(CASE WHEN CRS.U1 = 'D'
                                                AND ( GRD.M1 NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS SCIENCE ,
                                COUNT(CASE WHEN CRS.U1 = 'E'
                                                AND ( GRD.M1 NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS FL ,
                                COUNT(CASE WHEN CRS.U1 = 'F'
                                                AND ( GRD.M1 NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS VA ,
                                COUNT(CASE WHEN CRS.U1 = 'G'
                                                AND ( GRD.M1 NOT LIKE '%FD%' )
                                           THEN STU.ID
                                      END) AS Prep
                      FROM      CRS AS CRS
                                INNER JOIN GRD ON GRD.CN = CRS.CN
                                INNER JOIN STU ON GRD.SN = STU.SN
                      WHERE     ( STU.ID = @ID )
                    ) AS r
        ) AS derived