Skip to content

Instantly share code, notes, and snippets.

@cmgiven

cmgiven/julie-bisi-query.sql

Created September 6, 2019 17:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save cmgiven/cee355528d2da3320ee7b6edc3f9b8be to your computer and use it in GitHub Desktop.
Save cmgiven/cee355528d2da3320ee7b6edc3f9b8be to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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; */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment