WITH Assessment_Data (StudentUSI, AssessmentTitle, AdministrationDate,Result,ResultDataType,Subject,ReportingMethod,MonthTaken)  
AS  
(  
select sa.StudentUSI,a.AssessmentTitle,sa.AdministrationDate,sas.Result,rdt.ShortDescription as ResultDataTYpe
, d.ShortDescription as Subject, dt.ShortDescription as ReportingMethod,DATEPART(month,sa.AdministrationDate)
from edfi.StudentAssessment sa
left join edfi.Assessment a on a.AssessmentIdentifier=sa.AssessmentIdentifier
left join edfi.AssessmentAcademicSubject acs on acs.AssessmentIdentifier=sa.AssessmentIdentifier
left join edfi.Descriptor d on d.DescriptorId=acs.AcademicSubjectDescriptorId
left join edfi.StudentAssessmentScoreResult sas on sas.StudentUSI=sa.StudentUSI and sas.AssessmentIdentifier=sa.AssessmentIdentifier and sas.StudentAssessmentIdentifier=sa.StudentAssessmentIdentifier
left join edfi.Descriptor dt on dt.DescriptorId=sas.AssessmentReportingMethodDescriptorId
left join edfi.Descriptor rdt on rdt.DescriptorId=sas.ResultDatatypeTypeDescriptorId
where a.AssessmentTitle like '%2020-2021%' and dt.ShortDescription='RIT scale score' and sa.AdministrationDate> '2020-07-01'
)
select distinct isnull(eo.NameOfInstitution,'') as School,s.LastSurname, s.FirstName
, dg.CodeValue as GradeLevel, ssa.EntryDate, ssa.ExitWithdrawDate
, (select Result from Assessment_Data a where a.MonthTaken=9 and a.StudentUSI=s.StudentUSI and a.Subject='Math K-12') as SeptMath
, (select Result from Assessment_Data a where a.MonthTaken=10 and a.StudentUSI=s.StudentUSI and a.Subject='Math K-12') as OctMath
, (select Result from Assessment_Data a where a.MonthTaken=11 and a.StudentUSI=s.StudentUSI and a.Subject='Math K-12') as NovMath
, (select Result from Assessment_Data a where a.MonthTaken=12 and a.StudentUSI=s.StudentUSI and a.Subject='Math K-12') as DecMath
, (select Result from Assessment_Data a where a.MonthTaken=1 and a.StudentUSI=s.StudentUSI and a.Subject='Math K-12') as JanMath
, (select Result from Assessment_Data a where a.MonthTaken=2 and a.StudentUSI=s.StudentUSI and a.Subject='Math K-12') as FebMath
, (select Result from Assessment_Data a where a.MonthTaken=3 and a.StudentUSI=s.StudentUSI and a.Subject='Math K-12') as MarMath
, (select Result from Assessment_Data a where a.MonthTaken=4 and a.StudentUSI=s.StudentUSI and a.Subject='Math K-12') as AprMath
, (select Result from Assessment_Data a where a.MonthTaken=5 and a.StudentUSI=s.StudentUSI and a.Subject='Math K-12') as MayMath
, (select Result from Assessment_Data a where a.MonthTaken=6 and a.StudentUSI=s.StudentUSI and a.Subject='Math K-12') as JuneMath
, (select Result from Assessment_Data a where a.MonthTaken=9 and a.StudentUSI=s.StudentUSI and a.Subject='Reading') as SeptRead
, (select Result from Assessment_Data a where a.MonthTaken=10 and a.StudentUSI=s.StudentUSI and a.Subject='Reading') as OctRead
, (select Result from Assessment_Data a where a.MonthTaken=11 and a.StudentUSI=s.StudentUSI and a.Subject='Reading') as NovRead
, (select Result from Assessment_Data a where a.MonthTaken=12 and a.StudentUSI=s.StudentUSI and a.Subject='Reading') as DecRead
, (select Result from Assessment_Data a where a.MonthTaken=1 and a.StudentUSI=s.StudentUSI and a.Subject='Reading') as JanRead
, (select Result from Assessment_Data a where a.MonthTaken=2 and a.StudentUSI=s.StudentUSI and a.Subject='Reading') as FebRead
, (select Result from Assessment_Data a where a.MonthTaken=3 and a.StudentUSI=s.StudentUSI and a.Subject='Reading') as MarRead
, (select Result from Assessment_Data a where a.MonthTaken=4 and a.StudentUSI=s.StudentUSI and a.Subject='Reading') as AprRead
, (select Result from Assessment_Data a where a.MonthTaken=5 and a.StudentUSI=s.StudentUSI and a.Subject='Reading') as MayRead
, (select Result from Assessment_Data a where a.MonthTaken=6 and a.StudentUSI=s.StudentUSI and a.Subject='Reading') as JuneRead
from edfi.Student s 
left join edfi.StudentSchoolAssociation ssa on ssa.StudentUSI=s.StudentUSI and (ssa.SchoolYear is null or ssa.SchoolYear=(select syt.SchoolYear from edfi.SchoolYearType syt where syt.CurrentSchoolYear=1))
left join edfi.Descriptor dg on dg.DescriptorId=ssa.EntryGradeLevelDescriptorId
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId
where ssa.StudentUSI is not null and dg.CodeValue in ('00','01','02','03','04','05','06','07','08','0','1','2','3','4','5','6','7','8')
and (ssa.SchoolYear is null or ssa.SchoolYear=(select syt.SchoolYear from edfi.SchoolYearType syt where syt.CurrentSchoolYear=1))
order by 1,dg.CodeValue,s.LastSurname,s.FirstName