DROP PROCEDURE IF EXISTS [dbo].[Daily_Load_Daily_Enrollment_v1]
GO
CREATE PROCEDURE [dbo].[Daily_Load_Daily_Enrollment_v1]
-- Add the parameters for the stored procedure here
--DECLARE @load_date DATETIME = getdate()
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
/**** This is for open enrollment dates ****/
DECLARE @Date DATE
SET @Date = CONVERT(DATE,GETDATE()-1)
;WITH FIND_TERM AS
(
SELECT DISTINCT A.EMPLID,
A.STRM,
A.ACAD_CAREER,
A.CLASS_NBR,
A.UNT_PRGRSS,
A.STDNT_ENRL_STATUS,
TR.TERM_BEGIN_DT,
TR.TERM_END_DT,
@Date AS 'Enrollment_Date'
FROM (SELECT EMPLID, STRM, ACAD_CAREER, CLASS_NBR, UNT_PRGRSS, STDNT_ENRL_STATUS
FROM Student_Data.dbo.cs_PS_STDNT_ENRL
WHERE STRM >= '2217'
AND STDNT_ENRL_STATUS = 'E'
AND ACAD_CAREER <> 'CEPD'
AND GRADING_BASIS_ENRL <> 'NON'
) A
LEFT JOIN Student_Data.dbo.cs_PS_TERM_TBL TR
ON A.STRM = TR.STRM
AND TR.ACAD_CAREER = 'UGRD'
INNER JOIN Student_Data.dbo.cs_PS_SESSION_TBL S
ON TR.STRM = S.STRM
AND TR.TERM_BEGIN_DT = S.SESS_BEGIN_DT
AND TR.TERM_END_DT = S.SESS_END_DT
WHERE @Date BETWEEN S.FIRST_ENRL_DT AND S.SESS_END_DT
)SELECT * FROM FIND_TERM
