Stored Procedure - usp_FactEnrollmentSS

Property	Value
Name	usp_FactEnrollmentSS_Select
Schema	dbo
Is Encrypted	False
Ansi Nulls Status	True
Quoted Identifier Status	True
Description

Name	Data Type	Direction	Description
@CurrentDayDate	datetime(23, 3)	Input
@Term	varchar(6)	Input

Name	Type
ST_STDNT_CLS_LOG__EnrollmentSS_Extract	Table

Name	Type
ST_STDNT_CLS_LOG__EnrollmentSS_Extract	TABLE
udf_StringToDate	USERDEFINEDFUNCTION

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_FactEnrollmentSS_Select] Script Date: 03/09/2017 17:21:49 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/* 10/22/2013 Sophia Cowan - Created Stored Procedure from DWStaging usp as replacement for
dbo.usp_FactActiveEnrollmentSnapshot_Select
*/
CREATE PROC [dbo].[usp_FactEnrollmentSS_Select]
@CurrentDayDate datetime
, @Term varchar(6)
AS
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT
          CAST('aaa' AS varchar(9)) AS STDNT_ID
        , CAST('aaa' AS varchar(6)) AS EFF_TRM
        , CAST(123 AS int) AS REF_NUM
        , CAST(2011-04-21 as datetime) as EnrollmentDate
        , CAST(1 as Int) as Enrolled
        , CAST(1 as Int) as PaidEnrollment
    END

CREATE TABLE #Stdnt_Term_Ref
    (
    [STDNT_ID] VARCHAR(9)  NOT NULL
    ,[EFF_TRM] VARCHAR(6) NOT NULL
    ,[REF_NUM] int NOT NULL
    ,MaxLogDtTm BIGINT NOT NULL
    ,Enrolled INT NULL
    ,PaidEnrollment INT NULL
    )
CREATE TABLE #Stdnt_Term_Ref_PaidFlag
    (
    [STDNT_ID] VARCHAR(9)  NOT NULL
    ,[EFF_TRM] VARCHAR(6) NOT NULL
    ,[REF_NUM] int NOT NULL
    ,MaxLogDtTm BIGINT NOT NULL
    ,Enrolled INT NULL
    ,PaidEnrollment INT NULL
    ,NotPaidFlag INT NULL
    )

INSERT INTO #Stdnt_Term_Ref
    (
    [STDNT_ID]
    ,[EFF_TRM]
    ,[REF_NUM]
    ,MaxLogDtTm
    )
     SELECT   [STDNT_ID]
             ,[EFF_TRM]
             ,[REF_NUM]  --Determines if class log
             ,MAX((CAST([LOG_DATE] AS bigint) * 10000000) + [LOG_TIME]) AS MaxLogDtTm
     FROM [dbo].[ST_STDNT_CLS_LOG__EnrollmentSS_Extract] clslog_inner
           WHERE EFF_TRM = @Term
           AND  [REF_NUM] IS NOT NULL
           AND dbo.udf_StringToDate([LOG_DATE]) <=  @CurrentDayDate
     GROUP BY [STDNT_ID],[EFF_TRM],[REF_NUM]
    ORDER BY [STDNT_ID],[EFF_TRM],[REF_NUM], MaxLogDtTm

INSERT INTO #Stdnt_Term_Ref_PaidFlag
    (
    [STDNT_ID]
    ,[EFF_TRM]
    ,[REF_NUM]
    ,MaxLogDtTm
    ,NotPaidFlag
    )
     SELECT   #Stdnt_Term_Ref.[STDNT_ID]
             ,#Stdnt_Term_Ref.[EFF_TRM]
             ,#Stdnt_Term_Ref.[REF_NUM]  --Determines if class log
             ,MaxLogDtTm
             ,(CASE
                WHEN NOT_PAID_FLG = 1 THEN 1
                ELSE 0
                END) as NotPaidFlag
     FROM #Stdnt_Term_Ref
     inner join [dbo].[ST_STDNT_CLS_LOG__EnrollmentSS_Extract] clslog_inner
     on
       (
         clslog_inner.STDNT_ID = #Stdnt_Term_Ref.STDNT_ID
        AND clslog_inner.EFF_TRM =  #Stdnt_Term_Ref.EFF_TRM
        AND clslog_inner.REF_NUM = #Stdnt_Term_Ref.REF_NUM
        AND CAST(clslog_inner.[LOG_DATE] AS bigint) * 10000000 + clslog_inner.[LOG_TIME]  = #Stdnt_Term_Ref.MaxLogDtTm
       )

UPDATE #Stdnt_Term_Ref_PaidFlag
SET
        #Stdnt_Term_Ref_PaidFlag.Enrolled =
            (CASE
                WHEN SSCL.[TRNSCTN_TY] = 'D' THEN 0
                ELSE 1
                End ) -- Enrolled
        ,#Stdnt_Term_Ref_PaidFlag.PaidEnrollment =
            (CASE
                WHEN SSCL.NOT_PAID_FLG = 1 THEN 0
                ELSE 1
                END) -- PaidEnrollment
FROM #Stdnt_Term_Ref_PaidFlag
INNER JOIN dbo.[ST_STDNT_CLS_LOG__EnrollmentSS_Extract] SSCL
    ON (
        SSCL.STDNT_ID = #Stdnt_Term_Ref_PaidFlag.STDNT_ID
        AND SSCL.EFF_TRM =  #Stdnt_Term_Ref_PaidFlag.EFF_TRM
        AND SSCL.REF_NUM = #Stdnt_Term_Ref_PaidFlag.REF_NUM
        AND CAST(SSCL.[LOG_DATE] AS bigint) * 10000000 + SSCL.[LOG_TIME]  = #Stdnt_Term_Ref_PaidFlag.MaxLogDtTm
        and CAST(SSCL.NOT_PAID_FLG as Int) = #Stdnt_Term_Ref_PaidFlag.NotPaidFlag
        )
SELECT
  #Stdnt_Term_Ref_PaidFlag.STDNT_ID
, #Stdnt_Term_Ref_PaidFlag.EFF_TRM
, #Stdnt_Term_Ref_PaidFlag.REF_NUM
, CAST(@CurrentDayDate as datetime) AS EnrollmentDate
, 1 AS Enrolled
, MAX(#Stdnt_Term_Ref_PaidFlag.PaidEnrollment) AS PaidEnrollment
FROM #Stdnt_Term_Ref_PaidFlag
WHERE Enrolled = 1
GROUP BY #Stdnt_Term_Ref_PaidFlag.STDNT_ID
        ,#Stdnt_Term_Ref_PaidFlag.EFF_TRM
        ,#Stdnt_Term_Ref_PaidFlag.REF_NUM
        ,#Stdnt_Term_Ref_PaidFlag.MaxLogDtTm
ORDER BY #Stdnt_Term_Ref_PaidFlag.REF_NUM