julie-bisi-query.sql

select
	Enrollment.EnrollmentFunding.EnrollmentId,
	Student.Enrollment.FacilityCode,
	Universal.Agency.Name,
	Student.Student.SASID,
	Student.StudentDetails.LastName,
	Student.StudentDetails.FirstName,
	Student.StudentDetails.MiddleName,
	/* MISSING: Suffix */
	Student.StudentDetails.Dob,
	Student.StudentDetails.Gender,
	Race.Race,
	(case when Hispanic.Hispanic is null then 'N' else 'Y' end) as Hispanic,
	Student.Address.AddressType as WhoChildResidesWith,
	Student.Address.StreetNumber,
	Student.Address.Address1,
	Student.Address.Address2,
	Student.Address.Town,
	Student.Address.State,
	Student.Address.Zip,
	Enrollment.AdditionalStudentInfo.NumberOfPeopleInHousehold,
	Enrollment.AdditionalStudentInfo.AnnualFamilyIncome,
	Enrollment.AdditionalStudentInfo.DateFamilyIncomeDocumented,
	(case when Enrollment.AdditionalStudentInfo.FamilyIncomeNotDisclosed is null then 'N' else 'Y' end) as FamilyIncomeNotDisclosed,
	(case when Enrollment.AdditionalStudentInfo.IndividualizedIEP is null then 'N' else 'Y' end) as IndividualizedIEP,
	/* MISSING: IEPStartDate */
	(case when Enrollment.AdditionalStudentInfo.TransportationProvided is null then 'N' else 'Y' end) as TransportationProvided,
	/* MISSING: FacilityGroupName */
	Student.Enrollment.EnrollmentDate,
	Student.Enrollment.FacilityExitDate,
	Student.Enrollment.ExitCategory,
	Student.Enrollment.ExitReason,
	Enrollment.EnrollmentFunding.FundingType,
	Enrollment.EnrollmentFunding.SpaceType,
	Enrollment.EnrollmentFunding.StartDate as FundingStartDate,
	Enrollment.EnrollmentFunding.EndDate as FundingEndDate,
	month(Enrollment.EnrollmentFunding.StartDate) as FundingStartMonth,
	year(Enrollment.EnrollmentFunding.StartDate) as FundingStartYear,
	month(Enrollment.EnrollmentFunding.EndDate) as FundingEndMonth,
	year(Enrollment.EnrollmentFunding.EndDate) as FundingEndYear,
	(case when
		Enrollment.EnrollmentFunding.StartDate <= '20200630'
		and (Enrollment.EnrollmentFunding.EndDate is null or Enrollment.EnrollmentFunding.EndDate >= '20190701')
	then 'Y' else 'N' end) as SFY2020,
	(case when
		Enrollment.EnrollmentFunding.StartDate <= '20190630'
		and (Enrollment.EnrollmentFunding.EndDate is null or Enrollment.EnrollmentFunding.EndDate >= '20180701')
	then 'Y' else 'N' end) as SFY2019,
	(case when
		Enrollment.EnrollmentFunding.StartDate <= '20180630'
		and (Enrollment.EnrollmentFunding.EndDate is null or Enrollment.EnrollmentFunding.EndDate >= '20170701')
	then 'Y' else 'N' end) as SFY2018,
	(case when
		Enrollment.EnrollmentFunding.StartDate <= '20170630'
		and (Enrollment.EnrollmentFunding.EndDate is null or Enrollment.EnrollmentFunding.EndDate >= '20160701')
	then 'Y' else 'N' end) as SFY2017,
	(case when
		Enrollment.EnrollmentFunding.StartDate <= '20160630'
		and (Enrollment.EnrollmentFunding.EndDate is null or Enrollment.EnrollmentFunding.EndDate >= '20150701')
	then 'Y' else 'N' end) as SFY2016,
	/* MISSING: HsEhsaCode */
	AdditionalFundingSource.AdditionalFundingSource,
	Enrollment.EnrollmentFunding.DateCreated,
	Enrollment.EnrollmentFunding.ModifiedDate
from (
	select
		Enrollment.EnrollmentFunding.Id,
		row_number() over (
			partition by
				Enrollment.EnrollmentFunding.StartDate,
				Enrollment.EnrollmentFunding.EndDate,
				Enrollment.EnrollmentFunding.FundingType,
				Enrollment.EnrollmentFunding.SpaceType,
				Student.Enrollment.StudentId
	
			order by Enrollment.EnrollmentFunding.ModifiedDate desc
		) as rn

	from Enrollment.EnrollmentFunding

	left join Student.Enrollment on Enrollment.EnrollmentFunding.EnrollmentId = Student.Enrollment.Id

	where 1=1
) as UndupEnrollmentFunding
	
left join Enrollment.EnrollmentFunding on UndupEnrollmentFunding.Id = Enrollment.EnrollmentFunding.Id
left join Student.Enrollment on Enrollment.EnrollmentFunding.EnrollmentId = Student.Enrollment.Id
left join Student.Student on Student.Enrollment.StudentId = Student.Student.Id
left join Student.StudentDetails on Student.Student.Id = Student.StudentDetails.StudentId /* 3 duplicate records */
left join (
	select StudentId, Id,
		row_number() over (
			partition by StudentId
			order by ModifiedBy desc
		) as rn
	from Student.Address
) as UndupStudentAddress on Student.Student.Id = UndupStudentAddress.StudentId
left join Student.Address on UndupStudentAddress.Id = Student.Address.Id
left join (
	select StudentId, case when count(*) > 1 then 'Multiple Races' else MIN(RaceCode) end as Race
	from (select distinct StudentId, RaceCode from Student.Race where RaceCode <> 'Hispanic or Latino of any race') as T
	group by StudentId
) as Race on Student.Student.Id = Race.StudentId
left join (
	select distinct StudentId, 1 as Hispanic from Student.Race where RaceCode = 'Hispanic or Latino of any race'
) as Hispanic on Student.Student.Id = Hispanic.StudentId
left join (
	select StudentId, ID,
		row_number() over (
			partition by StudentId
			order by DateFamilyIncomeDocumented desc
		) as rn
	from Enrollment.AdditionalStudentInfo
) as UndupAdditionalStudentInfo on Student.Student.Id = UndupAdditionalStudentInfo.StudentId
left join Enrollment.AdditionalStudentInfo on UndupAdditionalStudentInfo.ID = Enrollment.AdditionalStudentInfo.ID
left join (
	select EnrollmentId, case when count(*) > 1 then 'Multiple Additional Funding Sources' else MIN(AdditionalFundingType) end as AdditionalFundingSource
	from (select distinct EnrollmentId, AdditionalFundingType from Enrollment.AdditionalFundingSources) as T
	group by EnrollmentId
) as AdditionalFundingSource on Student.Enrollment.Id = AdditionalFundingSource.EnrollmentId
left join Universal.Agency on Student.Enrollment.FacilityCode = Universal.Agency.Code

where UndupEnrollmentFunding.rn = 1
	and UndupStudentAddress.rn = 1
	and UndupAdditionalStudentInfo.rn = 1
	
order by FacilityCode, SASID;

/* select count(*), N from (select count(*) N from Enrollment.AdditionalFundingSources group by EnrollmentId, AdditionalFundingType) as T group by N; */