with ethnicity as
(
select top 100 percent 
101913 as district,
rtrim(reg.student_id) student_id, 
FORMAT (birthdate, 'MMddyyyy')			[DATE-OF-BIRTH],
max(case when REG_ETHNICITY.ETHNIC_CODE = 1 then 1 else 0 end)			[AMERICAN-INDIAN-ALASKA-NATIVE-CODE (I)],
max(case when REG_ETHNICITY.ETHNIC_CODE = 2 then 1 else 0 end)			[ASIAN-CODE (A)],
max(case when REG_ETHNICITY.ETHNIC_CODE = 3 then 1 else 0 end)			[BLACK-AFRICAN-AMERICAN-CODE (B)],
max(case when REG_ETHNICITY.ETHNIC_CODE = 4 then 1 else 0 end)			[NATIVE-HAWAIIAN-PACIFIC-ISLANDER-CODE (P)],
max(case when REG_ETHNICITY.ETHNIC_CODE = 5 then 1 else 0 end)			[WHITE-CODE (W)]
from reg
left join REG_ETHNICITY on reg.STUDENT_ID = REG_ETHNICITY.STUDENT_ID
where CURRENT_STATUS = 'A'
group by reg.STUDENT_ID, BIRTHDATE
order by 1
)
,demo as (
Select distinct top 100 percent
reg.*
,isnull(case when rprogv3.program_value = 'Y' then '1' else rprogv3.program_value end,'0') [MIGRANT_PV]
,isnull(case when rprogv5.program_value = '5' then '0' else rprogv5.program_value end ,'0') [LEP_PV]
,isnull(rprogv24.program_value,'0') [BIL_PROGRAM_PV]
,isnull(rprogv25.program_value,'0') [ESL_PROGRAM_PV]
,case when rprogv7.program_value = 'Y' then 1 else 0 end [AT_RISK_PV]
,case when rprogv11.program_value = 'Y' then 1 else 0 end [GIFTED_TALENTED_PV]
FROM ethnicity as reg
LEFT OUTER JOIN REG_PROGRAMS rprogv3 ON  (REG.DISTRICT = rprogv3.DISTRICT AND rprogv3.PROGRAM_ID = '146' and rprogv3.FIELD_NUMBER = 3 and REG.STUDENT_ID = rprogv3.student_id and rprogv3.END_DATE is null)
LEFT OUTER JOIN REG_PROGRAMS rprogv5 ON  (REG.DISTRICT = rprogv5.DISTRICT AND rprogv5.PROGRAM_ID = '146' and rprogv5.FIELD_NUMBER = 5 and REG.STUDENT_ID = rprogv5.student_id and rprogv5.END_DATE is null)
LEFT OUTER JOIN REG_PROGRAMS rprogv24 ON  (REG.DISTRICT = rprogv24.DISTRICT AND rprogv24.PROGRAM_ID = '146' and rprogv24.FIELD_NUMBER = 24 and REG.STUDENT_ID = rprogv24.student_id and rprogv24.END_DATE is null)
LEFT OUTER JOIN REG_PROGRAMS rprogv25 ON  (REG.DISTRICT = rprogv25.DISTRICT AND rprogv25.PROGRAM_ID = '146' and rprogv25.FIELD_NUMBER = 25 and REG.STUDENT_ID = rprogv25.student_id and rprogv25.END_DATE is null)
LEFT OUTER JOIN REG_PROGRAMS rprogv7 ON  (REG.DISTRICT = rprogv7.DISTRICT AND rprogv7.PROGRAM_ID = '146' and rprogv7.FIELD_NUMBER = 7 and REG.STUDENT_ID = rprogv7.student_id and rprogv7.END_DATE is null)
LEFT OUTER JOIN REG_PROGRAMS rprogv11 ON  (REG.DISTRICT = rprogv11.DISTRICT AND rprogv11.PROGRAM_ID = '146' and rprogv11.FIELD_NUMBER = 11 and REG.STUDENT_ID = rprogv11.student_id and rprogv11.END_DATE is null)
order by 1
)
,eco as (
Select distinct top 100 percent
reg.*
,isnull(rtrim(STATE_CODE_EQUIV),'00') [ECO_DIS_PV]
FROM demo as reg
LEFT JOIN REG_PROGRAMS rprogv1 ON  (REG.DISTRICT = rprogv1.DISTRICT AND rprogv1.PROGRAM_ID = '145' and rprogv1.FIELD_NUMBER = 1 and REG.STUDENT_ID = rprogv1.student_id and rprogv1.END_DATE is null)
left join REGTB_MEAL_STATUS on rprogv1.PROGRAM_VALUE = REGTB_MEAL_STATUS.CODE
order by 1
)

select distinct
'101913'			[DistrictID],
cast(reg.student_id as char(6)) [LOCAL-STUDENT-ID],
rtrim(STATE_REPORT_ID)			[PEIMS-ID],
[DATE-OF-BIRTH],
case when reg_personal.HISPANIC	= 'Y' then 1 else 0 end	[HISPANIC-LATINO-CODE (ETH)],
[AMERICAN-INDIAN-ALASKA-NATIVE-CODE (I)],
[ASIAN-CODE (A)],
[BLACK-AFRICAN-AMERICAN-CODE (B)],
[NATIVE-HAWAIIAN-PACIFIC-ISLANDER-CODE (P)],
[WHITE-CODE (W)],
cast(eco_dis_pv as char(2))			[ECONOMIC-DISADVANTAGE-CODE (ED)],
isnull(getStudentPrograms.Title1,'0')			[TITLE-I-PART-A-INDICATOR-CODE (TIA)],
MIGRANT_PV	[MIGRANT-INDICATOR-CODE (MS)],
LEP_PV			[LEP-INDICATOR-CODE (L)],
BIL_PROGRAM_PV			[BILINGUAL-PROGRAM-TYPE-CODE (B)],
ESL_PROGRAM_PV			[ESL-PROGRAM-TYPE-CODE (ESL)],
GIFTED_TALENTED_PV 			[GIFTED-TALENTED-INDICATOR-CODE (G/T)],
AT_RISK_PV			[AT-RISK-INDICATOR-CODE (AR)],
isnull(getStudentPrograms.CTE_IND,'0')		[CAREER-AND-TECHNICAL-ED-IND-CD (CT)]
from eco as reg
inner join reg_personal on reg.STUDENT_ID = REG_PERSONAL.STUDENT_ID
INNER JOIN hmb.getStudentPrograms ON getStudentPrograms.STUDENT_ID = REG.student_id

where STATE_REPORT_ID is not null
order by 2