select distinct isnull(eo.NameOfInstitution,'') as School,s.LastSurname, s.FirstName , dg.CodeValue as GradeLevel, ssa.EntryDate, ssa.ExitWithdrawDate ,'F '+isnull((select top 1 sas.Result 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 left join edfi.Descriptor dt on dt.DescriptorId=sas.AssessmentReportingMethodDescriptorId left join edfi.Descriptor rdt on rdt.DescriptorId=sas.ResultDatatypeTypeDescriptorId where d.Description like 'Math%' and sa.AdministrationDate< '2021-01-21'and rdt.ShortDescription='Integer' and sa.StudentUSI=ssa.StudentUSI and a.AssessmentFamily like 'NWEA%' order by sas.Result desc),'') +' W '+isnull((select top 1 sas.Result 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 left join edfi.Descriptor dt on dt.DescriptorId=sas.AssessmentReportingMethodDescriptorId left join edfi.Descriptor rdt on rdt.DescriptorId=sas.ResultDatatypeTypeDescriptorId where d.Description like 'Math%' and sa.AdministrationDate > '2021-01-20' and sa.AdministrationDate < '2021-03-16' and rdt.ShortDescription='Integer' and sa.StudentUSI=ssa.StudentUSI and a.AssessmentFamily like 'NWEA%' order by sas.Result desc),'') +' S '+isnull((select top 1 sas.Result 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 left join edfi.Descriptor dt on dt.DescriptorId=sas.AssessmentReportingMethodDescriptorId left join edfi.Descriptor rdt on rdt.DescriptorId=sas.ResultDatatypeTypeDescriptorId where d.Description like 'Math%' and sa.AdministrationDate > '2021-03-15' and rdt.ShortDescription='Integer' and sa.StudentUSI=ssa.StudentUSI and a.AssessmentFamily like 'NWEA%' order by sas.Result desc),'') as [NWEA-2021-MATH RIT] ,'F '+isnull((select top 1 sas.Result 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 left join edfi.Descriptor dt on dt.DescriptorId=sas.AssessmentReportingMethodDescriptorId left join edfi.Descriptor rdt on rdt.DescriptorId=sas.ResultDatatypeTypeDescriptorId where d.Description = 'Reading' and sa.AdministrationDate < '2021-01-21' and rdt.ShortDescription='Integer' and sa.StudentUSI=ssa.StudentUSI and a.AssessmentFamily like 'NWEA%' order by sas.Result desc),'') +' W '+isnull((select top 1 sas.Result 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 left join edfi.Descriptor dt on dt.DescriptorId=sas.AssessmentReportingMethodDescriptorId left join edfi.Descriptor rdt on rdt.DescriptorId=sas.ResultDatatypeTypeDescriptorId where d.Description = 'Reading' and sa.AdministrationDate > '2021-01-20' and sa.AdministrationDate < '2021-03-16' and rdt.ShortDescription='Integer' and sa.StudentUSI=ssa.StudentUSI and a.AssessmentFamily like 'NWEA%' order by sas.Result desc),'') +' S '+isnull((select top 1 sas.Result 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 left join edfi.Descriptor dt on dt.DescriptorId=sas.AssessmentReportingMethodDescriptorId left join edfi.Descriptor rdt on rdt.DescriptorId=sas.ResultDatatypeTypeDescriptorId where d.Description = 'Reading' and sa.AdministrationDate > '2021-03-15' and rdt.ShortDescription='Integer' and sa.StudentUSI=ssa.StudentUSI and a.AssessmentFamily like 'NWEA%' order by sas.Result desc),'') as [NWEA-2021-READING RIT] ,'F '+isnull((select top 1 sas.Result 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 left join edfi.Descriptor dt on dt.DescriptorId=sas.AssessmentReportingMethodDescriptorId left join edfi.Descriptor rdt on rdt.DescriptorId=sas.ResultDatatypeTypeDescriptorId where d.Description = 'Language Usage' and sa.AdministrationDate< '2021-01-21' and rdt.ShortDescription='Integer' and sa.StudentUSI=ssa.StudentUSI and a.AssessmentFamily like 'NWEA%' order by sas.Result desc),'') +' W '+isnull((select top 1 sas.Result 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 left join edfi.Descriptor dt on dt.DescriptorId=sas.AssessmentReportingMethodDescriptorId left join edfi.Descriptor rdt on rdt.DescriptorId=sas.ResultDatatypeTypeDescriptorId where d.Description = 'Language Usage' and sa.AdministrationDate > '2021-01-20' and sa.AdministrationDate < '2021-03-16' and rdt.ShortDescription='Integer' and sa.StudentUSI=ssa.StudentUSI and a.AssessmentFamily like 'NWEA%' order by sas.Result desc),'') +' S '+isnull((select top 1 sas.Result 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 left join edfi.Descriptor dt on dt.DescriptorId=sas.AssessmentReportingMethodDescriptorId left join edfi.Descriptor rdt on rdt.DescriptorId=sas.ResultDatatypeTypeDescriptorId where d.Description = 'Language Usage' and sa.AdministrationDate > '2021-03-15' and rdt.ShortDescription='Integer' and sa.StudentUSI=ssa.StudentUSI and a.AssessmentFamily like 'NWEA%' order by sas.Result desc),'') as [NWEA-2021-ELA RIT] 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