sample get_current_sections.sql

SELECT SECTIONS.EVENT_LONG_NAME + ' / ' + SECTIONS.SECTION as 'EVENT_LONG_NAME'
	,CAST(SECTIONS.SectionId AS NVARCHAR(20)) AS 'SectionId'
	,SECTIONS.ACADEMIC_YEAR + '/' + SECTIONS.ACADEMIC_TERM + '/' + SECTIONS.ACADEMIC_SESSION + '/' + SECTIONS.EVENT_ID + '/' + SECTIONS.EVENT_SUB_TYPE + '/' + SECTIONS.SECTION AS 'classCode'
	,SECTIONS.ACADEMIC_YEAR + SECTIONS.ACADEMIC_TERM + SECTIONS.ACADEMIC_SESSION + SECTIONS.EVENT_ID + SECTIONS.SECTION AS 'mailNickname'
	,TRANSCRIPTDETAIL = (
		SELECT TRANSCRIPTDETAIL.PEOPLE_CODE_ID AS 'PEOPLE_CODE_ID'
		FROM [TRANSCRIPTDETAIL]
		WHERE TRANSCRIPTDETAIL.EVENT_ID = SECTIONS.EVENT_ID
			AND TRANSCRIPTDETAIL.ACADEMIC_YEAR = SECTIONS.ACADEMIC_YEAR
			AND TRANSCRIPTDETAIL.ACADEMIC_TERM = SECTIONS.ACADEMIC_TERM
			AND TRANSCRIPTDETAIL.ACADEMIC_SESSION = SECTIONS.ACADEMIC_SESSION
			AND TRANSCRIPTDETAIL.EVENT_SUB_TYPE = SECTIONS.EVENT_SUB_TYPE
			AND TRANSCRIPTDETAIL.SECTION = SECTIONS.SECTION
			AND TRANSCRIPTDETAIL.ADD_DROP_WAIT = 'A'
		FOR JSON PATH 
		)
	,SECTIONPER = (
		SELECT SECTIONPER.PERSON_CODE_ID AS 'PERSON_CODE_ID'
		FROM [SECTIONPER]
		WHERE SECTIONPER.EVENT_ID = SECTIONS.EVENT_ID
			AND SECTIONPER.ACADEMIC_YEAR = SECTIONS.ACADEMIC_YEAR
			AND SECTIONPER.ACADEMIC_TERM = SECTIONS.ACADEMIC_TERM
			AND SECTIONPER.ACADEMIC_SESSION = SECTIONS.ACADEMIC_SESSION
			AND SECTIONPER.EVENT_SUB_TYPE = SECTIONS.EVENT_SUB_TYPE
			AND SECTIONPER.SECTION = SECTIONS.SECTION
		FOR JSON PATH
		)
	,term = (
		SELECT ACADEMICCALENDAR.ACADEMIC_YEAR + '/' + ACADEMICCALENDAR.ACADEMIC_TERM + '/' + ACADEMICCALENDAR.ACADEMIC_SESSION AS 'displayName'
			,CAST(SessionPeriodId AS NVARCHAR(20)) AS 'externalId'
			,CAST([START_DATE] AS DATE) AS 'startDate'
			,CAST(END_DATE AS DATE) AS 'endDate'
		FROM [ACADEMICCALENDAR]
		WHERE ACADEMICCALENDAR.ACADEMIC_YEAR = SECTIONS.ACADEMIC_YEAR
			AND ACADEMICCALENDAR.ACADEMIC_TERM = SECTIONS.ACADEMIC_TERM
			AND ACADEMICCALENDAR.ACADEMIC_SESSION = SECTIONS.ACADEMIC_SESSION
		FOR JSON PATH
		)
FROM [SECTIONS] AS [SECTIONS]
INNER JOIN [vwCurrentYT]
	ON CURRENT_YEAR = SECTIONS.ACADEMIC_YEAR
		AND CURRENT_TERM = SECTIONS.ACADEMIC_TERM
FOR JSON PATH, INCLUDE_NULL_VALUES