SSRS SQL Query: Nested Select Statement

**Select Person_Name, (Select Person_Salary from Salary) AS Salary From Person**

DECLARE     @Y1RefDate1 Date, @Y1RefDate2 Date ,@CollegeYear1 Int, @CollegeYear2 int, @CollegeYear3 int

SET         @Y1RefDate1 = '12-OCT-2013'

SET         @Y1RefDate2 = '01-JAN-2014'

SET         @CollegeYear1 = 2013

SET         @CollegeYear2 = 2012

SET         @CollegeYear3 = 2011

SELECT                  CR11.Course

                        ,SUM(CR11.Y1_Start_Num) AS Y1_Start_Num

                        ,SUM(CR22.Y1_End_Num) as Y1_End_Num

                        ,SUM(CR33.Y2_Start_Num) AS Y2_Start_Num

                        ,SUM(CR44.Y2_End_Num) as Y2_End_Num

                        ,SUM(CR55.Y3_Start_Num) AS Y3_Start_Num

                        ,SUM(CR66.Y3_End_Num) as Y3_End_Num            

FROM

     

      (SELECT     CR1.Course, Count(CR1.Y1_Start_Num) as Y1_Start_Num

      FROM

            (SELECT                 RTRIM(STEN_Provision_Code) +

                                    COALESCE((SELECT RTRIM(GNUC_CODE_1) FROM GNUCustom WHERE GNUC_Type = 'PRIL' AND GNUC_Entity_ISN = PRIL_ISN),'') AS Course

                                    ,COALESCE((SELECT GNUC_CODE_1 FROM GNUCustom WHERE GNUC_Type = 'PRIL' AND GNUC_Entity_ISN = PRIL_ISN),'') AS Cohort

                                    ,(STEN_Student_ID) AS Y1_Start_Num

            FROM              STYRstudentYR

            INNER JOIN        ACYR  ON ACYR_College_Year=STYR_Year

            INNER JOIN        STEN  ON STEN_Student_ID=STYR_Student_ID AND STEN_Year=STYR_Year

            INNER JOIN        PRILILR

                  ON                      PRIL_Code = STEN_Provision_Code

                  AND                     PRIL_Instance = STEN_Provision_Instance

                  AND                     PRIL_Year = STEN_Year

            WHERE             STYR_Year=@CollegeYear1

                  AND               STEN_Exclude_from_ILR=0

                  AND               STEN_Annual_Start_Date<=@Y1RefDate1

                  AND               (STEN_Actual_End_Date>@Y1RefDate1 OR STEN_Actual_End_Date IS NULL)

           

            --GROUP BY        RTRIM(STEN_Provision_Code),PRIL_ISN

            )AS CR1

      GROUP BY CR1.Course

      )AS CR11

     

     

     

      LEFT JOIN

      (SELECT     CR2.Course, Count(CR2.Y1_End_Num) as Y1_End_Num

      FROM

            (SELECT                 RTRIM(STEN_Provision_Code) +

                                    COALESCE((SELECT RTRIM(GNUC_CODE_1) FROM GNUCustom WHERE GNUC_Type = 'PRIL' AND GNUC_Entity_ISN = PRIL_ISN),'') AS Course

                                    ,COALESCE((SELECT GNUC_CODE_1 FROM GNUCustom WHERE GNUC_Type = 'PRIL' AND GNUC_Entity_ISN = PRIL_ISN),'') AS Cohort

                                    ,(STEN_Student_ID) AS Y1_End_Num

            FROM              STYRstudentYR

            INNER JOIN        ACYR  ON ACYR_College_Year=STYR_Year

            INNER JOIN        STEN  ON STEN_Student_ID=STYR_Student_ID AND STEN_Year=STYR_Year

            INNER JOIN        PRILILR

                  ON                      PRIL_Code = STEN_Provision_Code

                  AND                     PRIL_Instance = STEN_Provision_Instance

                  AND                     PRIL_Year = STEN_Year

            WHERE             STYR_Year=@CollegeYear1

                  AND               STEN_Exclude_from_ILR=0

                  AND               STEN_Annual_Start_Date<=@Y1RefDate2

                  AND               (STEN_Actual_End_Date>@Y1RefDate2 OR STEN_Actual_End_Date IS NULL)

           

            --GROUP BY        RTRIM(STEN_Provision_Code),PRIL_ISN

            )AS CR2

      GROUP BY CR2.Course

      )AS CR22         

      ON CR11.Course=CR22.Course

     

--Group for top select statement

GROUP BY CR11.Course

     

ORDER BY 1