--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 = STVTERM:term_CODEcode
JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVACCT
 ORDER BY   ON SSBSECT_ACCTTERM_CODE =DESC
STVACCT_CODE;
WHERE SSBSECT_PTRM_END_DATE > (
    SELECT (FTVFSYR
--Positive Attendance Sections where course ends in Fiscal Year (320 Reporting Cycle) different than STVTERM_END_DATE)
SELECT DISTINCT
   FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_FIMSMGR_FTVFSYRSSBSECT.SSBSECT_TERM_CODE,
    WHERE FTVFSYR_FSYR_CODE = :fiscal_year
  SSBSECT.SSBSECT_CRN,
    SSBSECT.SSBSECT_SUBJ_CODE,
     AND INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
)SSBSECT.SSBSECT_CRSE_NUMB,
    SSBSECT.SSBSECT_ENRL,
    SSBSECT_PTRM_CODE,
AND STVACCT_ACTUAL_IND = 'Y'
 SSBSECT_ACCT_CODE,
   AND SSBSECT.INVOKE_DATATO_VARCHAR(SSBSECT_PTRM_END_DATE = DATEADD(DAY, -1, CURRENT_DATE)
--, 'YYYY-MM-DD') AS SSBSECT_PTRM_END_DATE,
   AND STVTERM_ACYR_CODE
< CONCAT('20', :fiscal_year)+'1' --Removes future CRNs based off their future Academic Year
--    ANDFROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SSBSECT AS SSBSECT
JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVTERM
    ON SSBSECT_TERM_CODE = < CONCAT('20', :fiscal_year,'30') --Removes trailing summer that is included in future 320 cycles
--    AND SSBSECT_TERMSTVTERM_CODE
JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVACCT
    ON SSBSECT_ACCT_CODE = :termSTVACCT_codeCODE
ORDER BYWHERE SSBSECT_TERMPTRM_END_CODEDATE DESC;> ;(
 --Registration activity and contact hours w/ CA residency
SELECT  SELECT (FTVFSYR_END_DATE)
    FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_FIMSMGR_FTVFSYR
    WHERE FTVFSYR_FSYR_CODE  SFRSTCR.*= :fiscal_year
        AND INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
)
 ,RESIDENT.* FROM  AND STVACCT_ACTUAL_IND = 'Y'
    AND  COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SFRSTCR SFRSTCR
LEFT JOIN  SSBSECT.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
--    AND 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 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.*
           SFRSTCR.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
AND  ,RESIDENT.*
FROM            STVRESD.STVRESD_IN_STATE_IND = 'I'
ANDCOS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SFRSTCR SFRSTCR
LEFT JOIN             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     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_SFRSTCRSTVRESD AS SFRSTCRSTVRESD
LEFT JOIN       DEV_DB.JOSHUAME.FV_DEMO_GET_RESIDENCY RESIDENTON RESIDENT.SGBSTDN_RESD_CODE = STVRESD_CODE
WHERE          ON SFRSTCR.SFRSTCR_PIDMTERM_CODE = RESIDENT.PIDM :term_code
AND            AND SFRSTCR.SFRSTCR_TERM_CODECRN = RESIDENT.TERM_CODE
LEFT JOIN:CRN
AND        COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVRESD AS STVRESD     ON RESIDENTSFRSTCR.SGBSTDNINVOKE_RESDDATA_CODEDATE = STVRESD_CODE
WHERE           SFRSTCR.SFRSTCR_TERM_CODE = :term_codeDATEADD(DAY, -1, CURRENT_DATE)
AND             SFRSTCR.SFRSTCR_CRNSTVRESD.STVRESD_IN_STATE_IND = :CRN'I'
AND             SFRSTCRSTVRESD.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE);

AND--Sum of contact hours divided by 525 for FTES amount
SELECT   STVRESD.STVRESD_IN_STATE_IND = 'I'
AND        SUM(SFRSTCR.SFRSTCR_ATTEND_HR)/525
FROM            STVRESD.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE);

Sorry for the delay on this. I’m inclined to say these Alternative Attendance Accounting Procedure-Daily Census courses also have the summer shift reporting option. Title 5 section 58010(a) states “Full-time equivalent student for courses using census procedure may be reported in either the fiscal year in which the census day procedure is completed or in which the course ends.” I know that this provision does not apply to positive attendance courses (those must be reported in the period in which the course ends), however, I do not see any reason that it would not apply to courses on the alternative attendance accounting procedure- daily census.

As is the case with regular daily census courses, you would want to make sure that appropriate records are maintained to show that FTES were claimed appropriately and not claimed in both fiscal years.

-Natalie Wagner 7/15/2021