--Cross Term Positive Attendance Sections
SELECT DISTINCT
SSBSECT_TERM_CODE
,SSBSECT.SSBSECT_CRN
,SSBSECT.SSBSECT_SUBJ_CODE
,SSBSECT.SSBSECT_CRSE_NUMB
,SSBSECT.SSBSECT_ACCT_CODE
,SSBSECT_ENRL
,TO_VARCHAR(SSBSECT_PTRM_END_DATE, 'YYYY-MM-DD') AS SSBSECT_PTRM_END_DATE
FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SSBSECT AS SSBSECT
JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVTERM ON SSBSECT_TERM_CODE = STVTERM_CODE
JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVACCT ON SSBSECT_ACCT_CODE = STVACCT_CODE
WHERE SSBSECT_PTRM_END_DATE > STVTERM_END_DATE
AND STVACCT_ACTUAL_IND = 'Y'
AND SSBSECT.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
AND SSBSECT_TERM_CODE = :term_code
ORDER BY SSBSECT_TERM_CODE DESC
;
--Positive Attendance Sections where course ends in Fiscal Year (320 Reporting Cycle) different than STVTERM_END_DATE
SELECT DISTINCT
SSBSECT.SSBSECT_TERM_CODE,
SSBSECT.SSBSECT_CRN,
SSBSECT.SSBSECT_SUBJ_CODE,
SSBSECT.SSBSECT_CRSE_NUMB,
SSBSECT.SSBSECT_ENRL,
SSBSECT_PTRM_CODE,
SSBSECT_ACCT_CODE,
TO_VARCHAR(SSBSECT_PTRM_END_DATE, 'YYYY-MM-DD') AS SSBSECT_PTRM_END_DATE,
STVTERM_ACYR_CODE
FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SSBSECT AS SSBSECT
JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVTERM
ON SSBSECT_TERM_CODE = STVTERM_CODE
JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVACCT
ON SSBSECT_ACCT_CODE = STVACCT_CODE
WHERE SSBSECT_PTRM_END_DATE > (
SELECT (FTVFSYR_END_DATE)
FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_FIMSMGR_FTVFSYR
WHERE FTVFSYR_FSYR_CODE = :fiscal_year
AND INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
)
AND STVACCT_ACTUAL_IND = 'Y'
AND SSBSECT.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
-- AND STVTERM_ACYR_CODE < CONCAT('20', :fiscal_year)+'1' --Removes future CRNs based off their future Academic Year
-- AND SSBSECT_TERM_CODE < CONCAT('20', :fiscal_year,'30') --Removes trailing summer that is included in future 320 cycles
-- AND SSBSECT_TERM_CODE = :term_code
ORDER BY SSBSECT_TERM_CODE DESC;
;
--Registration activity and contact hours w/ CA residency
SELECT SFRSTCR.*
,RESIDENT.*
FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SFRSTCR SFRSTCR
LEFT JOIN DEV_DB.JOSHUAME.FV_DEMO_GET_RESIDENCY RESIDENT ON SFRSTCR.SFRSTCR_PIDM = RESIDENT.PIDM
AND SFRSTCR.SFRSTCR_TERM_CODE = RESIDENT.TERM_CODE
LEFT JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVRESD AS STVRESD
ON RESIDENT.SGBSTDN_RESD_CODE = STVRESD_CODE
WHERE SFRSTCR.SFRSTCR_TERM_CODE = :term_code
AND SFRSTCR.SFRSTCR_CRN = :CRN
AND SFRSTCR.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
AND STVRESD.STVRESD_IN_STATE_IND = 'I'
AND STVRESD.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE);
--Sum of contact hours divided by 525 for FTES amount
SELECT SUM(SFRSTCR.SFRSTCR_ATTEND_HR)/525
FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SFRSTCR AS SFRSTCR
LEFT JOIN DEV_DB.JOSHUAME.FV_DEMO_GET_RESIDENCY RESIDENT
ON SFRSTCR.SFRSTCR_PIDM = RESIDENT.PIDM
AND SFRSTCR.SFRSTCR_TERM_CODE = RESIDENT.TERM_CODE
LEFT JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVRESD AS STVRESD
ON RESIDENT.SGBSTDN_RESD_CODE = STVRESD_CODE
WHERE SFRSTCR.SFRSTCR_TERM_CODE = :term_code
AND SFRSTCR.SFRSTCR_CRN = :CRN
AND SFRSTCR.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
AND STVRESD.STVRESD_IN_STATE_IND = 'I'
AND STVRESD.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE);
--D or ID ACCT_CODE for NC Sections
SELECT DISTINCT
SSBSECT_TERM_CODE
,SSBSECT.SSBSECT_CRN
,SSBSECT.SSBSECT_SUBJ_CODE
,SSBSECT.SSBSECT_CRSE_NUMB
,SSBSECT.SSBSECT_ACCT_CODE
,SSBSECT_ENRL
FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SSBSECT AS SSBSECT
WHERE SSBSECT.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
AND SSBSECT_TERM_CODE = :term_code
AND SSBSECT_ACCT_CODE IN ('D','ID')
AND SSBSECT_CRSE_NUMB LIKE '4%'
ORDER BY SSBSECT_TERM_CODE DESC
--NonResident TSCH are showing up for D/ID on Term 202330.
SELECT SSBSECT.SSBSECT_CRN
,SSBSECT.SSBSECT_PTRM_END_DATE
,SSBSECT.SSBSECT_CAMP_CODE
,COUNT(SFRSTCR.SFRSTCR_PIDM)
FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SFRSTCR AS SFRSTCR
LEFT JOIN DEV_DB.JOSHUAME.FV_DEMO_GET_RESIDENCY RESIDENT
ON SFRSTCR.SFRSTCR_PIDM = RESIDENT.PIDM
AND SFRSTCR.SFRSTCR_TERM_CODE = RESIDENT.TERM_CODE
LEFT JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVRESD AS STVRESD
ON RESIDENT.SGBSTDN_RESD_CODE = STVRESD_CODE
INNER JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SSBSECT AS SSBSECT
ON SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
AND SFRSTCR_CRN = SSBSECT_CRN
WHERE SFRSTCR.SFRSTCR_TERM_CODE = :term_code
AND STVRESD.STVRESD_IN_STATE_IND <> 'I'
AND SSBSECT.SSBSECT_ACCT_CODE IN ('D','ID')
AND SSBSECT_CAMP_CODE <> 'COS'
AND SFRSTCR.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
AND STVRESD.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
AND SSBSECT.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
GROUP BY SSBSECT_CRN, SSBSECT_CAMP_CODE,SSBSECT_PTRM_END_DATE; |