enrolled_siblings_SY22

SELECT 
	[athena_to_s3_scholar_data.csv].childid,
	[athena_to_s3_scholar_data.csv].appnum AS confnumber,
	[athena_to_s3_scholar_data.csv].firstname AS "firstname applying child",
	[athena_to_s3_scholar_data.csv].applicationstatus,
	[athena_to_s3_scholar_data.csv].isapplying,
	[athena_to_s3_scholar_data.csv].enrolledschoolname,
	[athena_to_s3_scholar_data.csv].enrollverified,
	[athena_to_s3_scholar_data.csv].highestrankschoolname,
	[athena_to_s3_enrolled_siblings.csv].firstname AS "firstname enrolled sibling",
	[athena_to_s3_enrolled_siblings.csv].lastname AS "lastname enrolled sibling",
	[athena_to_s3_enrolled_siblings.csv].enrolledschoolcode AS "school enrolled sibling",
	[athena_to_s3_enrolled_siblings.csv].gradeabbrev AS "grade enrolled sibling",
	[athena_to_s3_enrolled_siblings.csv].sibling_childid,
	[athena_to_s3_scholar_data.csv].responsibleadultid,
FROM [CSV1].[athena_to_s3_scholar_data.csv]
LEFT JOIN [CSV1].[athena_to_s3_enrolled_siblings.csv]
ON [CSV1].[athena_to_s3_scholar_data.csv].responsibleadultid = [CSV1].[athena_to_s3_enrolled_siblings.csv].responsibleadultid
WHERE [athena_to_s3_enrolled_siblings.csv].enrolledschoolcode IS NOT NULL
AND [athena_to_s3_scholar_data.csv].isapplying = 1
AND [athena_to_s3_scholar_data.csv].applicationstatus NOT LIKE 'Closed%'
ORDER BY [athena_to_s3_scholar_data.csv].appnum DESC;